Multiple Currency format

Question

Hi

I have to consolidate weekly many bank and credit card statements, for 2 countries (US and BR), meaning 2 different region where date and number format doesn't match. Also, each statement is different (columns)

Its taking to much time to manualy clean up and with the same structure (columns and data format US):

• Changing the settings for region on the computer back and forth
• Downloading the CSV files, with different layouts, columns...
• Manually adjusting each file to be on the same format, the same date and number setting...

Any suggestion, maybe using the power query I would have a much easier and automatic way, no?

If someone can give me the best route/steps to follow I appreciate it.

I'm using Mac, but I also have a virtual PC, I'm using for excel features not available for Mac

thanks for your help

Answer 1

** I start downloading from each web site the statment. Mostly comes as a .CSV or .XLS. Than, open it with excel and manually work to have a clean set of data for each file

I need the CSV files, one for each region. If possible do not use XLS format, that can lead to problems.

(I've see so much wrong XLS files, and the issue is due to the export application, not Excel!)

If you like to anonymize the data use notepad to open the file, not Excel!

To 1.

No, the data is wrong, you import a text, not values!

I can see that row 7:21 should come from the CSV file, that is really no problem.

The total row is also simple, format as table and enable it.

How do you calculate the values in C25:C31 and E35:E37?

Andreas.

Answer 2

HI Andreas, 

thanks for your help. I don't know much about VBA yet (next course), so If you have a change to give me more inputs on the power query I appreciate it.

I just upload a sample file, with 5 diff statments samples. The project is:

** I start downloading from each web site the statment. Mostly comes as a .CSV or .XLS. Than, open it with excel and manually work to have a clean set of data for each file

1. Have the Columns: DATE and Amounts working properly (you can see on the R$, its painted on yellow with some comments)
2. Create a connection, on the data consolidate files, with the right columns. Please note some has Debit, Credit and Balance; others it is just 1 amount column with (-) or not, and the balance. I don't care about the balances...

here is the link: https://1drv.ms/x/s!AmZPY\_vKW1kHgytAhWsq4N8Z4u6k?e=7ADOiF

thanks,

lucia

Answer 3

VBA is always a way... but Power Query too.

The different regions is not really a problem, you can change the Region settings for each Query in the file.

After the import use "headers as first row" and transpose the data. Now you can replace the values in the first column with your desired names.

Transpose again, use "first row as headers", done.

If you need further help please upload sample files and post the download link here.

Andreas.