Do you want to: Ask a how-to question
OS version: Windows 11 Home
App version: ONLYOFFICE Desktop Editors, Community version 9.2.1.43 (x64 exe)
Downloaded from: ONLYOFFICE website
Additional information:
I’m working on creating a template for bookkeeping, and I’m stuck on a really annoying issue: getting the bank transactions into the spreadsheet, and in a way that will work for every [Canadian] bank.
To that point, I’m wondering if there is a guide, macro, or plugin to help one import quicken and/or sage50 files into spreadsheets
I know that I could use .csv files, and in some ways that would be easier and in other ways it would be way more complicated.
When I open a quicken or sage50 file in a text editor, the two file types are almost identical, and they follow a standardized format. The order of columns is the same, the date format is the same, everything is the same format regardless of what bank it comes from. The only difference is that quicken ends in .qfx and inside the file it says security:type1, and safe50 ends in .aso and inside the file it says security:none. Otherwise the contents of the two file types are identical.
Quicken/sage50 files have header information, and then footer information. The first transaction starts with the first “”, each column header or label format is in this type of bracket <>, and each transaction ends in “” But I’m not sure how to tell it “import just this information, in this way”.
Alternatively there’s the .csv files. I opened .csv files in a text editor from multiple different banks, and a problem became abundantly clear. While almost all the Canadian banks provide .csv as a format option for downloading transactions, they are not at all standardized. There are different column orders, although I could work with that and just copy/paste by column. What I don’t know how to handle, is the fact that they’re using different date formats! I’ve seen YYYY-MM-DD, MM/DD/YYYY, MM-DD-YYYY, and I bet if I got a few more samples from different banks, I’ll probably also see YYYY-DD-MM and DD-MM-YYYY formats. How can I make sure the dates are correct when they’re using different formatting? Which is why I thought to instead go with quicken/sage50 files, but that has the issue of lots of data and needing programming.
I would appreciate any thoughts/suggestions