Automation of Tax and Accounting Auditing: Quality Verification Tool for the Siconfi Ranking

Paulo Henrique Mariano Chaves; Gian Carlo Decarli

DOI: 10.22167/2675-6528-202603363

Article derived from a Course Conclusion Work (TCC), with content based on the student’s original work and adapted to the editorial format of the E&S Magazine with the support of the ResumeAI tool, an artificial intelligence solution developed by Instituto Pecege for textual synthesis and organization.

Summary

The transparency and integrity of accounting information are fundamental requirements for the fiscal compliance of public entities, evaluated by the National Treasury Secretariat (STN) through the Ranking of the Brazilian Public Sector Accounting and Fiscal Information System (SICONFI). The study aimed to develop a proactive audit tool for a federation entity, seeking to mitigate fiscal inconsistencies in Dimension 1 (Information Management) before the official result. The methodology was based on Data Science, using the Python language for the automated extraction of the Accounting Balances Matrix (MSC) via the Transparent Treasury Application Programming Interface (API), and the Power BI software for building a compliance dashboard. The project focused on data integration to ensure the symmetry of deadlines and deliveries between accounting records and fiscal statements. The results demonstrated that the automation of the data flow allowed for the early identification of discrepancies, promoting information reliability and the efficiency of governmental internal control, with a structured architecture for expansion to other dimensions of the ranking. The application of the tool to data from the 2025 fiscal year demonstrated its full functionality, with 100% compliance in the 38 control checks of Dimension 1. It was concluded that the solution acted as an innovative and scalable model for the modernization of public management, delivering a tool capable of mitigating fiscal inconsistencies prior to the consolidation of the SICONFI Ranking.

Keywords: data science; governmental compliance; internal control; public management; business intelligence.

1. Introduction

The management of public finances in Brazil is governed by principles of transparency and responsibility, based on the Fiscal Responsibility Law [LRF], established by Complementary Law No. 101/2000 (Brazil, 2000). This legislation imposes on the federative entities the obligation to publish instruments that allow social control and monitoring by oversight bodies. In this scenario, transparency in fiscal management transcends mere legal compliance, becoming a strategic asset for public entities.

The ability of an organization to stand out in contemporary public administration depends directly on the analytical use of data for decision-making, as pointed out by Davenport and Harris (2017). In the Brazilian context, the Ranking of the Accounting and Fiscal Information System of the Brazilian Public Sector [SICONFI] acts as a thermometer of excellence, evaluating the consistency and quality of information sent to the National Treasury. The score in this ranking reflects the adherence of the federative entities to current accounting and fiscal standards.

The generation of the Budget Execution Summary Report [RREO] and the Fiscal Management Report [RGF] constitutes primary legal requirements for monitoring budget and financial execution. The standardization of these documents is governed by the Manual of Fiscal Statements [MDF], published by the National Treasury Secretariat [STN] (STN, 2024), which establishes strict integrity rules, accounting concepts, and layouts to ensure the comparability and reliability of public accounts. However, the preparation of these reports presents significant operational challenges, given the complexity in cross-referencing budget, financial, and asset data, which demands a high level of precision and minimal manual intervention.

Historically, monitoring the dozens of checks required by the National Treasury demanded a predominantly manual effort. This process involved cross-referencing reports in extensive spreadsheets, files in Comma-Separated Values [CSV] format, and extracts in Portable Document Format [PDF]. Such an analog model presented a high susceptibility to human errors and consumed significant time from technical teams. Goularte (2022) highlights that the evolution of the information quality ranking requires federative entities not only to transmit data but also to perform rigorous and continuous verification of accounting integrity, evidencing a gap in proactive auditing capacity.

To overcome this highly complex scenario and optimize governmental internal control processes, this study proposes an innovative solution. The methodology is based on Data Science, integrating the Python language for automated information extraction, such as the Accounting Balances Matrix [MSC] via the Transparent Treasury Application Programming Interface [API], and the Power BI software for building a compliance dashboard. The use of storytelling and data visualization techniques, as advocated by Knaflic (2015), becomes essential for managers to identify distortions clearly and actionably, transforming complex data into visual insights.

