Macro para Información Exógena 2025 en Excel - Genera Formatos DIAN en Segundos

How to Use the Macro for Exógena Information Formats

Introduction to the Macro Tool

• This video tutorial explains how to use a macro that generates exogenous information formats quickly, optimizing time and reducing errors.

Accessing the Application

• Upon accessing the application, users are prompted to enter some informational data, which can be obtained directly from the informant's root.

• The initial page presents two main buttons: one for importing preliminary work information or updates, and another for downloading the user manual.

Main Features of the Application

• A tab named "Exógena 2025" contains essential commands for importing data, validating it, and generating necessary formats with just a few clicks.

Preparing the Trial Balance

• The trial balance must include specific columns: account code, account name, third-party NIT (tax identification number), third-party name, initial balance, debit movements, credit movements, and final balance. It is crucial that this balance is structurally sound without inconsistencies.

• There are two methods to transfer trial balance information into the application:

• Automatic Method: Click on "import balances," select your prepared trial balance file in a navigation window for quick loading.

• Manual Method: Copy and paste data directly into the corresponding sheet while maintaining column structure; ensure headers match exactly before proceeding. Errors here can hinder validation and format generation processes.

Formatting Requirements

• Account codes and NIT must be in numeric format; if they appear as text (indicated by a green alert in Excel), they cannot be processed correctly. To fix this:

• Select the range of cells showing an alert and convert them to numbers using Excel's option.

Configuring Format Settings

• Navigate to the "format configuration" sheet where you will add accounts with transactions during the period associated with third parties using a button labeled "adicionar cuentas." This helps identify accounts linked to relevant exogenous formats like format 107 used in this example.

Associating Accounts with Concepts

• To associate accounting accounts with concepts:

• Click on "conceptos formatos" in options; choose your format then search for concepts available for proper association of each accounting account based on its nature.

• In configuration sheets under category columns, select operation type according to reported concept; choose appropriate movement type (debit/credit) based on account nature—this ensures accurate reporting without inconsistencies later on.

Technical Recommendations

• Ensure correct assignment of concepts based on accounting nature per guidelines provided; incorrect classifications may lead to issues during future requirements or validations.

• An additional Excel file named “parámetros exógenas” serves as a technical guide detailing various formats alongside their respective concepts according to current regulations—useful when uncertainties arise regarding concept assignments.

Organizing Third Party Information

• Move onto “base de terceros” sheet where you organize information about third parties involved in transactions throughout the year by selecting “terceros únicos,” which automatically transfers basic info from registered balances into your application setup.

Organizing Exógena Reports: Key Steps and Tools

Importance of Accurate Personal Information

• The personal information required for exógena reports includes identification, names or business names, and other necessary data. Ensuring this information is complete and accurate is crucial to avoid errors in the final file.

• Certain details, such as addresses and country codes, cannot be directly obtained from the trial balance. These are considered confidential under data protection policies.

Utilizing Accounting Software for Data Management

• Accounting software typically allows the generation of a third-party database where missing information can be found. Excel functions can then be used to retrieve this data efficiently.

• After applying the correct formula in Excel, users can drag down to fill in additional rows, ensuring that the third-party database is comprehensive for generating exógena formats.

Validating Third-Party Information

• To ensure reliability and security of reported data, specialized tools can help maintain an updated and structured third-party database from the start of the process.

• A specific application available on my website allows mass queries directly with Dian to verify each third party's status before submitting information. This tool operates independently from the exógena tool discussed in this video.

Enhancing Control Over Reported Data

• Implementing verification tools significantly strengthens control over information by validating against official Dian databases, reducing inconsistencies and enhancing professional reporting security.

• After completing preliminary steps—importing trial balances, configuring accounts with their respective formats, and organizing third-party databases—users are ready to generate reports.

Generating Exógena Formats

• Users navigate back to the options ribbon to select desired exógena formats; the system processes previously configured information automatically for review.

• It’s essential to check deadlines using the expiration button based on NIT numbers to avoid penalties due to late submissions.

Additional Tools for Efficient Reporting

• The "Complementos" sheet contains various useful tools like macros for separating names, verification digit checks, PUC equivalences for exógenas, current official resolutions, and simulators for potential sanctions related to reports.

• Features within this tool are designed to optimize time management; one key function allows importing previously worked macro data (like third-party bases), facilitating continuity across reporting periods without unnecessary reprocessing.

Conclusion of Tutorial Insights

• Users can import only specific components (third-party base or format configurations), ensuring that existing work remains intact while leveraging past efforts effectively.

• This tutorial aims at optimizing time management and improving report quality; sharing these insights with colleagues is encouraged as it supports ongoing development in accounting practices.