A method for generating an audit analysis snapshot by fusing Excel macros and external data sources

Abstract

The present application relates to the field of audit data analysis application, especially to a kind of audit analysis snapshot generation method fusing Excel macro and external data source.The method includes: by obtaining the financial data table exported by external data source and loading to form data base table, the column information is verified and the result is written into step abstract area;Combined with data base table and step abstract area, execute thread processing, index information generation and write range record;Then based on the write range and analysis model library, select the target analysis model, complete formula calculation and carry out threshold rule anomaly identification, generate anomaly list;Finally, the anomaly list is packaged as an analysis snapshot document and performs download authentication processing.The present application realizes the unified link of data processing, anomaly identification and snapshot packaging in audit analysis process, which is beneficial to improve the reviewability of analysis process, record consistency and the standardization of archival management.

Description

Technical Field

[0001] This invention relates to the field of audit data analysis applications, and in particular to a method for generating audit analysis snapshots that integrates Excel macros and external data sources. Background Technology

[0002] In the field of audit data analysis applications, existing solutions for generating audit analysis snapshots from financial data tables exported from external data sources using Excel macros typically rely on loading the financial data tables as base tables, performing data processing and calculations, and then compiling the calculation outputs into result files or result tables for verification. These solutions suffer from limitations such as difficulty in creating a unified record of column comparison and verification results in the step summary area, a lack of consistent constraints on the write range with thread processing and index information generation, and a lack of verifiable correspondence between the anomaly list and the analysis snapshot document. Existing methods often rely on manual adjustments to table headers and field positions within Excel and the addition of calculation process descriptions, or on separately compiling anomaly items and explanatory materials after generating the result table. Under the constraint of download authentication processing, this can easily lead to incomplete records in the step summary area and unclear sources for the anomaly list, making it difficult to reliably generate authentication results for the analysis snapshot document. For the joint processing of data base tables, step summary areas, write ranges, analysis model libraries, anomaly lists, and analysis snapshot documents, existing technologies generally lack a common mechanism that integrates thread startup processing, index information generation and write range recording, target analysis model selection, write formula calculation results and threshold rule anomaly identification, and download authentication processing into the same continuous chain. This makes it difficult to form a consistent process from acquisition, recording, generation to encapsulation and verification in the application scenario of audit analysis snapshot generation. As a result, the analysis process is difficult to be uniformly reviewed and archived, which in turn affects the consistency and traceability of subsequent audit analysis work based on analysis snapshot documents. Summary of the Invention

[0003] To address the aforementioned technical problems, this invention provides a method for generating audit analysis snapshots that integrates Excel macros and external data sources, comprising:

[0004] S100. Obtain the financial data table exported from the external data source, load it to obtain the data base table, and perform column comparison and verification to write the results into the step summary area to generate the step summary area.

[0005] S200: Obtain the data base table and step summary area, perform thread startup processing, index information generation and write range record processing, and generate the write range;

[0006] S300. Based on the writing range and analysis model library, perform target analysis model selection processing, and execute anomaly identification processing of writing formula calculation results and threshold rule, and generate an anomaly list.

[0007] S400. Based on the anomaly list, perform analysis and snapshot document processing, execute download authentication processing, and generate authentication results.

[0008] Furthermore, the process of obtaining financial data tables exported from external data sources and loading them to obtain the base data table includes:

[0009] The system retrieves financial data tables exported from external data sources and performs loading processing. This loading process includes reading the file attributes and worksheet structure of the financial data tables to determine the scope of the worksheets to be imported and the data areas, copying or importing the data areas to preset worksheet locations to form a data base table, and registering the area location information of the data base table.

[0010] Furthermore, the process of writing the column comparison and verification results into the step summary area includes:

[0011] The column comparison and verification process includes extracting header information from the base data table, normalizing the column names in the header information, matching the normalized column names with a preset field list to output the matching results, and checking for missing, duplicate, and empty columns to generate column comparison and verification results. The column comparison and verification results are then written into the step summary area. This writing process includes writing the header information, mapping relationships, missing column records, duplicate column records, and empty column records into the step summary area in a verifiable manner, and cleaning or writing in segments before writing to the step summary area; finally, the step summary area is generated.

[0012] Furthermore, the process of obtaining the data base table and the step summary area, and then starting the thread processing includes:

[0013] The data base table and step summary area are obtained, and thread startup processing is performed. The thread startup processing includes determining the range of data to be processed based on the area positioning information of the data base table, dividing the data range into multiple segment ranges according to preset segmentation rules, creating a processing task for each segment range to read row data from the data base table and perform field alignment according to the mapping relationship, generating intermediate process table segments containing alignment fields, key field copy columns, and alignment mark columns, and merging each intermediate process table segment into a complete intermediate process table and performing a consistency check.

[0014] Furthermore, the index information generation and write range record processing, the process of generating the write range includes:

[0015] The index information generation process includes reading alignment fields and alignment markers from the intermediate process table, generating index information according to preset index rules, which include establishing index mapping relationships based on row number ranges and establishing column positioning relationships based on alignment field column positions; and performing write range record processing, which includes serializing the index information and writing it to the Redis cache to generate a write range when Redis write is enabled, or directly generating a write range based on the index information when Redis write is not enabled. The write range includes the range of row numbers covered by the index information, the range of column positions covered, and the range of the number of records written.

[0016] Furthermore, the process of selecting a target analysis model based on the writing range and the analysis model library includes:

[0017] Based on the writing range and analysis model library, a target analysis model selection process is performed. The target analysis model selection process includes loading the set of model entries in the analysis model library and their corresponding list of input fields, threshold rules and model information. The list of model entries is displayed in the Excel interface for selection. After selection, the position range of the covered column of the input field list and the writing range are matched and verified. After the verification is passed, the target analysis model is locked.

[0018] Furthermore, the process of performing anomaly detection and processing of the calculation results written into the formula and the threshold rule includes:

[0019] The process of writing the formula calculation results includes extracting data from the data range covered by the writing range according to the input field list of the target analysis model, generating a result table in an Excel workbook and writing the calculation results into the result table, while writing model information into the result table or the step summary area; and performing threshold rule anomaly identification processing, which includes scanning the result table according to the threshold rules of the target analysis model, highlighting items that meet the threshold conditions and generating anomaly identification records, and generating an anomaly list based on the anomaly identification records.

[0020] Furthermore, the anomaly list includes:

[0021] The anomaly list contains the correspondence between the location information of the anomaly items and the trigger threshold conditions.

[0022] Furthermore, based on the aforementioned anomaly list, the process of analyzing snapshot documents includes:

[0023] Based on the anomaly list, an analysis snapshot document is processed. The analysis snapshot document processing includes reading the data base table, intermediate process table, result table, anomaly list, and step summary area in the same workbook, generating a self-contained Excel file as the analysis snapshot document, and encapsulating the data base table, intermediate process table, result table, anomaly list, and step summary area into the analysis snapshot document in sequence. At the same time, the analysis snapshot document identifier, encapsulation time, and encapsulation status are appended to the step summary area.

[0024] Furthermore, the process of performing download authentication and generating authentication results includes:

