Questions? We have answers.

Data Formatting Guide - Tudor

Article author
Jonathon Plowman-Samson
  • Updated

This guide is broken into two parts:

  1. Formatting an Opening Balance to Import into MindBridge
  2. Formatting a General Ledger to Import into MindBridge

A raw Excel export from Tudor will appear look like this:

image_-_2021-01-07T132554.826.png

Part 1: Formatting an Opening Balance to Import into MindBridge

Begin by filtering the “opening balance” in the column containing the opening balance (column F in this example).

image_-_2021-01-07T132657.909.png

Copy and paste the filtered opening balances into a new workbook and save it as a new file for import.

image_-_2021-01-07T132736.046.png

Consolidate debit and credit values into a single column called “Balance” by using the formula below. Debits will be positive, credits will be negative. 

=IF(ISNUMBER(I2),I2,K2*-1)

Note: This formula searches for an available debit value. If a debit value is present, it will be used. If no debit value is found, then the credit value is multiplied by the negative one to create a negative value. 

image_-_2021-01-07T132803.297.png

 

Copy and paste the values for a new “Balance” column.

image_-_2021-01-07T132825.869.png

Remove all columns except "Account Number", "Account Description" and "Balance."

image_-_2021-01-07T132849.354.png

Your opening balance file is now ready for import!

 

Part 2: Formatting the General Ledger

Save a copy of the General Ledger (GL) file as a .csv.

image_-_2021-01-07T132912.973.png

Re-open the newly saved .csv file in Excel, which is now formatted as shown below:

image_-_2021-01-07T132932.810.png

Remove all rows above the “date” cell.

image_-_2021-01-07T133003.038.png

Create two new columns named "Account Number" and "Account Description."

image_-_2021-01-07T133048.964.png

Copy the account number by using the following formula:

=IF(IFERROR(SEARCH("account",C2),FALSE), MID(C2,10,4),A1)

Note: this formula is searches for the text “account” in column C.  
If “account” is found, then the formula will take the four digit account number by counting 10 characters into the cell, then taking the next four digits.  
If “account” is not found, the formula copies the cell above in the B column. This process allows for the formula to be copied down the entire GL.

image_-_2021-01-07T133122.127.png

Copy the account description using the following formula:

=IF(IFERROR(SEARCH("account",C2),FALSE), MID(C2,24,100),B1)

Note: This formula uses the logic applied above, but instead captures the account description.  

image_-_2021-01-07T133140.794.png

Copy both formulas to the bottom of the worksheet, then copy and paste each column as values to remove the formulas.

image_-_2021-01-07T133157.590.png

Remove unnecessary rows of data by filtering the “Date” column.

Using the filter, deselect the date values:

image_-_2021-01-07T133223.871.png

Delete the filtered rows:

image_-_2021-01-07T133241.106.png

 

Remove blank columns and rename remaining the following columns:

image_-_2021-01-07T133258.554.png

Your final data file will look like this:

image_-_2021-01-07T133314.987.png

You are now ready to import your GL file into MindBridge!

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.