Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
[This article is prerelease documentation and is subject to change.]
Finance Agent data preparation automates the steps required to prepare your data before running the matching process in data reconciliation or when beginning the process of variance analysis. This automation decreases the number of unmatched transactions and improves the overall quality of your data. The preparation steps can include cell-level cleansing, cell-level formatting, row-level cleansing, and data augmentation.
Finance Agent data preparation doesn't require expertise in Excel or support from IT to prepare your data. You can express your intent in natural language, making the process accessible and user-friendly. You're always in control, and have the opportunity to review proposed changes before they take effect. These changes are nondestructive and are prepared in a new sheet to help ensure the integrity of your original data.
Types of data preparation
Finance Agent data preparation includes the following types of preparation.
- Cell formatting
- Cell cleansing
- Row-level cleansing
- Data augmentation
Cell formatting
Cell formatting ensures data consistency and accuracy, which are essential for effective reconciliation. Cell formatting includes:
- Converting monetary data from one currency to another using a specified conversion rate.
- Converting numerical data from one language format to another while maintaining their original numerical value.
- Parsing strings containing dates and converting them into a standardized format (YYYY-MM-DD).
- Converting all numerical data into their absolute values.
Cell cleansing
Cell cleansing ensures that all cells in a dataset contain valid data. Cell cleansing includes:
- Replacing null values with a specific value, such as the mean, median, or mode of the dataset.
- Using the previous or next value to fill in missing data points in time series data.
- Removing invalid characters or replacing them with valid ones.
Row-level cleansing
Row-level cleansing involves identifying and removing rows that don't meet predefined quality standards. Row-level cleansing includes:
- Removing entire rows containing empty cells in the selected columns.
- Filtering out rows containing invalid values.
- Filtering rows based on numerical values falling within a specific range.
- Filtering rows based on a date range, which can be defined as fixed, relative, or by matching date parts.
- Detecting and eliminating duplicate entries.
- Sorting rows based on one or more columns.
Data augmentation
Data augmentation involves creating new columns and deriving values in these new columns from the existing dataset. Data augmentation includes:
- Creating a new column and setting its value based on conditions derived from existing columns.
- Creating new columns by concatenating existing columns to form new, meaningful data.
Supported and unsupported scenarios in data preparation
Supported scenarios
- Removing entire rows containing empty cells in the selected columns.
- Filtering out rows containing invalid values.
- Filtering rows based on numerical values falling within a specific range.
- Filtering rows based on a date range.
- Creating new columns by concatenating existing columns.
- Creating columns for consistency and uniformity (for example, rounding values).
- Populating empty data columns by specific values or by values from other columns or fields.
- Removing duplicate rows or entries.
- Populating missing values based on other rows.
- Generating unique identifiers for each record.
- Converting currency using a fixed exchange rate.
- Filtering records based on specific conditions.
- Flagging rows in a new column with a condition.
- Using basic arithmetic calculations.
- Creating new columns from existing data.
- Standardizing the format of specific data fields.
- Sorting rows based on one or many columns.
Unsupported scenarios
- Tasks involving row-level aggregation.
- Creation of pivot tables.
- Visual formatting of cells (for example, cell colors, cell padding, fonts, etc.).
How Finance Agent data preparation works
The following sections explain how Finance Agent data preparation works.
Step 1: Convert data to a table
Ensure your data is in table format before proceeding with data preparation.
Step 2: Describe the data preparation in natural language
To access data preparation, follow these steps:
- Launch the Finance Agent add-in from the Excel add-in menu.
- If it is your first time using the add-in, select Get Started then select Financial Data Preparation.
- Enter your data preparation instructions in natural language into the data preparation plan text box and submit the request.
Step 3: Review the new sheet created by the add-in
To review the new sheet created by the add-in, follow these steps:
- Select Preview with sample data to view results in a new sheet with the suffix
_Sample. - If satisfied, select Apply rules to generate a new sheet with the suffix
_Prepared.
Step 4: Save a plan as a template
To save a plan as a template, follow these steps:
- Select Save as template or + from the lower right-hand corner of the Excel Add-in.
- Enter the name of the template and select Save.
Prompt Suggestions
To get started with data preparation, select the data preparation plan text box and a list of suggestions is presented to help you get started.
Example Scenarios
Example 1: Removing Rows with Empty Cells
Prompt: "Remove all rows with empty cells."
Before:
| ID | Transaction Date | Amount |
|---|---|---|
| 1 | 2023-01-01 | 100 |
| 2 | 2023-01-02 | 200 |
| 3 | 300 | |
| 4 | 2023-01-04 | 400 |
After:
| ID | Transaction Date | Amount |
|---|---|---|
| 1 | 2023-01-01 | 100 |
| 2 | 2023-01-02 | 200 |
| 4 | 2023-01-04 | 400 |
Example 2: Converting Dates and Replacing Null Values
Prompt: "Convert all dates to YYYY-MM-DD format and replace null values in the 'Amount' column with the mean of the column."
Before:
| ID | Transaction Date | Amount |
|---|---|---|
| 1 | 01/01/2023 | 100 |
| 2 | 02/01/2023 | |
| 3 | 03/01/2023 | 300 |
| 4 | 04/01/2023 |
After:
| ID | Transaction Date | Amount |
|---|---|---|
| 1 | 2023-01-01 | 100 |
| 2 | 2023-01-02 | 200 |
| 3 | 2023-01-03 | 300 |
| 4 | 2023-01-04 | 200 |
Example 3: Creating a New Column and Formatting Currency
Prompt: "Create a new column named 'Status' and set its value to 'Overdue' for all rows where the 'Transaction Date' is before January 1, 2024. Then, format all currency values in the 'Amount' column to '$1,234.56'."
Before:
| ID | Transaction Date | Amount |
|---|---|---|
| 1 | 2023-12-31 | 1000 |
| 2 | 2024-01-02 | -2000 |
After:
| ID | Transaction Date | Amount | Status |
|---|---|---|---|
| 1 | 2023-12-31 | $1,000.00 | Overdue |
| 2 | 2024-01-02 | -$2,000.00 |
Example 4: Removing Duplicates and Converting Case
Prompt: "Remove any duplicate rows based on the 'ID' column and convert all text in the 'Description' column to uppercase."
Before:
| ID | Description |
|---|---|
| 1 | transaction one |
| 2 | transaction two |
| 1 | transaction one |
| 3 | transaction three |
After:
| ID | Description |
|---|---|
| 1 | TRANSACTION ONE |
| 2 | TRANSACTION TWO |
| 3 | TRANSACTION THREE |
Example 5: Filling Missing Customer Names and Standardizing Customer Numbers
Prompt: "Fill in missing Customer names in all rows based on the Customer Number then remove the dash from Customer Number."
Before:
| Customer Number | Customer Name | Transaction Amount |
|---|---|---|
| C-001 | ABC Corp | 1000 |
| C-002 | XYZ Ltd | 2000 |
| C-001 | 1500 | |
| C-002 | 3000 | |
| C-003 | 123 Industries | 2500 |
| C-003 | 1800 |
After:
| Customer Number | Customer Name | Transaction Amount |
|---|---|---|
| C001 | ABC Corp | 1000 |
| C002 | XYZ Ltd | 2000 |
| C001 | ABC Corp | 1500 |
| C002 | XYZ Ltd | 3000 |
| C003 | 123 Industries | 2500 |
| C003 | 123 Industries | 1800 |