[0025] The download authentication process includes entering the preview stage when the encapsulation status is successful, displaying the exception list and result table in the highlighted area, triggering authentication after generating the preview pass status, executing the download process after successful authentication, and writing the authentication result to the step summary area.

[0026] The key innovations of this invention include:

[0027] (1) After loading the financial data table exported from the external data source to obtain the data base table, the column comparison verification result is written to the step summary area to form a step summary area bound to the data base table, so that the column comparison verification result is used as a callable record in the subsequent processing link to participate in the writing range generation and the exception list generation.

[0028] (2) Based on the data base table and the step summary area, start thread processing is performed, and index information generation and writing range record processing are completed within the same thread processing link to obtain the writing range. The writing range simultaneously represents the processing boundary of the thread processing and the registration result of the index information, and serves as the input constraint for the target analysis model selection processing and the calculation result of the writing formula.

[0029] (3) Based on the writing range and analysis model library, the target analysis model selection process is completed, and under the constraint of the target analysis model, the writing formula calculation result and threshold rule anomaly identification process are executed to generate an anomaly list. Then, based on the anomaly list, the analysis snapshot document processing is executed and the download authentication process is executed to generate the authentication result, so that the anomaly list, analysis snapshot document and authentication result form a verifiable associated output in the same continuous link.

[0030] The following are its main beneficial effects:

[0031] (1) In view of the common problem that the column comparison verification results of the data base table are difficult to form a unified record in the step summary area in the background technology, by writing the column comparison verification results into the step summary area and forming a corresponding relationship with the data base table, the subsequent thread start processing based on the data base table can directly refer to the column comparison verification results in the step summary area for processing branch judgment and record inheritance, thereby reducing the repeated organization and external explanation of the column comparison verification results in the process of generating the analysis snapshot document.

[0032] (2) In view of the common problem in the background technology that the write range lacks consistent constraints with thread processing and index information generation, by synchronously completing the index information generation and write range record processing during the thread processing, the write range and the index information form a consistent input boundary description under the same processing link, and serve as the input constraint for the target analysis model selection processing and the calculation results of the writing formula driven by the analysis model library, thereby reducing the risk of model calculation depending on changes in the data base table structure and unclear processing boundaries.

[0033] (3) In view of the common problem in the background technology that there is no verifiable correspondence between the anomaly list and the analysis snapshot document and that the source is easy to be unclear under the constraints of download authentication processing, the writing range, analysis model library, target analysis model selection processing, writing formula calculation results, threshold rule anomaly identification processing, anomaly list generation, analysis snapshot document processing and download authentication processing are linked into a continuous link, so that the basis for generating the anomaly list is consistent with the encapsulation object of the analysis snapshot document, and the authentication result is output along with the analysis snapshot document, thereby supporting the consistency of the link and the traceability of records in the archiving and review scenarios of audit analysis snapshot generation. Attached Figure Description

[0034] Figure 1 This is a flowchart illustrating an audit analysis snapshot generation method that integrates Excel macros and external data sources, as provided in an embodiment of this application. Detailed Implementation

[0035] Example 1: Refer to Figure 1 This is a flowchart illustrating an audit analysis snapshot generation method that integrates Excel macros and external data sources, provided by an embodiment of the present invention. The process may include at least steps S100-S400:

[0036] S100. Obtain the financial data table exported from the external data source, load it to obtain the data base table, and perform column comparison and verification to write the results into the step summary area to generate the step summary area.

[0037] S200: Obtain the data base table and step summary area, perform thread startup processing, index information generation and write range record processing, and generate the write range;

[0038] S300. Based on the writing range and analysis model library, perform target analysis model selection processing, and execute anomaly identification processing of writing formula calculation results and threshold rule, and generate an anomaly list.

[0039] S400. Based on the anomaly list, perform analysis and snapshot document processing, execute download authentication processing, and generate authentication results.

[0040] S100. Obtain the financial data table exported from the external data source, load it to obtain the data base table, and perform column comparison and verification to write the results into the step summary area to generate the step summary area.

[0041] Specifically, this step is executed by an Excel macro program running in the Excel software. The Excel macro program is implemented using Visual Basic for Applications (VBA). It can be triggered either by the auditor selecting a preset macro name and executing it in the Excel interface, or by the Excel macro program automatically triggering it after detecting that a financial data table exported from an external data source has been imported into the current workbook. The external data source is a system that provides financial data export, and the financial data table is a table file or table content exported from the external data source, which at least contains fields related to financial subjects, periods, or amounts. The loading of the data base table process refers to importing the table content of the financial data table into the Excel workbook and forming a data base table within the workbook as input for subsequent processing. The data base table is an import table formed in Excel, whose fields and row / column structure correspond to the financial data table, and it serves as common input for subsequent steps after the import is complete. To avoid structural changes during the import process, the Excel macro program first reads the file attributes and worksheet structure of the financial data table during loading to determine the range of worksheets and data areas to be imported. Then, it copies or imports the data area to a preset worksheet location to form the base data table. This preset worksheet location can be a fixed-named form area within the workbook, or a worksheet area created and named by the Excel macro program during the initial import. Understandably, the loading process does not involve formula calculations; it only completes data transfer and area positioning. After loading, it registers the start row, end row, start column, and end column of the base data table, which is used for subsequent extraction of header information and column comparison from the base data table.

[0042] Furthermore, after forming the base data table, the Excel macro program extracts header information from the base data table and performs column comparison. The header information refers to the row content in the base data table used to describe the meaning of each column, typically located in the first row of the base data table or a header row determined by the Excel macro program according to import rules. When extracting the header information, the Excel macro program reads the header row column by column according to the registered regional positioning information, obtaining the column name and column order corresponding to each column, and combines the column name and column order to form the header information. The column comparison refers to the verification process of matching the header information column by column against a preset field list. The preset field list is a set of fields built into or configurable by the Excel macro program, used to describe the minimum set of fields required by the present invention and the allowed range of field names. The minimum set of fields at least includes the column correspondences required for subsequent steps to generate intermediate process tables. Therefore, the preset field list is not a generalized set of fields, but rather a set of field constraints corresponding to the processing chain of the present invention: "base data table → intermediate process table → result table → analysis snapshot document". The column comparison process includes: the Excel macro program normalizing each column name in the header information, which may include removing leading and trailing spaces, merging consecutive spaces, and unifying full-width and half-width characters without changing the semantics of the fields; subsequently, using the preset field list as a reference, matching judgment is performed on each column name, and the matching result is output. The matching judgment can use exact match or match within the range of allowed names registered in the preset field list; when multiple names are allowed for the same field in the preset field list, the Excel macro program maps the column name to the corresponding field entry in the preset field list and records the mapping relationship. To avoid instability in subsequent processing links caused by fields with the same name or empty fields, the Excel macro program simultaneously checks for missing columns, duplicate columns, and empty columns during the column comparison process. The missing column refers to a field column that is required to exist in the preset field list but is not found in the header information; the duplicate column refers to a situation where multiple column names in the header information match the same preset field entry; the empty column refers to a situation where the column name corresponding to the header row is empty or contains only blank characters. After identifying missing, duplicate, and empty columns, the Excel macro program organizes and outputs these as part of the column comparison verification result, thus obtaining the column comparison verification result. Understandably, the column comparison verification result not only includes a "pass / fail" judgment but also verifiable information supporting that judgment. This verifiable information includes at least a list of column names, column order, mapping relationships, and the record content of missing, duplicate, and empty columns.

