Adaptive multi-table data normalization method based on rpa and ocr technology

Abstract

This invention discloses an adaptive multi-table data normalization method based on RPA and OCR technologies. Using RPA tools and OCR technology, this invention can synchronously adjust the corresponding data content in the "Floating Summary" and "Normal Transfer" sub-tables of the summary table after the client modifies the data. Then, it performs data aggregation and merging on the two sub-tables separately. Finally, after confirming that the data does not need modification, the data in the summary table is finally aggregated and merged into the group's audit standard notes to the financial statements, thereby generating an audit report. This invention achieves automated merging and aggregation processing of massive amounts of single-file data through RPA and OCR technologies. Under the premise of intelligently identifying basic data table information, quickly matching and capturing data, and accurately summarizing data, it achieves the normalization and reverse writing of audit standard notes to the financial statements, solving the problems of traditional manual data aggregation and merging methods, which may result in aggregation errors, large workloads, and high labor costs due to repeated data adjustments and modifications.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to an adaptive multi-table data normalization method based on RPA and OCR technologies. Background Technology

[0002] When issuing a financial audit report, the certified public accountant collects the individual audit financial documents of a client's parent company and all its subsidiaries. The accountant then manually organizes the data from each individual document into a summary table. The data from each sub-table is then merged, categorized, and calculated in the summary table. Finally, the data from the summary table is written back into the group's unified audit standard notes to the financial statements for final data unification and summarization.

[0003] The above data normalization and aggregation method has the following two problems:

[0004] 1. Consolidating massive amounts of data requires significant manpower. When organizing data into summary tables, auditors repeatedly copy and paste various sub-tables of different table types from each individual file into the summary tables. This manual process of merging and calculating data from over 200 sub-tables in the financial statements attached to the audit standards is highly repetitive, extremely labor-intensive, and prone to errors. Furthermore, when a group company has multiple subsidiaries, each subsidiary may contain over 200 sub-tables in its individual file; the number of individual files across several subsidiaries could reach thousands. This undoubtedly represents a massive workload for auditors. When dealing with a large client base, accounting firms often struggle to complete the data consolidation and merging work quickly and efficiently.

[0005] 2. The data in some individual audit files may be repeatedly modified and adjusted. Manually summarizing and unifying the data can easily lead to omissions in the summary. After manually merging the individual files into a summary table, the data in the summary table is written back into the financial statements in the audit standard notes. Since clients may continue to modify or adjust some individual audit financial documents after writing, accountants need to manually update the client's adjustments, which is very time-consuming and labor-intensive, and prone to data omissions. Summary of the Invention

[0006] This invention aims to improve the automation of data classification, summarization, and normalization, and to reduce errors and omissions in data summarization. It provides an adaptive multi-table data normalization method based on RPA and OCR technologies.

[0007] To achieve this objective, the present invention adopts the following technical solution:

[0008] An adaptive multi-table data normalization method based on RPA and OCR technologies is provided, the steps of which include:

[0009] S1, the RPA tool uses OCR technology to classify the sub-tables in the template of the audit standard notes to the financial statements, and obtains the basic information of each floating financial sub-table classified as a floating table and records it in the floating summary sub-table of the newly created summary table named "Summary Transfer to_Merge Template.xls";

[0010] S2, The RPA tool automatically creates a new table in the summary table and names it a regular transfer to sub-table. The regular transfer to sub-table records the data table information of the corresponding floating corporate and financial sub-table that is classified as a floating table. Then, the RPA tool is used to identify the data entry location information of the data table, including the content of the first column of the last row of the data table, the data table name, the name of the sub-table to which the data table belongs, and the files of each individual subsidiary.

[0011] S3, the RPA tool automatically retrieves the corresponding data table content from the corresponding floating corporate and financial sub-tables of each individual subsidiary based on each piece of basic information recorded in the floating summary sub-table, merges and transcribes it into the corresponding floating table identification template in the floating summary sub-table.

[0012] And according to the data in each of the data tables to be merged and summarized as identified in the ordinary transfer to sub-table, the location information is filled in, and the data content is automatically captured from the corresponding data table in the corresponding floating corporate and financial sub-table of each of the individual subsidiaries and merged and transcribed into the ordinary transfer to sub-table according to the location label guidance;