Given the problem of high complexity and inefficiency of manual fiscal and accounting audit processes, which result in inconsistencies and risks of sanctions for federative entities, the development of a tool that optimizes internal control and promotes information reliability is justified. Thus, the study aimed to develop a proactive audit tool for a federation entity, aiming to mitigate fiscal inconsistencies in Dimension 1 (Information Management) of the SICONFI Ranking before the official results.

2. Material and Methods

This research was characterized as a study of an applied nature, with a descriptive approach, focused on the development of a technological solution. The objective was to improve the quality of fiscal information and optimize governmental internal control processes. The methodology was based on Data Science, integrating the Python language for automated information extraction and the Power BI software for the construction of a compliance dashboard.

The methodological procedure was structured in a continuous flow, starting with the collection of two distinct primary sources of information. The first strand focused on external extraction, capturing the already approved reports from the Summary Report of Budget Execution [RREO], the Fiscal Management Report [RGF], and the Accounting Balances Matrix [MSC]. This stage was automated using the Python language, consuming the Transparent Treasury’s Application Programming Interface [API].

Simultaneously, the second aspect encompassed the internal database, capturing information from the State Accounting System [SCG]. The extraction of data from the MSC was segmented by branch of government (Executive, Legislative, Judiciary, Public Prosecutor’s Office, and Public Defender’s Office) and by classes, aiming to optimize the process and ensure data integrity for later verification.

The aggregated Accounting Balances Matrix [MSC], an accessory obligation, was extracted for each month of the fiscal year, and the Closing Matrix [MSCE] was collected for the closing of the fiscal year. The aggregated MSC is subdivided into three matrices: the Patrimonial Accounting Balances Matrix [MSCP], the Budgetary Accounting Balances Matrix [MSCORC], and the Control Accounting Balances Matrix [MSCC].

After extraction, data treatment and normalization were carried out, using the Power Query tool and the Pandas library. This phase was essential for standardizing information, handling null values, and reconciling primary keys, allowing the connection between the state system accounts and the requirements of the National Treasury Secretariat [STN], as recommended by Davenport and Harris (2017).

In this stage, data type conversion was performed to ensure that accounting balances were read as precise numerical values and the standardization of account nomenclatures from the Public Sector Accounting Plan [PCASP]. The complete structure of the current PCASP was imported into the data model, in a dimension table named PCASP_extended, containing the account coding and the nature of the balance.

Next, the database architecture was structured in the Power BI environment, following the Star Schema model. The model was designed with fact tables, containing the volumes of delivery matrices and extracts, and dimension tables, such as calendar, chart of accounts, and power and authority report. This modeling ensured the performance of analytical queries and the integrity of the relationships necessary for the application of auditing rules.

The final phase of development consisted of the Business Intelligence stage, with the application of the Data Analysis Expressions [DAX] language. Advanced metrics were created for the purpose of crossing the balance sheet matrix and delivery statements. The use of storytelling and data visualization techniques, as recommended by Knaflic (2015), was essential for the construction of the dashboard.

The final product of this architecture was a validation dashboard, an interactive interface designed for analysis and consultation. This dashboard was developed to highlight discrepancies, monitor compliance with the fiscal responsibility law’s deadlines, and generate automated alerts, enabling proactive correction of inconsistencies before the legal approval deadlines close. The practical scope of validation was strictly limited to Dimension 1 (Information Management) of the SICONFI Ranking, for a federation entity (ID 52), using data from the 2025 fiscal year.

3. Results and Discussion

The results achieved in this study demonstrated the technical feasibility of the proposed solution, showing that the integration between the Python language and Power BI is capable of anticipating inconsistencies that would previously only be detected after official publication in SICONFI. The developed approach represents a significant advance in governmental internal control, transforming a reactive process into a continuous and proactive monitoring system. Below, the executed steps and initial findings are detailed, focusing on Dimension 1 (Information Management).