[0043] In terms of operational control, the Excel macro program sets trigger and interruption conditions for column comparison. Regarding trigger conditions, when the base data table has been generated and the header information is successfully extracted, the column comparison process automatically begins; if the base data table has not been generated or the header information is empty, the Excel macro program records an anomaly and enters interrupt handling. Regarding interruption conditions, when the column comparison verification result is determined to be unsuccessful, the Excel macro program does not enter the thread processing flow of subsequent steps, but instead writes the reason for the failure into the step summary area and prompts the auditor to adjust the export method of the financial data table or re-import it; when the column comparison verification result is determined to be successful, the Excel macro program writes the "successful" status and the corresponding mapping relationship into the step summary area, and retains the base data table as the input for subsequent S200 in the current workbook. The above operational control ensures that this step forms an auditable verification checkpoint at the entry point, avoiding untraceable deviations caused by subsequent processing using data base tables with inconsistent structures.

[0044] Furthermore, the process of writing the column comparison verification results into the step summary area is executed by the Excel macro program. The step summary area is a region in the Excel workbook used to record processing information. It coexists with the data base table in the same workbook and is included as a self-contained element in the analysis snapshot document during encapsulation. Specifically, the Excel macro program locates or creates the step summary area within the workbook. This location can be based on preset region coordinates or a preset worksheet name. When the step summary area does not yet exist, the Excel macro program creates a region for recording in a preset worksheet and marks its starting row and starting column. The writing step includes: writing the column comparison verification results into the step summary area in a verifiable manner. This verifiable manner includes at least recording the header information as is, recording the mapping relationship, recording missing columns, recording duplicate columns, and recording empty columns. To avoid confusion in the step summary area due to repeated runs, the Excel macro program cleans or segments the step summary area before writing. Cleaning includes clearing the column comparison and verification result area from the previous run while retaining other recorded content. Segmented writing involves appending new record segments to the step summary area in chronological order of execution and marking the starting position of each segment. The marking can be implemented using segment titles or segment numbers, but without introducing formulas or code; it is only used to distinguish the recorded content from multiple runs. Understandably, by retaining the input and output information of column comparisons in the step summary area, the "data source structure and verification process" can be solidified together when subsequently encapsulating the analysis snapshot document, thus meeting the consistency requirements of audit analytical programs for review and archiving.

[0045] Regarding exception handling and boundary constraints, the Excel macro program sets processing paths for common input boundaries. If the financial data table contains multiple worksheets, the Excel macro program selects the worksheet containing the data area according to preset import rules and loads it, writing the selection result into the step summary area. If the header row of the financial data table contains merged cells, the Excel macro program first splits and reads the merged cells or uses the value of the top-left cell within the merged range as the column name when extracting header information, and writes this processing method into the step summary area. If the financial data table has empty rows or empty columns interspersed within the data area, the Excel macro program uses continuous non-empty areas as the standard when determining the data area, and explicitly records columns judged as empty columns in the column comparison verification result as empty columns. For duplicate columns, the Excel macro program does not directly delete duplicate columns, but records the column position and column name of the duplicate columns in the step summary area and includes them in the judgment criteria of the column comparison verification result. For missing columns, the Excel macro program records the name of the missing column and gives a failure status in the column comparison verification result, thereby blocking subsequent steps from calling the incomplete data base table. The above boundary constraints are all implemented in the form of "column comparison verification results - step summary area" without introducing new terms, so that the process of this step can be verified.

[0046] In terms of engineering implementation, the following is an operational scenario to illustrate the operation process of this step. Auditors export a financial data table for a specific period from an external data source, open the financial data table in Excel, and copy it to the corresponding workbook, or the Excel macro program selects the financial data table through the open file dialog box and executes the import. After the Excel macro program is triggered, it first loads the data area of ​​the financial data table into a preset worksheet to form a data base table and records the data area location information of the data base table; then, it extracts the header information from the first row of the data base table, obtaining the column names and column order, and compares them with a preset field list. If a field required by the preset field list is not found in the header information during the column comparison, a missing column record is created, and the column comparison verification result is determined to be unsuccessful; if the same field is found to be matched by multiple column names, a duplicate column record is created; if a column name is found to be empty, an empty column record is created. The Excel macro program writes the above records into the step summary area, and simultaneously records the status information of this operation in the step summary area, which is then included in the subsequent analysis snapshot document encapsulation. Auditors can adjust the external data source export settings and re-import based on missing, duplicate, and empty column records in the step summary area until the column comparison verification result passes. When the column comparison verification result passes, the data base table and the step summary area remain valid as input products of subsequent S200 in the same workbook. Subsequent S200 will obtain the data base table and step summary area to start thread processing, generate index information, and record the write range. Thus, this step naturally outputs the data base table and step summary area to subsequent steps. The data base table corresponds to the "data base table" of subsequent S200, and the step summary area corresponds to the "step summary area" of subsequent S200, forming an input relationship across main steps. This relationship remains traceable within the workbook in the form of fixed area and fixed field records.

[0047] In summary, this step achieves the following technical results: While forming the base data table, it extracts header information and performs column comparison verification, writing the verification results to the step summary area. This allows subsequent steps to initiate thread processing based on a structurally consistent base data table. By embedding missing, duplicate, and empty columns and their mapping relationships in the step summary area, this step provides the base data table's structural state and verification process as verifiable information directly referenced in subsequent analysis snapshot document processing. By setting interruption conditions for column comparison failures and recording them in the step summary area, this step ensures that the input boundaries of subsequent steps remain clear and auditable during operation.

[0048] S200: Obtain the data base table and step summary area, perform thread startup processing, index information generation and write range record processing, and generate the write range;

[0049] Specifically, this step is triggered when the data base table output by S100 and the step summary area have been generated in the same Excel workbook and are in a readable state. The execution subject is the Excel macro program. The triggering method can be that the auditor selects the corresponding macro name in the Excel interface to continue execution after completing the column comparison verification, or the Excel macro program automatically triggers the execution after detecting that the column comparison verification result recorded in the step summary area is in a pass state. Understandably, the input sources for this step include the data base table and the step summary area. The data base table is a load table formed by loading financial data tables exported from external data sources, and the step summary area is the area for recording column comparison and verification results. Before starting thread processing, this step first reads the column comparison and verification results in the step summary area and performs a running condition determination. When the column comparison and verification results indicate that there are missing columns or column names that cannot be mapped, the Excel macro program stops the subsequent processing of this step and writes the stop information in the step summary area. When the column comparison and verification results indicate that the verification is successful and the mapping relationship is available, the Excel macro program enters the thread startup processing flow and writes the startup time of this step, the current workbook identifier, and the area positioning information of the data base table into the corresponding record position in the step summary area, so that the running process of this step can be encapsulated together when analyzing the snapshot document.

