Reporting tutorials: Use MS Query to extract and analyse accounts data
As part of an on-going campaign to help accountants and finance managers produce better reports from their transactional data, AccountingWEB consultant editor David Carter has produced a series of tutorial articles explaining how Microsoft Query can help you get extract and format data from an accounts system into an "analysis-ready file".
"The people who write accounts packages are mainly technicians who understand very little about reporting," he says. But once you have managed to locate the source data and get it into Excel in a clean condition, "You will be able to use the power of pivot tables to produce pretty well any report you want. These tutorials show you how."
Due to its popularity, the Stationery Exhibitions demo company in Sage Line 50 was used for the initial series on how to locate and extract the data using ODBC (open database connectivity). But the same principles can be applied to any other ODBC-compliant accounts system. Further tutorials are listed below for non-Sage users.
Tutorial 1: Using MS Query to get Sage data into Excel
Microsoft Query only works with packages that are ODBC-enabled. First you will need to ensure that Query is installed within Excel and that the Sage ODBC drivers are correctly set up. It can be a fiddly operation, but should take just a few minutes. If you do have problems, persevere: it’s only a one-off job, and when ODBC is finally set up, you will have access to your data for evermore.
Tutorial 2: Import Sage balances into Excel with MS Query
Sage offers numerous Trial Balance and P&L reports - but only allows you to print a TB or P&L for a single period. This tutorial uses Query to extract the nominal account balances for several months from Sage and paste them into Excel. The tutorial also covers how to format the results so they appear the way you want in your spreadsheet.
Tutorial 3: Set up your analysis-ready file
If you are serious about improving your reports, you really need to bring over transactions at the detailed level, so you can then use pivot tables to produce different summary reports. The Audit Trail report is a good place to start, because it contains most detail at the transaction level - but not all the fields you want. The tutorial tackles formatting issues, and attempts to retrieve the missing data.
Tutorial 4: Add the missing pieces of data
When you get data out of an accounts package into Excel, key items of data are usually missing. How do you include them? This tutorial explains how to use Excel's Vlookup function to pull in the extra data from a lookup table.
Tutorial 5: Add nominal categories
Following on from the previous tutorial, David Carter now introduces two more new fields into the Sage analysis-ready file – the nominal categories. Having brought them in, we can now produce a Profit and Loss report.
Tutorial 6: Add customer and supplier names
When you export data out of Sage, it includes account codes of customers and suppliers, but not their names. This may not matter for your own use, but any report for a manager has to include full names and descriptions. The final tutorial in this series, shows how to add customer and supplier names to the analysis-ready file.
Tutorial 7: Use MS Query to create an analysis-ready SALES file in Sage
If you use Sage for sales invoicing, this will give you the data you need to report on sales and margins by product and customer. This tutorial shows you how to use MS Query to pull in data from the invoice data tables.
Import External Data tutorials for other accounting applications
Import Data from Navision into Excel
Import data from Pegasus Opera into Excel
Management Reporting in QuickBooks
Import data from TAS Books into Excel
Import data from Iris Exchequer into Excel. Part 1
Import data from Iris Exchequer into Excel. Part 2
Import data from MYOB into Excel. Part 1
Import data from MYOB into Excel. Part 2
Import data intoExcel from Access Dimensions. Part 1
Access Dimensions and Excel, part 2 - Access Analytics
Import data from Sage Line 100 into Excel
Import data from Sage Line 200 into Excel
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:
Reporting Tools #4: Introduction to summary reporting
Towards an analysis-ready file for reporting #1
Towards an analysis-ready file for reporting #2
Management Reporting in Sage Line 50 and Instant
Want to learn more about pivot tables? Start here
Improve your reporting skills with self-teach tutorials
An introduction to Excel-driven reporting tools
Industry insightsView more
Subscribe to the ExcelZone newswire
To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.