A data efficient re-aggregation method for bank statements

Abstract

The application provides a bank statement data efficient collection method, comprising the following steps: A, determining a standard table; B, data preprocessing; C, data reading and processing; D, table header and field judgment; E, date and time processing; F, large model interaction and account processing; G, amount and character processing; H, data import and verification, the present application builds a standardized collection process, covers data preprocessing, format conversion, column name matching and high-speed import, and with the help of large model intelligent analysis technology, can effectively process various formats of bank statement data, ensure the accuracy and traceability of data; the method supports the analysis and conversion of multiple file formats such as Excel, CSV and PDF, and realizes fast data import by using the LOAD DATA INFILE function of MySQL, and further improves the efficiency and reliability of data collection by combining large model intelligent matching, provides strong data support for discipline inspection and supervision work, meets the efficient and accurate data processing requirements of discipline inspection and supervision departments when calling bank statements of evidence objects.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a method for efficiently collecting data from bank statements. Background Technology

[0002] In the disciplinary inspection and supervision process, obtaining bank statements from the subjects of investigation is a crucial step. However, there are currently a series of problems in the collection of bank statement data. On the one hand, traditional data collection methods are extremely inefficient, requiring staff to spend a significant amount of time and energy on tedious data processing, which seriously affects the overall progress of disciplinary inspection and supervision work. On the other hand, the formats of bank statement data provided by different banks vary greatly, lacking unified standards, which makes data integration and analysis exceptionally complex and significantly increases the difficulty of data processing. Moreover, existing processing methods are insufficient in ensuring data accuracy and integrity, making it difficult to meet the stringent data requirements of disciplinary inspection and supervision work. Therefore, it is necessary to design an efficient method for collecting bank statement data. Summary of the Invention

[0003] This application provides an efficient data collection method for bank statements to address the shortcomings of existing processing methods in ensuring data accuracy and integrity, which makes it difficult to meet the strict data requirements of discipline inspection and supervision work. It achieves this by constructing a standardized collection process that covers data preprocessing, format conversion, column name matching, and high-speed import, and by leveraging large-scale intelligent analysis technology, it can effectively process bank statement data in various formats, ensuring data accuracy and traceability.

[0004] This application provides a method for efficiently collecting bank statement data, including the following steps:

[0005] A. Determine the standard table;

[0006] B. Data preprocessing;

[0007] C. Data reading and processing;

[0008] D. Decisions regarding table headers and fields;

[0009] E. Date and time processing;

[0010] F. Large-scale model interaction and account processing;

[0011] G. Amount and character processing;

[0012] H. Data import and verification.

[0013] Preferably, step A specifies a standard table structure for data collection, whose fields include id (auto-incrementing primary key), case number, local account number, local account name, counterparty account number, counterparty account name, transaction amount, transaction date, transaction time, summary, remarks, gap flag, currency, bank name, original file name for data collection, and data source. The original file name for data collection and the data source are primarily used to trace the data source and ensure that information is not erroneously processed during audit data processing. The data types for each field are set as follows: the original file name for data collection is varchar(1024), the remarks are text, and the remaining fields are varchar.

[0014] Preferably, the data preprocessing in step B includes the following methods:

[0015] a. Create folders and name files: Create corresponding folders based on case number, bank name, and account holder's name. Name the bank statement records retrieved from the financial institution according to the standard rule of "account holder's name - account holder's name - currency" and put them into the corresponding folders.

[0016] b. Editing Excel files: For Excel files, first move the header to the first row; if an Excel file contains data from multiple accounts, merge these data into one table; delete the rows at the end of the table that do not belong to the statement content, and then save the processed file as "Modify - original Excel file name";

[0017] c. Format Conversion: Use pandas and openpyxl libraries to convert Excel files to CSV format. The specific steps are as follows: recursively obtain Excel files in the case directory, use pandas to read the Excel files and get the names of all worksheets, then iterate through each worksheet and save it as a CSV file. The CSV file name is constructed according to the rule "_excel file_sheet name.csv". Finally, save the data frame as a CSV file. For PDF files, use the Tabula library to extract the table data and convert it to CSV format.

[0018] Preferably, in step C, Python is used to read the first row of the file header and the second to fifth rows of data. If there are fewer than five rows of data, all data is read and the column number is recorded. Each column in the second to fifth rows of data is compared with the local account and local account. If the names match, the local account and local account are identified. The local large model is called, and the header table is queried first. If a template exists, the MySQL load data infile import statement is generated based on the load and insert statements defined in the template and imported into the original table. Then, the insert into select statement is used to import the data into the standard table. During the data processing, the time format is standardized, requiring the transaction date to be in YYYY-MM-DD format and the transaction time to be in hh24:mi:ss format. For the transaction amount, "+" is marked when the local account is receiving income and "-" is marked when it is spending.