[0013] S4. The data in the summary table are merged and summarized and then included in the audit standard notes to the financial statements.

[0014] Preferably, step S1, the method of obtaining the basic information of each of the floating corporate and financial sub-tables classified as floating tables and recording it in the floating summary sub-table, specifically includes the following steps:

[0015] S11, the RPA tool uses the OCR technology to identify the name of each sub-table in the audit standard notes financial statement template provided by the client;

[0016] S12, each identified sub-table name is matched with the floating table name stored in the database, and the sub-table corresponding to the successfully matched sub-table name is determined to be a floating table;

[0017] S13, using OCR technology to extract the data table name, header content, number of header rows, end content, and number of header columns of the sub-table that is determined to be a floating table as the basic information of the sub-table;

[0018] S14, Construct the floating table identification template corresponding to the sub-table;

[0019] S15, record the basic information of each of the sub-tables that are identified as floating tables in the corresponding floating table identification template in the floating summary sub-table.

[0020] Preferably, step S4, the method for merging and summarizing the data in the summary table and then normalizing it to the group's audit standard notes to the financial statements, includes the following steps:

[0021] S41, The data in the floating summary sub-table is merged and summarized by the RPA tool and then unified into the audit standard notes to the financial statements.

[0022] S42, The data transferred from the ordinary table to the sub-table is merged and summarized by the RPA tool and then included in the audit standard notes to the financial statements.

[0023] This invention, while clients continuously provide modified data, uses RPA tools and OCR technology to simultaneously adjust the corresponding data content in the floating summary sub-table and the ordinary transfer sub-table within the "Summary Transfer to_Merge Template.xls" file. Then, it performs data aggregation and merging on both the floating summary sub-table and the ordinary transfer sub-table. Finally, after confirming that the data requires no further modification, the data in "Summary Transfer to_Merge Template.xls" is finally aggregated and unified into the group's audit standard notes to the financial statements, thereby generating an audit report. This invention achieves automated merging and aggregation processing of massive amounts of individual audit files through RPA and OCR technologies. With intelligent identification of basic data table information, rapid data matching and extraction, and accurate data aggregation, it achieves unified rewriting of the group's audit standard notes to the financial statements. This solves the problems of traditional manual data aggregation and merging methods, which may result in aggregation errors, large workloads, and high labor costs due to repeated data adjustments and modifications. Attached Figure Description

[0024] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments of the present invention will be briefly described below. Obviously, the drawings described below are merely some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without any creative effort.

[0025] Figure 1 This is an example diagram of an empty floating financial sub-table;

[0026] Figure 2 yes Figure 1 The floating table identification template corresponding to the floating enterprise and finance sub-table shown;

[0027] Figure 3 This is an example of an empty floating summary sub-table in "Summary Transfer to _Merge Template.xls" which contains basic information about floating tables;

[0028] Figure 4 This is a sample image of a blank template for a data table named "Cash and Cash Equivalents";

[0029] Figure 5 This is an example diagram of the floating summary sub-table after data merging;

[0030] Figure 6 This is an example diagram showing how data is typically transferred to a sub-table after being merged and summarized.

[0031] Figure 7 It is Figure 5 The floating summary sub-tables shown are Figure 6 The diagram shown illustrates the normalization of the sub-table to the corresponding floating corporate finance sub-table in the financial statements in the notes to the audit standards.

[0032] Figure 8 This is a logic diagram of the implementation of the adaptive multi-table data normalization method based on RPA and OCR technology provided in this embodiment of the invention. Detailed Implementation

[0033] The technical solution of the present invention will be further described below with reference to the accompanying drawings and specific embodiments.

[0034] The accompanying drawings are for illustrative purposes only and are schematic diagrams, not actual images. They should not be construed as limiting the scope of this patent. To better illustrate the embodiments of the present invention, some parts in the drawings may be omitted, enlarged, or reduced, and do not represent the actual dimensions of the product. It is understandable to those skilled in the art that some well-known structures and their descriptions may be omitted in the drawings.