[0050] Furthermore, the startup thread processing is used to perform structured processing on the data base table and generate an intermediate process table. The thread is a parallel or quasi-parallel execution unit scheduled by the Excel macro program within the Excel environment. It is used to process data segment by segment when the number of rows in the data base table is large or segmented processing is required. When the Excel runtime environment does not support true parallelism, the thread can be understood as a processing task executed sequentially in a queue, and each processing task follows the same input / output constraints. The implementation process of the startup thread processing includes: the Excel macro program determining the range of data to be processed based on the regional positioning information of the data base table, and generating multiple segment ranges according to preset segmentation rules; the segmentation rules can be divided by row number intervals or by period field groups, where dividing by row number intervals is one of the minimum sets essential for implementing the core link of this invention, at least satisfying the requirement of dividing the data base table into several continuous intervals and processing them segment by segment; grouping by period field is a preferred extension method, used to form segment ranges that better fit the input of the audit analysis model library when the data base table naturally contains period fields. After generating the segmented ranges, the Excel macro program creates processing tasks for each segmented range and starts them sequentially. The execution process of each processing task includes reading row data within the segmented range from the data base table, aligning the fields of each row of data according to the mapping relationship formed in stage S100, and generating intermediate process table segments containing alignment fields and alignment markers. The alignment fields refer to the field columns that are called by the subsequent analysis model library, which are copied or converted from the data base table by the Excel macro program according to the mapping relationship. The alignment markers refer to the marker content used to record the source of alignment or the reason for alignment failure. They at least include status information such as whether the mapping relationship is matched, whether the field is empty, and whether there is a duplicate column conflict, and are associated with the corresponding row data. Understandably, the minimum set of intermediate process tables should contain at least three types of columns: key field copy columns, alignment field columns, and alignment marker columns. The key field copy columns are field columns copied exactly from the data base table to maintain traceability. The alignment field columns are used as input fields when writing formula calculation results later. The alignment marker columns are used to provide alignment process records when generating an anomaly list or encapsulating analysis snapshot documents later. When there are empty rows, empty columns, or inconsistent cell formats in the base data table, the Excel macro program skips empty rows or writes null values ​​to place empty cells when reading row data, and records the corresponding status in the alignment mark. For mapping conflicts caused by duplicate columns, the Excel macro program selects one of the columns as the source of the alignment field according to the mapping priority recorded in the summary area of ​​step S100, and writes the selection into the alignment mark, so that subsequent steps can verify the alignment process.

[0051] Furthermore, after each segment range is processed, the Excel macro program executes an intermediate process table merging process, merging each intermediate process table segment by segment range to generate a complete intermediate process table, and performing a consistency check on the intermediate process table after merging. The consistency check can be implemented by checking whether the column names of the intermediate process table match the preset alignment field set, whether the alignment marker column exists, and whether the number of rows matches the number of valid rows processed. When the consistency check fails, the Excel macro program writes the error reason to the step summary area and stops generating subsequent index information. When the consistency check passes, the Excel macro program registers the worksheet position, start and end row and column ranges, alignment field column set, and alignment marker column position of the intermediate process table to the corresponding record positions in the step summary area. Understandably, the intermediate process table is an intermediate product connecting the data base table and the subsequent result table. Its generation and registration processes are both automatically completed by the Excel macro program, and the registered information provides input for subsequent index information generation and writing range record processing.

[0052] After the intermediate process table is generated, this step proceeds to index information generation and write range record processing. The index information is location information extracted from the intermediate process table, describing the organization of data within the table and its quickly accessible location relationships. Specifically, the Excel macro program reads alignment fields and alignment markers from the intermediate process table and generates index information according to preset index rules. The minimum set of index rules includes establishing index mapping relationships based on row number ranges and establishing column positioning relationships based on alignment field column positions, enabling rapid location of input fields when subsequent calculations are performed based on the write range and analysis model library. Preferably, the index rules may also include grouping indexes by period field or subject field, but this grouping index, as an extended function, is only enabled when the corresponding field exists in the intermediate process table and the column comparison mapping relationship in the step summary area contains that field. The index information generation process may include: scanning the intermediate process table row by row or by segment range, extracting the row position mapping between the copy column value and the alignment field value of each row, and organizing this mapping into an index information structure; for rows where the alignment mark indicates alignment failure, the Excel macro program writes the row position into the exception segment of the index information and records the row number range of the exception segment in the step summary area for subsequent association and display when encapsulating and analyzing snapshot documents. To avoid duplicate or omitted registrations during the index information generation process, the Excel macro program verifies the start and end row numbers of each segment range when generating the index information, and checks the consistency between the total number of rows recorded in the index information and the valid number of rows in the intermediate process table after completion; when the verification is inconsistent, the Excel macro program records the inconsistent row number range in the step summary area and stops subsequent range recording processing.

[0053] Furthermore, the write range recording process is executed in conjunction with Redis writes, where Redis is a cache storage for caching index information. Specifically, after the index information is generated, the Excel macro program determines whether to enable Redis writes; if the workbook configuration allows it and the step summary area does not record a write-prohibited field, the Excel macro program writes the index information to Redis and simultaneously generates a write range. The write range refers to the record content corresponding to the range of index information written to Redis, which at least includes the range of covered row numbers, the range of covered column positions, and the range of the number of records written. The implementation process of writing to Redis may include: serializing the index information according to a preset segment range and writing it to the specified key space of Redis, and reading the write confirmation information after the write is completed to determine the write status; when the write status is failure, the Excel macro program writes the failure reason and the failure segment to the step summary area, and records the write range as the range corresponding to the failure status; when the write status is success, the Excel macro program writes the success status and the corresponding write range to the step summary area. Understandably, even without enabling Redis writes, this step still generates a write range. In this case, the write range is used to record the coverage and availability of index information in the intermediate process table, and serves as input constraint information when S300 selects the target analysis model and executes the write formula calculation results, thereby maintaining consistency across steps without adding new terms.

[0054] Regarding the degree of operational control and automation, the Excel macro program sets a repeatable execution strategy and recording strategy for this step. Specifically, when the auditor re-imports the financial data table exported from the external data source and re-executes S100, the step summary area will record the new column comparison verification results and running status. When this step is triggered again, it first reads the latest record segment in the step summary area and decides whether to clean up the old intermediate process table and old index information accordingly. The minimum set of cleanup strategies includes at least cleaning up the old intermediate process table worksheet or emptying the old intermediate process table data area, and writing the cleanup action into the step summary area. Preferably, historical running record segments can be retained in the step summary area and new record segments can be appended, so that the analysis snapshot document processing can include multiple running traces. For anomalies that occur during thread processing, such as failure to read a certain segment range or abnormal data format of a certain row causing the alignment field to fail to be generated, the Excel macro program writes the position of the abnormal row to the alignment mark and continues to process the remaining rows, so that the intermediate process table generation process remains continuous. For consistency anomalies that affect the overall merging, such as inconsistent column sets, the Excel macro program stops the generation of subsequent index information and records the stop information in the step summary area. The aforementioned triggering conditions, cleanup strategies, and exception recording methods ensure that this step maintains a clear input-output relationship even when executed repeatedly, and provides a complete process record for subsequent analysis and snapshot document processing.