[0019] Preferably, step D includes the following process:

[0020] 1) Header judgment: By iteratively using a string statistics algorithm, all lines in which more than 90% of the text is Chinese or English characters are filtered out and labeled with a header.

[0021] 2) Field matching: Case number and bank name are obtained from the directory name; original file name and account number are obtained from the file name; balance is obtained from the thesaurus; transaction date and time are obtained based on synonyms and data representation; counterparty account and counterparty name are obtained from the thesaurus and the first five rows of audit data that are not from our side. If no match can be found, the corresponding column is obtained by communicating with the large model, and the relevant information is recorded in the header table and the thesaurus; the summary is also obtained from the thesaurus.

[0022] 3) Other field processing: The remarks field is organized and stored in JSON format according to the other columns through the header column: content; the data source is uniformly set to "letter sending method". If it is a local bank statement customized interface, it is "xx bank interface method". The interface call is made to pull statement records by name, ID number, bank account number, and company name; the currency is RMB by default and is obtained from the file name. If it is not specified in the file name, the default value is used; foreign currency account file names need to add "-currency" at the end.

[0023] Preferably, in step E, a custom MySQL function is used to match and process various date formats, including yyyymmdd, yyyy.mm.dd, mm / dd / yyyy, yyyy / mm / dd, and the above formats with two digits of yy, and uniformly returned as yyyy-mm-dd format. In terms of time processing, yymmddhhmiss, hh:mi:ss and yyyymmdd yyyy.mm.ddmm / dd / yyyy yyyy / mm / ddhh:mi:ss formats are supported.

[0024] Preferably, in step F, the first 5 rows of anonymized data are transmitted to the large model interface, and the standard table data columns are informed to the large model. The large model is asked to determine whether it is a bank statement and to provide the column correspondence with the standard table, as well as the date and time format. The account is verified according to the account verification rules. If a card number exists and the account of the party also exists, the card number is written into the remarks field, and the card number in the remarks field is used as a virtual column of the standard table.

[0025] Preferably, step G uses the REPLACE function in MySQL to replace commas, currency symbols, spaces, and other special characters in the transaction, converting it into a digital form.

[0026] Preferably, in step H, the `load data infile` statement generated by the large model is executed to import data into the original table. If the import fails, Python is used to communicate with the large model to generate code, which is then executed to complete the data import. Using defined delimiters and newline characters, the data (including currency, bank name, original file name, case number, and data source) is imported into the original table. The `update` statement replaces the currency, bank name, original file name, case number, and default data source extracted from the folder and filename. Based on established correspondences and rules, a MySQL `select` statement is used to check for issues with the transaction amount. Specifically, the number of rows in `select transaction amount 1.0 from standard table where transaction amount 1.0>-100000000000000 and case number = this case number` is compared with the result of `select count(*) from standard table where case number = this case number`. Simultaneously, sorted by account number and ID, the sum of the previous row's balance and transaction amount is checked against the current row's balance. Inconsistencies are marked as gaps requiring manual auditing. The `load data infile` and `insert into` statements are then used to check if they match. The SELECT statement records data to the header table, including column mapping rules and column replace rules.

[0027] Beneficial Effects: This invention constructs a standardized data collection process, covering data preprocessing, format conversion, column name matching, and high-speed import. Utilizing large-scale intelligent analysis technology, it effectively processes bank statement data in various formats, ensuring data accuracy and traceability. This method supports the parsing and conversion of multiple file formats such as Excel, CSV, and PDF, and leverages MySQL's LOAD DATA INFILE function for rapid data import. Combined with large-scale intelligent matching, it further improves the efficiency and reliability of data collection, providing strong data support for disciplinary inspection and supervision work and meeting the needs of disciplinary inspection and supervision departments for efficient and accurate data processing when retrieving bank statements from evidence-gathering targets.

[0028] The above description is merely an overview of the technical solutions of the embodiments of this application. In order to better understand the technical means of the embodiments of this application and to implement them in accordance with the contents of the specification, and to make the above and other objects, features and advantages of the embodiments of this application more obvious and understandable, specific implementation methods of this application are described below. Attached Figure Description