As a practical result of automation focused on Dimension 1, a Python script was developed exclusively for capturing the official delivery extract. This script was parameterized to search the history of the 2025 fiscal year for the federative entity (ID 52), allowing the collection of data from transmissions made throughout the year. The successful extraction of this data pipeline allowed the identification of 119 delivery records for the 2025 fiscal year, covering shipments from the Aggregated Accounting Balances Matrix (Aggregated MSC), Closing Accounting Balances Matrix (MSCE), Annual Accounts Declaration (DCA), Summary Report of Budget Execution (RREO), and Fiscal Management Report (RGF).

These shipments originated from various Powers and Bodies (PO), including the Legislative Assembly, Public Defender’s Office of the State, State Government (Executive), State Public Prosecutor’s Office, State Court of Accounts, Municipal Courts of Accounts of the State, and State Court of Justice. Preliminary analysis of the captured data revealed the exact date and time of transmission, recorded in the `data_status` column. This information enabled the automated calculation and traceability of compliance with legal delivery deadlines, such as the 30th of the following month for the monthly MSC, the 30th of the following month for the bimonthly RREO, the 30th of the following month for the quarterly RGF, and April 30th of the following year for the DCA.

After extraction, the delivery statement data was integrated into the Power BI environment, where Power Query was used for treatment and normalization steps. This phase was crucial for standardizing information, handling null values, and ensuring primary key compatibility, enabling the connection between state system accounts and the requirements of the National Treasury Secretariat (STN). The database architecture was structured in Power BI following the Star Schema model, with fact tables containing delivery matrices and statements volumes, and dimension tables such as calendar, chart of accounts, and power and agency report. This modeling ensures the performance of analytical queries and the integrity of the relationships necessary for applying audit rules.

Within the scope of analytical preparation in Power Query, the adopted strategy segmented the extract of deliveries into two distinct tables: one dedicated exclusively to shipments from the Accounting Balances Matrix and another to fiscal reports (RREO, RGF, and DCA). This modeling approach aims to facilitate the verification routines of Dimension 1, allowing the system to process timeliness rules independently for each type of obligation. The segmented table for fiscal reports allows monitoring compliance by quarter or bimester for various Powers and Bodies, ensuring that the verification dashboard accurately points out any transmission failures in specific units of the entity. This refinement in data structure is essential for the democratization of information, transforming a massive volume of metadata into granular and actionable indicators for the manager (Knaflic, 2015).

Next, an API extraction pipeline was built, capable of handling the volume of the Accounting Balances Matrix. Unlike generic extractions, the script was parameterized to perform segmented collection by Power and Body, which avoided timeout failures common in large-scale requests. The use of modular functions allowed for efficient memory management and ensured process resilience through automatic retry logic. The implementation of the extraction and resilience logic in the Python script used the Requests library and the offset parameterization for pagination of data from the Tesouro Transparente API.

The structure of the aggregated MSC is subdivided into three distinct matrices, made available by the SICONFI API for extraction. The Matriz de Saldos Contábeis Patrimonial (MSCP) [Balance Sheet Matrix] includes information on the entity’s assets, liabilities, and net worth, covering Classes 1 (Assets), 2 (Liabilities), 3 (Diminutive Patrimonial Variations – VPD), and 4 (Augmentative Patrimonial Variations – VPA). The Matriz de Saldos Contábeis Orçamentária (MSCORC) [Budgetary Accounting Balances Matrix] records budget execution, detailing revenue forecast and realization, as well as expenditure fixation and execution, focusing on Classes 5 and 6 (Budget Planning and Execution). Finally, the Matriz de Saldos Contábeis de Controle (MSCC) [Control Accounting Balances Matrix] is intended for recording potential acts and information that do not immediately affect equity, in addition to specific transparency controls, comprising Classes 7 (Debtor Controls) and 8 (Creditor Controls). The Matriz de Encerramento (MSCE) [Closing Matrix], known as the 13th MSC, consolidates all balance sheet, budgetary, and control accounts at the end of the fiscal year.