[0055] In an engineering implementation, after completing S100, the auditor sees the column comparison verification result as passed in the step summary area, and then triggers this step. The Excel macro program reads the number of rows and column ranges of the data base table, and divides the data base table into multiple segment ranges according to preset segmentation rules. Threads are started sequentially to process each segment range, generating intermediate process table segments and merging them into an intermediate process table. After merging, the Excel macro program extracts alignment fields and alignment marks from the intermediate process table to generate index information, and writes the index information to Redis, or, if Redis is not enabled, only creates index information records within the workbook. At the same time, a write range is generated and written to the step summary area. Thus, the write range is generated as the output field name of this step and is in a readable state within the same workbook. Subsequently, S300 will perform target analysis model selection processing based on the write range and the analysis model library, and call the available range and positioning relationship indicated by the write range in the write formula calculation result and threshold rule anomaly identification processing. Understandably, the write range output in this step, together with the thread processing records and intermediate process table registration information in the step summary area, constitutes an input relationship that connects across main steps, providing a clear data positioning basis for the subsequent execution of the formula calculation results.

[0056] In summary, this step achieves the following technical results: Based on the read data base table and step summary area, this step initiates thread processing and generates an intermediate process table. Simultaneously, it generates index information and establishes a write range record, enabling subsequent steps to select target analysis models and write formula calculation results based on the write range. By writing the thread processing status, intermediate process table registration information, and write range into the step summary area, this step creates a verifiable record of the intermediate processing chain's operation, which can be referenced during the analysis snapshot document processing. By recording abnormal rows with alignment markers and implementing stop and record strategies for anomalies affecting overall consistency, this step maintains the continuity and clear boundaries of the operational process.

[0057] S300. Based on the writing range and analysis model library, perform target analysis model selection processing, and execute anomaly identification processing of writing formula calculation results and threshold rule, and generate an anomaly list.

[0058] Specifically, this step is triggered when the write range output by S200 has been generated and the write range is consistent with the intermediate process table location information registered in the step summary area. The execution subject is still the Excel macro program. The Excel macro program can be triggered by the auditor selecting a preset macro name in the Excel interface, or it can be automatically triggered after a valid record segment of the write range is detected in the step summary area. Understandably, the input sources for this step include at least the write range and the analysis model library. The write range refers to the record content of the record index information write range, and the analysis model library is a preset set of analysis models bound to an Excel macro program and stored within a workbook or a storage location associated with the workbook. When this step is initiated, the Excel macro program first reads the write range and performs an availability check. The availability check includes whether the write range is empty, whether there are any failure status records, and whether the covered row number range and column position range are consistent with the range registered in the intermediate process table. If the availability check fails, the Excel macro program writes a stop message for this step in the step summary area and terminates the subsequent target analysis model selection process. If the availability check passes, the Excel macro program further reads the analysis model library and enters the target analysis model selection process, while simultaneously writing the start status of this step and the write range identifier into the step summary area for encapsulation during subsequent analysis snapshot document processing.

[0059] Furthermore, the analytical model library is a set of models used to drive the generation of result tables and anomaly identification. Its structure includes at least a set of model entries, a list of input fields corresponding to each model entry, threshold rules, and model information. The set of model entries is a preset or configurable model directory within an Excel workbook, from which auditors can select target analytical models. The list of input fields is a set of alignment fields required for each model entry, used to guide the extraction of data from the intermediate process table or the data range covered by the write range. The threshold rules are a set of anomaly identification rules bound to the model entries, used to determine anomalies after the result table is generated. The model information is a record of the model name, model version, and applicable scope, and its write location can be the model information area or the model record segment of the step summary area of ​​the result table. Understandably, the model item set and the input field list constitute the minimum set for generating the result table, the threshold rule is the minimum set for anomaly identification, and the model information is the key set of self-contained records formed during subsequent analysis and snapshot document processing. Therefore, all of the above components are essential to the core link of this invention. In contrast, the display method of the model item set, the sorting method of the model item set, and the interaction method of the auditor's selection interface are preferred extended functions that can be adjusted according to the implementation method in the Excel environment.

[0060] Specifically, the target analysis model selection process is executed by the Excel macro program. The process includes loading, displaying, and confirming the selection of the analysis model library. The loading process refers to reading the set of model entries and their corresponding input field lists, threshold rules, and model information from the model directory or associated storage location within the workbook. The display process refers to providing a list of selectable model entries in the Excel interface, which can be achieved through a data validation dropdown, worksheet selection area, or macro pop-up. The confirmation process involves the Excel macro program recording the identifier of the target analysis model and locking this identifier for subsequent input formula calculation results and threshold rule anomaly identification after the auditor selects a model entry. To avoid mismatches between model selection and the input range leading to interruptions in subsequent processing, the Excel macro program performs a matching check between the input field list of the target analysis model and the column position range covered by the input range after confirmation. If all fields in the input field list can be located within the input range, the match is considered successful; if any fields cannot be located, the match is considered unsuccessful, and the reason for the unsuccessful match is written to the step summary area, while the confirmation is revoked. Understandably, this matching check is one of the triggering conditions for the stable operation of this step, and it does not introduce new terms, but only establishes a consistency relationship between "input field list - write range - intermediate process table location information".

[0061] Further, after the target analysis model selection process is completed and the matching verification passes, this step proceeds to the process of writing the formula calculation results. Writing the formula calculation results refers to the Excel macro program extracting data from the data range covered by the writing range according to the input field list of the target analysis model, generating a result table in the Excel workbook, and writing the calculation results to the result table. The result table is a form area formed in Excel to carry the calculation output, which at least includes an indicator field column, a location field column, and a model information area, and is bound to the target analysis model. The implementation of writing the formula calculation results includes at least two technical solutions. Technical solution one involves the Excel macro program writing a calculation expression based on cell references within a preset cell area of ​​the result table and triggering the calculation, thereby obtaining the calculation result and writing it to the result table; in this solution, the preset cell area is a fixed area in the result table used to carry the calculation output, and its range can be preset by the model entry and registered by the Excel macro program in the step summary area. Technical Solution Two involves the Excel macro program directly reading the data covered by the write range and generating result values ​​according to the calculation steps defined in the model entries, then writing them into the result table. In this solution, the key reference relationships of the calculation steps are still written into the step summary area to maintain verifiability. Understandably, regardless of whether Technical Solution One or Technical Solution Two is used, the following execution sequence must be met: "extracting input fields from the write range—generating the result table—writing the calculation results—writing model information," and the correspondence between the write range and the list of input fields is maintained throughout this step. To avoid confusion caused by repeated generation of the result table, the Excel macro program first checks whether a previous result table exists in the workbook before generating the result table. If it exists and the model information recorded in the step summary area is inconsistent with the current target analysis model, the previous result table area is cleared and regenerated. If it exists and is consistent, a new calculation block is appended to the result table, and the starting position of the appended block is recorded in the step summary area. The above cleanup or appending strategy is a preferred extended function, and the essential minimum set for this step is "result table generation and calculation result writing" and "model information writing."