[0029] To more clearly illustrate the technical solutions of the embodiments of this application, the drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0030] Figure 1 This is a flowchart of the workflow of the present invention. Detailed Implementation

[0031] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0032] The term "embodiment" as used herein means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of this application. The appearance of the phrase "embodiment" in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0033] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings.

[0034] Please see Figure 1 This invention discloses an efficient data collection method for bank statements, comprising the following steps:

[0035] A. Determine the standard table;

[0036] B. Data preprocessing;

[0037] C. Data reading and processing;

[0038] D. Decisions regarding table headers and fields;

[0039] E. Date and time processing;

[0040] F. Large-scale model interaction and account processing;

[0041] G. Amount and character processing;

[0042] H. Data import and verification.

[0043] In this invention, step A specifies the standard table structure used for data collection, whose fields include id (auto-incrementing primary key), case number, local account number, local account name, counterparty account number, counterparty account name, transaction amount, transaction date, transaction time, summary, remarks, gap flag, currency, bank name, original file name of the collection, and data source. The original file name of the collection and the data source are primarily used to trace the data source and ensure that information is not erroneously processed during the audit data processing. The data types of each field are set as follows: the original file name of the collection is varchar(1024), the remarks are of type text, and the remaining fields are all varchar.

[0044] In this invention, the data preprocessing in step B includes the following methods:

[0045] a. Create folders and name files: Create corresponding folders based on case number, bank name, and account holder's name. Name the bank statement records retrieved from the financial institution according to the standard rule of "account holder's name - account holder's name - currency" and put them into the corresponding folders.

[0046] b. Editing Excel files: For Excel files, first move the header to the first row; if an Excel file contains data from multiple accounts, merge these data into one table; delete the rows at the end of the table that do not belong to the statement content, and then save the processed file as "Modify - original Excel file name";

[0047] c. Format Conversion: Using pandas and openpyxl libraries, Excel files are converted to CSV format. The specific steps are as follows: recursively retrieve Excel files from the case directory, use pandas to read the Excel files and obtain all worksheet names, then iterate through each worksheet and save it as a CSV file. The CSV file name is constructed according to the rule "_excel file_sheet name.csv". Finally, the data frame is saved as a CSV file. For PDF files, the Tabula library is used to extract the table data and convert it to CSV format. The operation method is to recursively retrieve PDF files from the case directory, use tabula.read_pdf(pdf file, pages='all', multiple_tables=True) to read the PDF files, output or save each table and save it as a CSV file. For CSV and TXT format files, the delimiter is intelligently determined (the delimiter in the ASC list, such as |, tab, etc., is determined by frequency statistics in the CSV file), and converted to standard CSV format, saved as "modify_csv (or TXT) file", and the column count is recorded.

[0048] In this invention, in step C, Python is used to read the first row of the file (header) and the second to fifth rows of data. If there are fewer than five rows of data, all data is read, and the column count is recorded. Each column in the second to fifth rows of data is compared with the local account and the local account holder. If the names match, the local account and the local account holder are identified. The local large model is called, and the header table is queried first. If a template exists, the MySQL `load data infile` import statement is generated based on the `load` and `insert` statements defined in the template and imported into the original table. Then, the `insert into select` statement is used to import the data into the standard table. During data processing, the time format is standardized, requiring the transaction date to be in YYYY-MM-DD format and the transaction time to be in hh24:mi:ss format. For the transaction amount, "+" is marked when the local account holder is receiving income, and "-" is marked when it is spending. The remarks field is used to store data that cannot be mapped to other columns in the original statement, and it is stored in JSON format as {column name: content separated by commas}.

[0049] In this invention, step D includes the following process:

[0050] 1) Header judgment: By iteratively using a string statistics algorithm, all lines in which more than 90% of the text is Chinese or English characters are filtered out and labeled with a header.

[0051] 2) Field matching: Case number and bank name are obtained from the directory name; original file name and account number are obtained from the file name; balance is obtained from the thesaurus; transaction date and time are obtained based on synonyms and data representation; counterparty account and counterparty name are obtained from the thesaurus and the first five rows of audit data that are not from our side. If no match can be found, the corresponding column is obtained by communicating with the large model, and the relevant information is recorded in the header table and the thesaurus; the summary is also obtained from the thesaurus.

[0052] 3) Other field processing: The remarks field is organized and stored in JSON format according to the other columns through the header column: content; the data source is uniformly set to "letter sending method". If it is a local bank statement customized interface, it is "xx bank interface method". The interface call is made to pull statement records by name, ID number, bank account number, and company name; the currency is RMB by default and is obtained from the file name. If it is not specified in the file name, the default value is used; foreign currency account file names need to add "-currency" at the end.