[0035] In the accompanying drawings of the embodiments of the present invention, the same or similar reference numerals correspond to the same or similar components. In the description of the present invention, it should be understood that if terms such as "upper," "lower," "left," "right," "inner," and "outer" indicate the orientation or positional relationship based on the orientation or positional relationship shown in the drawings, they are only for the convenience of describing the present invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, the terms used to describe positional relationships in the drawings are only for illustrative purposes and should not be construed as limiting the present patent. For those skilled in the art, the specific meaning of the above terms can be understood according to the specific circumstances.

[0036] In the description of this invention, unless otherwise explicitly specified and limited, the term "connection" or similar designation indicating a connection between components should be interpreted broadly. For example, it can refer to a fixed connection, a detachable connection, or an integral part; it can be a mechanical connection or an electrical connection; it can be a direct connection or an indirect connection through an intermediate medium; it can refer to the internal communication between two components or the interaction between two components. Those skilled in the art can understand the specific meaning of the above terms in this invention based on the specific circumstances.

[0037] This invention automates the process of manually merging and rewriting multiple tables, achieving adaptive merging and normalization of data from multiple tables through RPA workflow automation and OCR text recognition technology. The following is combined with... Figures 1-8 The adaptive multi-table data normalization method based on RPA and OCR technology provided in this embodiment of the invention is described in detail. The method includes the following steps:

[0038] Step 1: The RPA tool uses OCR technology to classify the sub-tables in the template of the audit standard notes to the financial statements, and obtains the basic information of each floating financial sub-table classified as a floating table and records it in the floating summary sub-table in the newly created summary table "Summary Transfer to_Merge Template.xls";

[0039] Specifically, the method for obtaining the basic information of each floating corporate and financial sub-table and recording it in the floating summary sub-table is as follows:

[0040] 1) The client provides the group's audit standard notes to the financial statement templates (including blank data templates for each individual file), and then the RPA tool uses OCR technology to automatically identify the sub-table names of each blank data template (e.g., Figure 3 The system records "monetary funds" and matches the identified sub-table names with the names of floating tables in the database to identify whether each blank data template is a floating table or a fixed table (the audit standard notes to the financial statements include fixed tables and floating tables, and the names of fixed tables and floating tables are different. If the name matches successfully, the type of the sub-table corresponding to the successfully matched sub-table name is determined to be a floating corporate financial sub-table).

[0041] 2) Use OCR technology to extract the table name, header content, number of header rows, end content, and number of columns for each floating financial sub-table identified as a floating table. For example, Figure 3 The phrase "where usage is restricted due to mortgage, pledge, or freeze..." shown is the data table name of this sub-table. Figure 1 The “Item,” “Ending Balance,” “Previous Year’s End Amount,” and “Remarks” shown are the table header content. Figure 1 The header row of this sub-table is the second row (that is, the header row in this sub-table is the second row). Figure 1The end content of this sub-table is "Total / Ending Balance / ..." (that is, the content of the first column of the last row of this sub-table). Figure 1 The number of columns in this sub-table is the same as the number of columns occupied by the table header. Assuming... Figure 1 If the header of the table has n elements, then the number of columns in the sub-table is n.

[0042] 3) After identifying the sub-table name, header content, number of header rows, end content, and number of header columns for each sub-table, a floating table identification template is constructed for each sub-table based on this information. For example, for... Figure 1 The sub-table structure shown is as follows Figure 2 The floating table identification template shown ( Figure 2 In the attached diagram, label "1" indicates the area containing the sub-table type and name corresponding to the floating table identification template; label "2" indicates the row containing the table header content; and label "3" indicates the end content (i.e., the first column of the last row of the sub-table). The purpose of constructing the floating table identification template for each floating enterprise / finance sub-table is to record the data table name, header content, number of header rows, end content, and number of header columns of the retrieved sub-table.

[0043] 4) For each floating financial sub-table identified by the RPA tool using OCR technology, the basic information of that sub-table—including its name, table name, header content, number of header rows, end content, and number of header columns—is automatically recorded in a newly created summary table named "Floating Summary" (sheet1) within "Summary Transfer to_Merge Template.xls." This creates a record labeled "Floating Table." The basic information for all floating tables is then recorded in this "Floating Summary" sub-table. The basic information for a specific floating table recorded in the "Floating Summary" sub-table is as follows: Figure 3 As shown. By Figure 3 As can be seen, the basic information of each sub-table is recorded in the floating summary sub-table in the format of [sub-table name|data table name|data table end row first column content|data table header row number|data table header column number]. The purpose of recording the basic information of each floating corporate and financial sub-table in the floating summary sub-table is to use it as a data carrier for the next step of summarizing the data in the floating corporate and financial sub-tables of each individual company.