[0062] Simultaneously with or after writing the calculation results, the Excel macro program writes model information to the results table or the step summary area. Specifically, the model information includes at least the name and version of the target analysis model, and may further include the scope of application; the name and model version are both from model entry records in the analysis model library, and are associated with the write scope identifier during writing. The model information can be written to the model information area of ​​the results table or to the model record segment in the step summary area, and this writing location is retained as important content encapsulated during subsequent analysis snapshot document processing. Understandably, model information writing is a component of "process traceability," and together with the column comparison verification results of S100 and the write scope record of S200, it constitutes a cross-step record chain.

[0063] Further, after the result table is generated and the calculation results are written, this step proceeds to threshold rule anomaly identification processing and generates an anomaly list. The threshold rules are a set of anomaly identification rules corresponding to the target analysis model, preset on a per-model-item basis and configurable in the analysis model library. Each threshold rule includes at least threshold conditions for judging anomalies and corresponding anomaly identification methods, and corresponds to the indicator fields in the result table. The threshold rule anomaly identification processing is executed by the Excel macro program. The process includes extracting the indicator field values ​​corresponding to the threshold rules from the result table and performing threshold condition judgments item by item. When the threshold conditions are met, the Excel macro program highlights the corresponding item in the result table and generates an anomaly identification record associated with that item. When the threshold conditions are not met, the Excel macro program records the status as non-abnormal and does not generate an anomaly identification record. To maintain the verifiability of the anomaly identification process, the Excel macro program simultaneously records the correspondence between trigger threshold conditions when generating anomaly identification records. This correspondence includes anomaly item location information and threshold condition summary information. The anomaly item location information indicates the row and column position of the anomaly item in the result table and can be associated with the row number range covered by the writing range for traceability. The threshold condition summary information indicates the threshold rule entry on which the anomaly identification record is based and can register the rule entry identifier and parameter value in the step summary area. Understandably, the above recording method does not introduce new terminology and revolves around the link of "result table—threshold rule—highlighted identifier—anomaly list," satisfying the constraint that this step uses only established terminology.

[0064] The anomaly list is a list area or list table generated by the Excel macro program based on anomaly identifier records. It can exist independently of the result table in a preset area within the workbook, or it can exist as an anomaly summary block within the result table. The minimum set of the anomaly list must contain at least the correspondence between the anomaly item location information and the trigger threshold conditions, and must be consistent with the highlighted identifiers in the result table. Preferably, the anomaly list may also include associated write range identifiers and the name of the target analysis model, thereby forming a more complete self-contained record during subsequent analysis snapshot document processing. The anomaly list generation process includes: the Excel macro program scans the anomaly identifier records generated by this anomaly identification, writes them into the anomaly list area in a preset order, and performs deduplication verification and sorting processing on the anomaly list area after writing is complete. Deduplication verification is used to prevent the same result table item from being repeatedly written into the anomaly list due to multiple threshold rules, and sorting processing is used to arrange the anomaly list according to the result table row number or threshold rule entry identifier. In the case where there are no anomaly identifier records, the Excel macro program still generates an anomaly list and writes it to an empty list state, while simultaneously recording the anomaly identification completion status and the empty list status in the step summary area. Understandably, the empty list status, as an output form of the exception list, still meets the input requirements of the subsequent S400 for the exception list, ensuring consistency in the connection between main steps.

[0065] Regarding operation control and exception handling, the Excel macro program sets a combination strategy for the degree of automation and triggering conditions for this step. Specifically, when the write range record is in a failed state or the write range coverage is inconsistent with the range registered in the intermediate process table, this step does not proceed to the target analysis model selection process; when the auditor changes the target analysis model in the model selection interface, the Excel macro program re-executes the matching verification between the input field list and the write range coverage, and blocks the writing formula calculation result when the matching fails; when a preset cell area is detected to be occupied or in an unwriteable state during the result table generation process, the Excel macro program records the unwriteable state in the step summary area and stops exception identification to avoid inconsistencies between the exception list and the result table. Preferably, the Excel macro program sets a model version management strategy for the analysis model library. The model version management strategy includes at least registering the model version in the model entries of the analysis model library and writing the model version to the step summary area and the model information area of ​​the result table each time the target analysis model selection process is performed; when a change in the model version is detected, the Excel macro program appends a new model record segment to the step summary area and marks the change time, so that the correspondence between the model version and the exception list can be seen when the analysis snapshot document is processed. Understandably, the model version management strategy prioritizes extended functionality, and the essential minimum set for this step is the order and recording method of the target analysis model selection, result table generation, and exception list generation.

[0066] In an engineering implementation, after completing S200, the auditor sees in the step summary area that the write range has been generated and is available, and then triggers this step. The Excel macro program reads the write range and loads the analysis model library, displaying selectable models in the model entry list. After the auditor selects one as the target analysis model, the Excel macro program reads the list of input fields corresponding to the model and matches and verifies it against the write range coverage. If successful, it generates a result table in the workbook and writes the calculation results, along with the model information. Subsequently, the Excel macro program performs anomaly identification on the result table according to the threshold rules corresponding to the model, highlights items that meet the threshold conditions, and summarizes the anomaly identification records into an anomaly list. The anomaly list is maintained in the workbook and is available for reading. Thus, the anomaly list is generated as the output field name of this step and is called as the input source for the subsequent S400. The subsequent S400 will perform analysis snapshot document processing and download authentication processing based on the anomaly list. At the same time, the model information and threshold rule records registered in the step summary area will form an associated record with the anomaly list and be encapsulated together in the subsequent analysis snapshot document processing, thereby completing a natural connection across main steps.

[0067] In summary, this step selects the target analysis model based on the read / write range and analysis model library, generates a results table, and writes the calculation results and model information. Then, it identifies anomalies according to threshold rules and generates an anomaly list, providing a clear input for subsequent analysis snapshot document processing. By associating model information with threshold rules and recording them in the step summary area, corresponding to the anomaly list, this step establishes a verifiable record chain for the model selection and anomaly identification process. By setting trigger conditions for verifying the availability of the write range and matching the input field list, and recording stop information in case of anomalies, this step ensures that the input boundaries for subsequent steps calling the anomaly list remain clear.

[0068] S400. Based on the anomaly list, perform analysis and snapshot document processing, execute download authentication processing, and generate authentication results.