After data consumption, Data Wrangling was applied, where the data were submitted to a cleaning and normalization process using the Pandas library. In this phase, preliminary results indicated the need for critical adjustments, such as converting data types to ensure that accounting balances were read as precise numerical values and standardizing the account names of the Public Sector Accounting Plan (PCASP). This treatment ensured that the generated CSV files were ready for modeling in Power BI, without the need for manual interventions in Excel spreadsheets, which represents a significant gain in productivity and information security. The normalization and typing of fiscal data, the structuring of balance columns, and the cleaning of duplicate records ensured the integrity of the base before the analytical load.

To enable dimension checks, which assess the consistency of accounting records, the complete structure of the current PCASP was imported into the data model, in the dimension table named `PCASP_extended`. This table contains account coding, balance nature (Asset, Liability, Variations), and functional descriptions. This integration is fundamental to ensure the standardization of accounting practices, allowing the system to automatically verify if the nature of the debit or credit balance in the entity’s MSC complies with the rules established by the STN. The use of this normative framework allowed for the immediate identification of inconsistencies in the classification of accounting accounts. By relating the MSC captured via API with the PCASP table, the data model can detect, for example, if Class 1 asset accounts have presented undue credit balances or if sub-accounts have been used in disagreement with federal standardization. This automated approach materializes the concept of data democratization and clarity (Knaflic, 2015), transforming the technical complexity of the chart of accounts into a transparent and traceable audit trail, ensuring the reliability of the accounting information that makes up the SICONFI Ranking.

To operate in a granular way, the extraction was divided into requests by power (Executive, Legislative, Judiciary, Public Prosecutor’s Office, and Public Defender’s Office) and by classes, which, together with the Chart of Accounts Applied to the Public Sector (PCASP) in CSV format (extracted from the National Treasury), were combined. This strategy of fragmenting queries to the Transparent Treasury API proved essential for combining the data, ensuring the integrity of the Closing Matrix (MSCE) for later verification. Thus, the study achieved its objective of verifying and evaluating the quality of information and accounting practices in the four dimensions of criticism of the SICONFI Ranking.

The first dimension analyzed, D1 (Information Management), assesses the entity’s behavior in submitting the MSC, RREO, RGF, and DCA declarations, with an emphasis on timeliness and compliance with legal deadlines. The developed tool achieved this verification effectively. Dimension D2 (Accounting) verifies the consistency of data within the MSC, analyzing whether the records respect the nature of asset, liability, and equity variation accounts, and if the balances follow the logic of the PCASP. Dimension D3 (Fiscal) cross-references information from the MSC with fiscal reports from the RREO and RGF, verifying if the values declared in the fiscal documents match the accounting data submitted electronically. Finally, dimension D4 (Accounting and Fiscal) performs the cross-referencing of accounting and fiscal information, evaluating the consistency between the Annual Accounting Declaration (DCA) and the RREO and RGF fiscal reports, and the relationship between net income and equity with the budgetary execution of the fiscal year.

The data pipeline consolidated the 38 checks required in Dimension 1 into three major automated governance control pillars, creating a continuous fine mesh for the federative entity. The first audit pillar focuses on Timeliness and Completeness. The algorithm logic monitors the decentralization of obligations, ensuring that critical reports (RREO, RGF, and DCA) from different spheres, such as the Executive, Legislative, and Judiciary, are delivered within the deadlines of the Fiscal Responsibility Law. The system crosses the processing date in the API with the regulatory milestones, nullifying the risk of score loss due to systemic delays or bimonthly omissions.

The second pillar ensures Accounting Integrity and Fraud Prevention. The tool’s great analytical differential lies in the mathematical validation of the trial balance. The system was programmed to apply the double-entry bookkeeping principle and attest to the fundamental accounting equation in an automated way, tracking undue negative absolute values, inversions of account nature, credit balances where they should be debit, and temporal continuity inconsistencies between months. As a technical innovation, the model implemented a digital signature heuristic capable of detecting artificial submission manipulations, such as copying the previous month’s trial balance in its entirety, acting as a rigorous anti-fraud filter.

