I’m attempting to import transactions from my unsupported Bank (CIBC).
CIBC’s specific dialect of csv has a few challenges to overcome:
The date format is DD/MM/YYYY
negative and positive transactions are in separate columns
So, I’m currently running through several manual steps to bash the data into the correct dialect for Tiller import like this:
Select all in date column
Format cells for date column to USA locale specific date format
Add ‘-1’ to a blank cell, copy it
Select all in negative transactions column
Goto Special, Constants(to select only cells with values in)
Paste Special (Multiply) (To turn them all negative)
New column with =C1&D1 to merge positive and negative columns together
It is time consuming and fiddly, and I haven’t figured out a way to turn it into a simple formula, but I’m sure it’s possible. In addition it’d be nice if this could all be done in GSheets instead, to remove a whole dependency link in the chain, but some researching I did seemed to show some of the UI functions I’m using in Excel like Goto Special (Constants) and Paste Special (Multiply) have no equivalents in GSheets (again, a formula would be better!).
Macros could be an option, sure. I could possibly write a Python script to do it, but I would ultimately like this to be something that my wife can use also.
I welcome any advice from the experienced folk here. The ultimate goal would be to paste raw bank CSV in, press 1 button, save it out again, CSV-Import (and then repeat for 10 other accounts)
Tangentially, this also reminded me of other i18n tooling I’ve worked on in the past, which has centered upon YYYYMMDD as the most widely accepted standard for dates, perhaps that is a way forward ultimately for storing the date values, and have GSheets/Excel format that into locale-friendly versions for readability.
With a little fussing you can build formula-driven derived values in addition to directly remapping data. (There are some examples in the prefab templates.) You should even be able to deal with that non-US date format using the derived data approach.
On merging the Debit and Credit columns, you can replace the above with the following formula:
As long as the empty columns are truly empty (to be interpreted as zero) the result is the value you are looking for. This works in Google Sheets and should work in Excel as well. Copy that result column then paste it back on itself as “values only” and you can then remove the old columns.