[0044] Step 2: Using an RPA tool, automatically create a new Sheet2 table named "Normal Transfer Sub-Table" within the "Summary Transfer to_Merge Template.xls" file. This Sheet2 sub-table records information from the data tables in the single file. Then, the RPA tool identifies the data entry locations for these tables. For example, Figure 4To create a blank template for a data table named "Cash and Cash Equivalents" in a single file within the "Summary Transfer to_Merge Template.xls" file, RPA records the table's location information within that single file. This information includes: the content of the first column of the last row of the table, the table name, the name of the sub-table to which the table belongs, and the single file to be merged. These four pieces of information allow the precise location of the data table. Specifically, the RPA tool iteratively identifies the "single files to be merged," prioritizing the identification of the "name of the sub-table to which the table belongs." Then, the RPA tool uses functions to retrieve row content to obtain the "table name" and the "content of the first column of the last row of the table," thus accurately determining the table's location using these four basic pieces of information.

[0045] Step 3: Merge and transcribe data from the same floating table in different subsidiaries of the same group into a floating summary sub-table, and merge and transcribe data from the same data table in the same floating table in different subsidiaries into a common transfer sub-table.

[0046] Figure 5 This is an example diagram of a floating summary sub-table after data merging and aggregation. Suppose we need to merge and aggregate floating summaries for three individual subsidiaries under a certain group. The merging method is as follows: First, create a new table as shown in step one... Figure 3 The floating summary sub-table shown is empty. Then, based on the basic information of each floating corporate and financial sub-table to be merged obtained in step one, the data content is automatically extracted from the corresponding floating corporate and financial sub-tables of each individual subsidiary and merged and transcribed into the corresponding floating table identification template in the floating summary sub-table. For example, an RPA tool is used to iteratively retrieve the data content information of the sub-table from the individual file by locating the sub-table position using [sub-table name|data table name|data table end row first column content|data table header row number|data table header column number], and then transcribe the data information into the blank space below the header of the corresponding floating summary sub-table.

[0047] Figure 6 This is an example diagram of a simple transfer to a sub-table after data merging and summarizing. Suppose we need to perform a simple transfer to a sub-table merging and summarizing on 3 individual subsidiaries under a certain group. The merging method is as follows: First, create a new table as shown in step two... Figure 4The example shows a standard transfer to a sub-table (empty). Then, based on the data in each table to be merged and summarized as identified in step two, the location information is filled in. Data content is automatically retrieved from the corresponding floating financial sub-tables of each individual subsidiary and merged and transcribed into the standard transfer to a sub-table according to the location indicators. For example, an RPA tool iteratively identifies the "individual files to be merged," prioritizing the identification of the "sub-table name to which the data table belongs." The tool uses functions to retrieve data row content to obtain the "data table name" and the "content of the first column of the last row of the data table." After accurately obtaining the data table location using these four basic pieces of information, the data content is copied and transcribed into the corresponding data table in the standard transfer to a sub-table. It should be noted that after data retrieval, appropriate calculations are required to merge the data. For example, Figure 6 The "total" in the text merges the data for the same item from various individual subsidiaries, such as... Figure 6 The "ending balance" of each individual subsidiary shown is summarized and merged.

[0048] Step 4: Consolidate and summarize the data in "Summary Transfer to_Consolidation Template.xls" and then integrate it into the group's audit standard notes to financial statements. First, use RPA tools to consolidate and summarize the data in the floating summary sub-tables and then integrate (reverse write) it into the group's audit standard notes to financial statements. Then, use RPA tools to consolidate and summarize the data in the ordinary transfer sub-tables (e.g., summing the data in each column of the ordinary transfer sub-tables) and then integrate it into the group's audit standard notes to financial statements.