[0069] Specifically, this step is triggered when the anomaly list output by S300 has been generated and is consistent with the highlighted identifier of the result table, and when the model information and threshold rule record segments of the target analysis model have been recorded in the step summary area. The execution entity is the Excel macro program. The Excel macro program can be triggered by the auditor selecting the corresponding macro name in the Excel interface, or it can be automatically triggered after the anomaly list completion status record is detected. Understandably, the input sources of this step include at least the anomaly list, and further reference the data base table, the intermediate process table, the result table, and the step summary area already formed in the same workbook. Among them, the anomaly list is a list area or list table generated by the threshold rule anomaly identification and processing summary; the data base table is a loading table formed by loading financial data tables exported from external data sources; the intermediate process table is a processing table generated by the starting thread and containing alignment fields and alignment marks; the result table is an output table that writes the formula calculation results and writes the model information; and the step summary area is an area for recording column comparison and verification results, thread processing records, writing range, model information, and threshold rule records. At the start of this step, the Excel macro program first reads the exception list and performs pre-packaging verification. This pre-packaging verification includes checking if the exception list exists, whether it is empty, and whether the location information of the exception items recorded in the exception list can point back to the highlighted position in the results table. If the verification fails, the Excel macro program writes the reason for the failure to the step summary area and stops subsequent snapshot document analysis. If the verification passes, the Excel macro program enters the snapshot document analysis process and writes the start status of this step, the current workbook identifier, and the exception list identifier to the corresponding record segment in the step summary area, providing input for subsequent download authentication processing and archiving records.

[0070] Furthermore, the analysis snapshot document processing refers to the process by which the Excel macro program encapsulates the data base table, the intermediate process table, the result table, the exception list, and the step summary area into an analysis snapshot document. The analysis snapshot document is a self-contained Excel file, meaning that the data base table, intermediate process table, result table, exception list, and step summary area coexist in the same file, and that there are associative positioning and record relationships between these objects. Specifically, during encapsulation, the Excel macro program first generates a file container for the analysis snapshot document and configures its basic attributes. The file container can be a newly created workbook file or a workbook file copied from the current workbook. When using a newly created workbook file, the Excel macro program creates a worksheet corresponding to the original workbook in the new workbook file and writes data in a preset order. When using a copy generation method, the Excel macro program copies the worksheet containing the data base table, intermediate process table, result table, exception list, and step summary area, and performs encapsulation verification after copying. Understandably, the method of generating file containers is a difference in implementation. As a preferred extension function, it can be selected according to the actual environment. However, the minimum set of analysis snapshot documents is the structural requirement of "encapsulating the data base table, intermediate process table, result table, exception list and step summary area in the same Excel file". Therefore, regardless of the generation method, this minimum set must be met.

[0071] In the encapsulation and writing process, the Excel macro program processes the encapsulation order and associated records of each component. Specifically, the Excel macro program first writes the base data table to the analysis snapshot document, retaining the header information and data area positioning information of the base data table, and simultaneously records the writing position of the base data table in the snapshot record segment of the step summary area; then, the Excel macro program writes the intermediate process table to the analysis snapshot document, retaining the alignment fields and alignment marks, and writes the worksheet position and alignment mark column position of the intermediate process table to the snapshot record segment of the step summary area; next, the Excel macro program writes the result table to the analysis snapshot document, retaining the written formula calculation result area, model information area, and highlight status, and writes the model information and preset cell area positioning information of the result table to the snapshot record segment of the step summary area; finally, the Excel macro program writes the exception list to the analysis snapshot document, and registers the correspondence between the exception list and the highlight status of the result table. Understandably, the above encapsulation order and registration process are used to maintain the closed-loop structure of "data base table - intermediate process table - result table - exception list - step summary area", so that after the analysis snapshot document is previewed and downloaded, the reviewer can still locate the highlighted position of the result table corresponding to the exception list based on the step summary area, and further trace back to the alignment mark of the intermediate process table and the source data position of the data base table.

[0072] Furthermore, to ensure the analysis snapshot document has a traceable process record, the Excel macro program not only copies the content of the step summary area when encapsulating it, but also appends encapsulation identifiers. Specifically, the step summary area has already been written with column comparison verification results, thread processing records, index information, write range, model information, and threshold rule records in stages S100 to S300; in this step, during encapsulation, the snapshot record segment of the step summary area is appended with record fields such as "analysis snapshot document identifier, encapsulation time, encapsulation status, preview status, and authentication result". The analysis snapshot document identifier is an identifier used to distinguish different snapshot documents, which can be generated by combining the workbook identifier and the encapsulation time and written into the step summary area; the encapsulation time is the time record of the encapsulation action execution; the encapsulation status is the status record of whether the encapsulation was successful or failed. When an exception occurs during the encapsulation process, such as worksheet creation failure, write failure, or missing location information, the Excel macro program writes the reason for the encapsulation failure into the step summary area and terminates the subsequent preview and download authentication processes. At the same time, it performs cleanup processing on the created analysis snapshot document or retains it as a failed snapshot document and writes the failure status. The cleanup or retention strategy is a preferred extended function, but the encapsulation status record is part of the minimum set of this step. The encapsulation status record is used to determine whether to allow entry into the preview process and download authentication process.

[0073] Upon successful processing of the analysis snapshot document and a successful encapsulation status, this step proceeds to preview and download authentication. The preview is a process of displaying key content of the analysis snapshot document before downloading. This key content includes at least the result table area corresponding to the exception list and highlighted indicators. Specifically, the Excel macro program opens or switches to the analysis snapshot document during the preview stage, extracts the exception list and highlighted indicator information from the document, locates the corresponding highlighted indicator position in the result table according to the exception item location information in the exception list, and displays it. The display method can be automatic jump to the highlighted indicator cell area in the result table or providing a jump entry in the exception list. After the preview display, the Excel macro program records the preview pass status. The preview pass status is a status record generated after the auditor confirms pass in the preview interface and is written to the snapshot record segment in the step summary area. Understandably, the preview pass status is one of the trigger conditions for entering the download authentication process. When the preview pass status is not generated or recorded as fail, the Excel macro program blocks the download authentication process and writes the blocking reason in the step summary area.

[0074] Furthermore, the download authentication process includes two parts: download processing and authentication processing. The download processing involves saving the analysis snapshot document to a specified location or outputting it as a downloadable file. The specified location can be a local path or a storage path configured by the system. The authentication processing involves authenticating the user previewing or downloading the document and generating an authentication result. Specifically, before the download process begins, the Excel macro program reads the preview's pass status and confirms it is pass, then generates a download request and triggers the authentication process. The authentication process can be performed by the Excel macro program calling a preset authentication interface or reading preset authentication information. The authentication information may include an account identifier, password, or system-bound authentication information. The authentication result is a record of whether the authentication is successful or failed. It is generated by the Excel macro program and written into the snapshot record segment of the step summary area, while a verifiable record is formed in the step summary area within the analysis snapshot document. When the authentication result is successful, the Excel macro program executes the download process, saving the analysis snapshot document and recording the save location identifier. When the authentication result is failed, the Excel macro program stops the download process and writes the reason for the failure into the step summary area. Understandably, the authentication result is generated as the output field name for this step, and this output is not only used for the record at the end of this step, but can also be called by subsequent archiving or review processes; the record link of the step summary area formed in stages S100 to S300 continues to extend to the preview and authentication records in this step, so that the generation and output process of the analysis snapshot document has a complete record loop.