[0053] In this invention, step E utilizes a custom MySQL function to match and process various date formats, including yyyymmdd, yyyy.mm.dd, mm / dd / yyyy, yyyy / mm / dd, and the above formats with two digits of yy, uniformly returning them as yyyy-mm-dd format. For time processing, it supports yymmddhhmiss, hh:mi:ss, and yyyymmdd yyyy.mm.ddmm / dd / yyyy yyyy / mm / dd hh:mi:ss formats. Simultaneously, it can intelligently determine Unix (seconds or milliseconds starting from January 1, 1970) and Excel date and time representations (days from 1900 to the present), and judge validity within the date range of 2000 to 2099.

[0054] In this invention, in step F, the first 5 rows of data after anonymization are transmitted to the large model interface. At the same time, the data columns of the standard table are informed to the large model, which is required to determine whether it is a bank statement and provide the column correspondence with the standard table, as well as the date and time format. The account is verified according to the account verification rules. If a card number exists and the account of the party also exists, the card number is written into the remarks field. The card number in the remarks field is used as a virtual column of the standard table.

[0055] In this invention, step G uses the REPLACE function of MySQL to replace commas, currency symbols, spaces (including full-width spaces, full-width commas, ordinary spaces, "", etc.) and other special characters in the generated amount, converting it into a digital form.

[0056] In this invention, in step H, the `load data infile` statement generated by the large model is executed to import data into the original table. If the import fails, Python is used to communicate with the large model to generate code, which is then executed to complete the data import. Using defined delimiters and newline characters, the data (including currency, bank name, original file name, case number, and data source) is imported into the original table. The `update` statement replaces the currency, bank name, original file name, case number, and default data source extracted from the folder and file name. Based on established correspondences and rules, a MySQL `select` statement is used to check for issues with the transaction amount. Specifically, the number of rows in `select transaction amount 1.0 from standard table where transaction amount 1.0>-100000000000000 and case number = this case number` is compared with the result of `select count(*) from standard table where case number = this case number`. Simultaneously, based on the account number and ID, the sum of the previous row's balance and the transaction amount is checked against the current row's balance. Inconsistencies are marked as gaps requiring manual auditing. The `load data infile` and `insert into` statements are then used to check if they match. The SELECT statement records data to the header table, including column mapping rules and column replace rules.

[0057] This invention constructs a standardized data collection process, encompassing data preprocessing, format conversion, column name matching, and high-speed import. Utilizing large-scale intelligent analysis technology, it effectively processes bank statement data in various formats, ensuring data accuracy and traceability. This method supports the parsing and conversion of multiple file formats, including Excel, CSV, and PDF, and leverages MySQL's LOAD DATA INFILE function for rapid data import. Combined with large-scale intelligent matching, it further enhances the efficiency and reliability of data collection, providing strong data support for disciplinary inspection and supervision work and meeting the needs of disciplinary inspection and supervision departments for efficient and accurate data processing when retrieving bank statements from evidence-gathering targets.

[0058] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for efficiently collecting bank statement data, characterized in that, Includes the following steps: A. Determine the standard table; B. Data preprocessing; C. Data reading and processing; D. Decisions regarding table headers and fields; E. Date and time processing; F. Large-scale model interaction and account processing; G. Amount and character processing; H. Data import and validation; Step D includes the following process: 1) Header judgment: By iteratively using a string statistics algorithm, all lines in which more than 90% of the text is Chinese or English characters are filtered out, and a header label is added; 2) Field matching: Case number and bank name are obtained from the directory name; original file name and account number are obtained from the file name; balance is obtained from the thesaurus; transaction date and time are obtained based on synonyms and data representation; counterparty account and counterparty name are obtained from the thesaurus and the first five rows of audit data that are not from our side. If no match can be found, the corresponding column is obtained by communicating with the large model, and the relevant information is recorded in the header table and the thesaurus; the summary is also obtained from the thesaurus. 3) Other field processing: The remarks field is organized and stored in JSON format as the other columns, using the header column: content; the data source is uniformly set to "letter sending method". If it is a customized interface for local bank statements, it is "corresponding bank name plus bank interface method". The interface call is used to retrieve statement records by name, ID number, bank account number, and company name; the currency is RMB by default and is obtained from the file name. If it is not specified in the file name, the default value is used; foreign currency account file names must add "- currency" at the end. In step F, the first 5 rows of data after anonymization are sent to the large model interface. At the same time, the data columns of the standard table are informed to the large model, which is asked to determine whether it is a bank statement and provide the column correspondence with the standard table, as well as the date and time format. The account is verified according to the account verification rules. If a card number exists and the account of the party also exists, the card number is written into the remarks field. The card number in the remarks field is used as a virtual column of the standard table.

