Sage 50 and Power Query
Good day:
We are a small Business Intelligence consulting company based in Nova Scotia and have worked with many accounting packages including Sage 50.
The “easiest” way to get data from Sage 50 seems to be an export in CSV format. The older versions of Sage 50 will likely restrict the data download to a year at a time, but this really isn’t a constraint; prior years need only be done once, and the current year will need to be downloaded every time an update is required. The download is quick; saving and renaming the file is painless.
You will also need three additional files: a calendar that contains every date from the earliest Sage record, a list of all Account Codes appearing in the Sage records, and a list of all Project Codes in the Sage records (assuming that you are using project codes). Best practice is to keep these files in a spreadsheet (we usually call it the warehouse file). Missing Accounts and Projects create the dreaded “blank” row in the reports.
Place the Sage exports in a single folder, ensuring that they are the only files in this folder (While Power Query can sort this out, might as well conform to “best practices” from the start.).
The CSV downloads are ugly to look at, but very strict formatting rules are followed by Sage, so the files are quite easy to work with.
Use Power Query to combine and restructure the files for use in a Power Pivot or Power BI Desktop report, adding in the Calendar, the Accounts list, and the Projects list. Our latest client has also added a Budget table (spreadsheets used to calculate Departmental and Project budgets), and a Forecast table (with updates to the Budget on a quarterly basis).
Their reporting requirements are to track Actual expenses by Department and Project versus Budget and Forecast, and track these against prior year actual values.
If you are interested in a sample Power PBI Desktop file (uses fake data, based on a charity), feel free to contact me. We are a consulting company, but if you keep your questions simple enough, I would be happy to give a bit of my time for free.
Dan