[0075] Regarding automation and triggering conditions, the Excel macro program is configured with a strategy for repeatable execution and stable operation under version evolution for this step. Specifically, when the auditor re-executes S300 to generate a new anomaly list on the same data base table, the Excel macro program reads the model information and threshold rule records in the step summary area before this step is triggered, and determines whether the current anomaly list is consistent with the result table; if consistent, it allows the generation of a new analysis snapshot document and updates the analysis snapshot document identifier; if inconsistent, it requires re-execution of the result table to write the formula calculation result or re-execution of threshold rule anomaly identification and records the reason for inconsistency in the step summary area. Preferably, the Excel macro program can retain the historical analysis snapshot document identifier and the corresponding authentication result record segment in the step summary area, and add a new record segment when a new encapsulation action occurs, thereby forming a record trajectory of multiple snapshot outputs. Understandably, the retention of historical record segments is a preferred extended function, and the minimum set of this step is "encapsulating analysis snapshot document - recording preview pass status - generating authentication result", so at least the above output relationship should be completed in any implementation.

[0076] In the engineering implementation, after completing S300, the auditor sees the generated exception list in the workbook, which matches the highlighted identifier in the result table, and then triggers this step. The Excel macro program reads the exception list and the step summary area, performs pre-packaging verification, and generates an analysis snapshot document file container. It writes the data base table, intermediate process table, result table, exception list, and step summary area into the analysis snapshot document, and writes the analysis snapshot document identifier and packaging status into the step summary area. After successful packaging, the Excel macro program enters the preview stage, displays the exception list, and jumps to the highlighted identifier position of the result table based on the exception item location information. After the auditor confirms, a preview pass status is generated and written into the step summary area. Subsequently, the Excel macro program triggers authentication processing, generates an authentication result, and writes it into the step summary area. After successful authentication, the download process is executed and the analysis snapshot document is saved. Thus, the authentication result is generated as the output field name of this step and written into the step summary area within the analysis snapshot document. This authentication result, together with the analysis snapshot document identifier, packaging status, and preview pass status, forms a complete record chain of packaged output.

[0077] In summary, this step, building upon the completed exception list generation, encapsulates the base data table, intermediate process table, result table, exception list, and step summary area into an analysis snapshot document. The encapsulation status, preview pass status, and authentication result are written to the step summary area, giving the analysis snapshot document a self-contained record structure. By locating and displaying the correspondence between the exception list and the highlighted indicators in the result table during the preview stage and recording the preview pass status, this step establishes a clear trigger relationship between the download action and preview confirmation. By performing authentication before downloading and generating an authentication result record, this step incorporates the output process of the analysis snapshot document into a traceable record chain, forming a closed loop with the preceding steps.

Claims

1. A method for generating audit analysis snapshots that integrates Excel macros and external data sources, characterized in that, include: S100. Obtain the financial data table exported from the external data source, load it to obtain the data base table, and perform column comparison and verification to write the results into the step summary area to generate the step summary area. S200: Obtain the data base table and step summary area, perform thread startup processing, index information generation and write range record processing, and generate the write range; S300. Based on the writing range and analysis model library, perform target analysis model selection processing, and execute anomaly identification processing of writing formula calculation results and threshold rule, and generate an anomaly list. S400. Based on the anomaly list, perform analysis and snapshot document processing, execute download authentication processing, and generate authentication results.

2. The method according to claim 1, characterized in that, The process of obtaining financial data tables exported from external data sources and loading them to obtain the base data table includes: The system retrieves financial data tables exported from external data sources and performs loading processing. This loading process includes reading the file attributes and worksheet structure of the financial data tables to determine the scope of the worksheets to be imported and the data areas, copying or importing the data areas to preset worksheet locations to form a data base table, and registering the area location information of the data base table.

3. The method according to claim 1, characterized in that, The process of writing the column comparison and verification results into the step summary area includes: The column comparison and verification process includes extracting header information from the base data table, normalizing the column names in the header information, matching the normalized column names with a preset field list to output the matching results, and checking for missing, duplicate, and empty columns to generate column comparison and verification results. The column comparison and verification results are then written into the step summary area. This writing process includes writing the header information, mapping relationships, missing column records, duplicate column records, and empty column records into the step summary area in a verifiable manner, and cleaning or writing in segments before writing to the step summary area; finally, the step summary area is generated.

4. The method according to claim 1, characterized in that, The process of obtaining the data base table and the step summary area, and then starting the thread processing includes: The data base table and step summary area are obtained, and thread startup processing is performed. The thread startup processing includes determining the range of data to be processed based on the area positioning information of the data base table, dividing the data range into multiple segment ranges according to preset segmentation rules, creating a processing task for each segment range to read row data from the data base table and perform field alignment according to the mapping relationship, generating intermediate process table segments containing alignment fields, key field copy columns, and alignment mark columns, and merging each intermediate process table segment into a complete intermediate process table and performing a consistency check.

5. The method according to claim 1, characterized in that, Index information generation and write range record processing: The process of generating a write range includes: The index information generation process includes reading alignment fields and alignment markers from the intermediate process table, generating index information according to preset index rules, which include establishing index mapping relationships based on row number ranges and establishing column positioning relationships based on alignment field column positions; and performing write range record processing, which includes serializing the index information and writing it to the Redis cache to generate a write range when Redis write is enabled, or directly generating a write range based on the index information when Redis write is not enabled. The write range includes the range of row numbers covered by the index information, the range of column positions covered, and the range of the number of records written.

6. The method according to claim 1, characterized in that, The process of selecting a target analysis model based on the writing range and analysis model library includes: Based on the writing range and analysis model library, a target analysis model selection process is performed. The target analysis model selection process includes loading the set of model entries in the analysis model library and their corresponding list of input fields, threshold rules and model information. The list of model entries is displayed in the Excel interface for selection. After selection, the position range of the covered column of the input field list and the writing range are matched and verified. After the verification is passed, the target analysis model is locked.

7. The method according to claim 1, characterized in that, The process of performing anomaly detection and handling of the calculation results written into the formula and the threshold rule includes: The process of writing the formula calculation results includes extracting data from the data range covered by the writing range according to the input field list of the target analysis model, generating a result table in an Excel workbook and writing the calculation results into the result table, while writing model information into the result table or the step summary area; and performing threshold rule anomaly identification processing, which includes scanning the result table according to the threshold rules of the target analysis model, highlighting items that meet the threshold conditions and generating anomaly identification records, and generating an anomaly list based on the anomaly identification records.

8. The method according to claim 7, characterized in that, The exception list includes: The anomaly list contains the correspondence between the location information of the anomaly items and the trigger threshold conditions.

9. The method according to claim 1, characterized in that, Based on the aforementioned anomaly list, the process of analyzing snapshot documents includes: Based on the anomaly list, an analysis snapshot document is processed. The analysis snapshot document processing includes reading the data base table, intermediate process table, result table, anomaly list, and step summary area in the same workbook, generating a self-contained Excel file as the analysis snapshot document, and encapsulating the data base table, intermediate process table, result table, anomaly list, and step summary area into the analysis snapshot document in sequence. At the same time, the analysis snapshot document identifier, encapsulation time, and encapsulation status are appended to the step summary area.

10. The method according to claim 1, characterized in that, The process of performing download authentication and generating authentication results includes: The download authentication process includes entering the preview stage when the encapsulation status is successful, displaying the exception list and result table in the highlighted area, triggering authentication after generating the preview pass status, executing the download process after successful authentication, and writing the authentication result to the step summary area.

Images