Finally, the third pillar acts on the quality and transparency of public spending. The absence of Complementary Information makes it impossible to track government money. Thus, the tool scans the budgetary matrices in search of gaps in essential metadata, requiring the exact correlation between the Nature of Expenditure, the Functional Classification, and the Source of Funds. This ensures not only technical compliance but social transparency about how and where public resources are being applied.

All this computational intelligence was structured through dynamic measures in DAX language, designed to consolidate thousands of lines of accounting data into an accessible visual interface. The main technological gain was overcoming the limitations of spreadsheet tools, delivering to senior management an interactive dashboard capable of processing compliance rules and generating real-time diagnostics. The Dimension 1 dashboard was developed from a Business Intelligence perspective, translating the technical and mathematical rigor of accounting audit into easily assimilable visual insights for the senior management of the entity’s State Secretariat of Economy.

The dashboard architecture was divided into strategic navigation zones. On the left side panel, data segmentation filters (slicers) were implemented, which provide interactivity to the analysis, allowing the manager to isolate results by dimension types and by year. This granularity meets the need for continuous auditing, breaking away from the traditional model of static and annual analyses. The results obtained, applied to the 2025 exercise database, demonstrated the tool’s full functionality. The upper section of the dashboard displays synthetic Key Performance Indicators (KPIs), showing that all 38 control checks developed for Dimension 1 were met, with 100% compliance. The graphical indicator of delivery efficiency reflects the timeliness and completeness in the submission of RREO, RGF, and MSC reports by all State powers.

The matrix structure arranged in the central area of the dashboard acted as a traceability map. In it, each indicator of the checks from D1_00001 to D1_00038 assumed a binary value of 1 (compliant) or 0 (inconsistent). In a real scenario of governmental operation, the mere appearance of an indicator with a value of 0 or flagged in red would act as a red flag audit trigger, allowing accounting technicians to isolate the failure in the matrix and proceed with the correction before the final submission to the National Treasury. Thus, the dashboard achieved the main objective of the study by identifying inconsistencies before the SICONFI Ranking evaluation cutoff date, replacing the traditional reactive finding of errors with a continuous and proactive verification process.

The dynamism of the SICONFI Ranking demands a high degree of adaptability from federative entities, as the National Treasury Secretariat periodically revises the evaluation criteria. The validation structure evolved significantly during the period of this study. Until mid-April 2026, the ranking had 186 total checks, with 38 of them for Dimension 1. Subsequently, a public consultation introduced 37 new provisional rules, of which 6 were for Dimension 1. The official publication on June 28, 2026, after a balance of criterion inclusions and exclusions, set the evaluation at 210 current checks, with 44 allocated to Dimension 1.

For Dimension 1 (Information Management), the practical focus of this research, this normative update required the absorption of new rules, such as additional validations on outstanding commitments and the destination of linked resources. In the traditional analog model, adapting to a new evaluation scope would require the manual reformulation of dozens of spreadsheets, demanding weeks of effort from the technical team to attest to retrospective compliance. However, the modular architecture developed in Python and Power BI demonstrated its scalability in practice. The new requirements, as soon as they were made available for public consultation, were parameterized in the DAX engine in a matter of hours.

As the database extraction was already automated and integral, the system processed the new rules almost instantaneously. The dashboard generated a real-time diagnosis, attesting to 100% compliance of entity 52 with the new requirements of Dimension 1, eliminating the need to allocate additional servers for verification or to make adjustments to already delivered reports. This episode validated the tool not only as an instrument of passive auditing but as an asset for agile regulatory adaptation, promoting data-driven governance and proactive transparency.

The quantification of results evidences the direct impact of automation on the efficiency of internal control and the optimization of the entity’s human capital. In the previous analog model, the verification process of Dimension 1 generated specific operational bottlenecks. Some verifications demanded the effort of two servers for an entire day each month solely for partial review. Additionally, at the closing of the accounting period, a task force of four servers was necessary, dedicated for approximately five business days exclusively to review all 38 known Dimension 1 verifications and ensure data accuracy. It is estimated that this reactive model consumed a total of 352 man-hours annually.