2. The method for efficient data collection of bank statements according to claim 1, characterized in that, Step A specifies the standard table structure used for data collection, with fields including id, case number, local account number, local account name, counterparty account number, counterparty account name, transaction amount, transaction date, transaction time, summary, remarks, gap flag, currency, bank name, original file name of the collection, and data source. The original file name of the collection and the data source are used to trace the data source and ensure that the information is not erroneously processed during the audit data processing. The data types of each field are set as follows: the original file name of the collection is varchar, the remarks are text, and the remaining fields are varchar.

3. The method for efficient data collection of bank statements according to claim 1, characterized in that, The data preprocessing in step B includes the following methods: a. Create folders and name files: Create corresponding folders based on case number, bank name, and account holder's name. Name the bank statement records retrieved from the financial institution according to the standard rule of "account holder's name - account holder's name - currency" and put them into the corresponding folders. b. Editing Excel files: For Excel files, first move the header to the first row; if an Excel file contains data from multiple accounts, merge these data into one table; delete the rows at the bottom that do not belong to the statement content, and then save the processed file as "Modify - original Excel file name"; c. Format Conversion: Use pandas and openpyxl libraries to convert Excel files to CSV format. The specific steps are as follows: recursively retrieve Excel files in the case directory, use pandas to read the Excel files and obtain the names of all worksheets, then iterate through each worksheet and save it as a CSV file. The CSV file name is constructed according to the rule "_excel file_sheet name.csv". Finally, save the data frame as a CSV file. For PDF files, use the Tabula library to extract the table data and convert it to CSV format.

4. The method for efficient data collection of bank statements according to claim 1, characterized in that, In step C, Python is used to read the first row of the file (header) and the second to fifth rows of data. If there are fewer than five rows of data, all data is read, and the column count is recorded. Each column in the second to fifth rows of data is compared with the local account number and account name. If the names match, the local account number and account name are confirmed. The local large model is then called. First, the header table is queried. If a template exists, the MySQL `load data infile` import statement is generated based on the `load` and `insert` statements defined in the template and imported into the original table. Then, the `insert into select` statement is used to import the data into the standard table. During data processing, the time format is standardized, requiring the transaction date to be in YYYY-MM-DD format and the transaction time to be in hh24:mi:ss format. For the transaction amount, "+" is used when the local account is receiving income, and "-" is used when it is spending.

5. The method for efficient data collection of bank statements according to claim 1, characterized in that, In step E, MySQL's custom functions are used to match and process various date formats, including yyyymmdd, yyyy.mm.dd, mm / dd / yyyy, yyyy / mm / dd, and the above formats with two digits of yy. All of these are returned as yyyy-mm-dd format. In terms of time processing, yymmddhhmiss, hh:mi:ss, and yyyymmdd yyyy.mm.dd mm / dd / yyyy yyyy / mm / dd hh:mi:ss formats are supported.

6. The method for efficient data collection of bank statements according to claim 1, characterized in that, In step G, the REPLACE function in MySQL is used to replace commas, currency symbols, spaces, and other special characters in the data, converting it into a digital form.

7. The method for efficient data collection of bank statements according to claim 1, characterized in that, In step H, the `load data infile` statement generated by the large model is executed to import data into the original table. If the import fails, Python is used to communicate with the large model to generate code, which is then executed to complete the data import. Using defined delimiters and newline characters, the data is imported into the original table, and the currency, bank name, original file name, case number, and data source are added. The `update` statement replaces the currency, bank name, original file name, case number, and default data source extracted from the folder and filename. Based on established correspondences and rules, a MySQL `select` statement is used to check for issues with the transaction amount. Specifically, the number of rows in `select transaction amount 1.0 from standard table where transaction amount 1.0>-10000000000000 and case number=this case number` is compared with the result of `select count (*) from standard table where case number=this case number`. Simultaneously, based on the user's account and ID... Sort the data, check if the sum of the previous row's balance and the transaction amount matches the current row's balance, and mark any discrepancies as gaps that require manual auditing. Record the "load data infile" and "insert into select" statements in the table header, including column correspondence rules and column replace rules.

Images