[0049] Figure 7 To be Figure 5 The floating summary sub-table shown and Figure 6The diagram illustrates how a regular transfer sub-table is consolidated into one of the floating corporate financial sub-tables in the audit standard notes to the financial statements. Since group clients or subsidiaries may continuously modify individual audit financial documents during the issuance of audit reports, this invention can simultaneously adjust the corresponding data content in the floating summary sub-table and the regular transfer sub-table in "Summary Transfer to_Consolidation Template.xls" using RPA tools and OCR technology, while clients continuously provide revised data. Then, data aggregation and consolidation are performed on both the floating summary sub-table and the regular transfer sub-table. Finally, after confirming that no further modifications are needed, the data in "Summary Transfer to_Consolidation Template.xls" is finally aggregated and consolidated into the group's audit standard notes to the financial statements, thereby generating the audit report. This invention achieves automated merging and summarizing of massive amounts of individual audit documents through RPA and OCR technologies. Under the premise of intelligently identifying basic information of data tables, quickly matching and capturing data, and accurately summarizing data, it realizes the unified reverse writing of the audit standard notes financial statements of the group. It solves the problems of traditional manual data summarization and merging, which may result in summarization errors, large workload, and high labor costs due to repeated data adjustments and modifications.

[0050] It should be stated that the above-described specific embodiments are merely preferred embodiments of the present invention and the technical principles employed. Those skilled in the art should understand that various modifications, equivalent substitutions, and variations can be made to the present invention. However, such variations, as long as they do not depart from the spirit of the present invention, should be within the scope of protection of the present invention. Furthermore, some terminology used in this specification and claims is not limiting, but merely for ease of description.

Claims

1. An adaptive multi-table data normalization method based on RPA and OCR technology, characterized by the steps of include: S1, the RPA tool uses OCR technology to classify the sub-tables in the template of the audit standard notes to the financial statements, identify the floating corporate financial sub-tables that belong to the floating table, and obtain the basic information of each floating corporate financial sub-table, and record it in the floating summary sub-table of the newly created summary table. S2, using the RPA tool, create a new ordinary transfer sub-table in the summary table. In this sub-table, record the information of each specific data table identified in each floating corporate and financial sub-table that is to be merged and summarized. Use the RPA tool to identify the data entry location information of each data table, including the content of the first column of the last row of the data table, the data table name, the name of the sub-table to which the data table belongs, and the file of the subsidiary to which it belongs. S3, the RPA tool extracts data table content from the corresponding floating corporate and financial sub-tables of each individual subsidiary based on the basic information of the floating corporate and financial sub-table recorded in the floating summary sub-table, and merges and transcribes it into the corresponding floating table identification template in the floating summary sub-table; At the same time, based on the data table location information identified in the ordinary transfer sub-table, data content is retrieved from the corresponding floating corporate and financial sub-tables of each individual subsidiary and merged and transcribed into the ordinary transfer sub-table; S4. After merging and summarizing the data in the summary table, the data is incorporated into the audit standard notes to the financial statements.

2. The adaptive multi-table data normalization method based on RPA and OCR techniques of claim 1, wherein, Step S1, the method of obtaining the basic information of each of the floating corporate and financial sub-tables classified as floating tables and recording it in the floating summary sub-table specifically includes the following steps: S11, the RPA tool uses the OCR technology to identify the name of each sub-table in the audit standard notes financial statement template provided by the client; S12, each identified sub-table name is matched with the floating table name stored in the database, and the sub-table corresponding to the successfully matched sub-table name is determined to be a floating table; S13, using OCR technology to extract the data table name, header content, number of header rows, end content, and number of header columns of the sub-table that is determined to be a floating table as the basic information of the sub-table; S14, Construct the floating table identification template corresponding to the sub-table; S15, record the basic information of each of the sub-tables that are identified as floating tables in the corresponding floating table identification template in the floating summary sub-table.

3. The adaptive multi-table data normalization method based on RPA and OCR techniques, as claimed in claim 1, wherein, Step S4, the method of merging and summarizing the data in the summary table and then unifying it into the group's audit standard notes to the financial statements, includes the following steps: S41, The data in the floating summary sub-table is merged and summarized by the RPA tool and then unified into the audit standard notes to the financial statements. S42, The data transferred from the ordinary table to the sub-table is merged and summarized by the RPA tool and then included in the audit standard notes to the financial statements.

Images