With the implementation of the validated data pipeline in this study, operational dynamics were transformed into continuous monitoring. After the technical effort of developing the solution, the validation routine became strictly monthly. Processing now requires the allocation of only one server to perform the extraction of the database and the analysis of the analytical dashboard, consuming approximately 96 man-hours annually. This new technological flow represents an estimated reduction of 72.7% in the operational effort dedicated to Dimension 1. The most significant gain for the agency, however, lies in the complete elimination of the need for an end-of-year task force for these checks, as the analyses have become timely. Inconsistencies are now mitigated in advance, month by month, allowing the annual consolidation to occur smoothly and without operational rework.

The study’s findings demonstrate that the proactive audit tool, based on Data Science, is effective in mitigating fiscal inconsistencies in Dimension 1 of the SICONFI Ranking. The automation of data extraction, processing, and visualization allowed for the early identification of discrepancies, ensuring 100% compliance in the initial 38 checks and rapidly adapting to the 6 new rules introduced. This approach resulted in a 72.7% reduction in annual operational effort, eliminating the need for reactive task forces and promoting more efficient, transparent public management aligned with STN requirements.

4. Conclusion

The study aimed to develop a proactive audit tool for a federative entity, seeking to mitigate fiscal inconsistencies in Dimension 1 (Information Management) of the SICONFI Ranking before the official result. It was found that the integration of Data Science technologies, such as the Python language for automated extraction of the Accounting Balances Matrix (MSC) via the Transparent Treasury Application Programming Interface (API) and the Power BI software for modeling and visualization, allowed for the early identification of discrepancies. The results demonstrated the entity’s full compliance with the 38 initial checks of Dimension 1 for the fiscal year 2025, as well as rapid adaptation to six new regulatory rules. The tool consolidated three governance pillars: timeliness and completeness, accounting integrity and fraud prevention, and quality and transparency of public spending, transforming reactive auditing into continuous monitoring. This approach resulted in an estimated 72.7% reduction in annual operational effort dedicated to Dimension 1, eliminating the need for reactive task forces and optimizing human capital. The main contribution lies in the delivery of an innovative and scalable model that modernizes public management, promoting information reliability and the efficiency of governmental internal control.

Although the study focused on Dimension 1, the modular architecture of the solution was designed to allow its expansion. The technical complexity and the vast volume of rules from the National Treasury suggest replicating this logic for the other Dimensions of the SICONFI Ranking (2, 3, and 4) as a perspective for future work. Such an expansion would enable the adaptation of validation parameters to the diverse periodicities required by the Fiscal Responsibility Law, consolidating an ecosystem of proactive auditing capable of monitoring the entity’s compliance systemically. The tool, therefore, acts as a vector of technological modernization, capable of mitigating fiscal inconsistencies prior to the consolidation of the SICONFI Ranking, ensuring data-driven and proactive transparency governance.

Bibliographic References

Brasil. 2000. Lei Complementar nº 101, de 4 de maio de 2000. Estabelece normas de finanças públicas voltadas para a responsabilidade na gestão fiscal e dá outras providências. Disponível em: <http://www.planalto.gov.br/ccivil_03/leis/lcp/lcp101.htm>. Acesso em: 10 fev. 2026.

Davenport, T.H.; Harris, J.G. 2017. Competing on analytics: the new science of winning. 2ed. Harvard Business Review Press, Boston, MA, EUA.

Goularte, J.L.L. 2022. Análise da evolução do ranking do indicador da qualidade da informação contábil e fiscal da Secretaria do Tesouro Nacional. Contabilometria – Brazilian Journal of Quantitative Methods Applied to Accounting 9(2): 118-135.

Knaflic, C.N. 2015. Storytelling com dados: um guia sobre visualização de dados para profissionais de negócios. Alta Books, Rio de Janeiro, RJ, Brasil.

National Treasury Secretariat [STN]. 2024. Fiscal Statements Manual [MDF]: applied to the Union and States, Federal District and Municipalities. 14th ed. National Treasury, Brasília, DF, Brazil.

Article originating from the Final Course Project of the Specialization in Data Science and Analytics of the MBA USP/Esalq

To learn more about the course, click here and access the MBX Academy platform