Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

ExcelZone Compendium: Working with accounts data (Excel 2007 update)

by
13th Jul 2009
Save content
Have you found this content useful? Use the button above to save it to your profile.

Practical issue - reduce the effort of accounting reports
Solutions: Getting at the data – general principles
Getting at the data – specific applications
Getting at the data – understanding databases
Working with the data
Third party tools
Presenting the data
Further reading

Practical issue - reducing the workload in producing accounting data reports
Excel Zone includes a substantial number of articles, and questions and answers on how to extract data from accounting systems and then use Excel to work with and present the information. Simon Hurst presents an overview of resources to help you with this activity.

Five top tips

1. Link to accounting data using Microsoft Query. David Carter's tutorials show how to extract and analyse accounts data from a variety of accounting systems.

2. Understand the basics of relational databases and how they store your data.

3. Use parameter queries to restrict the amount of data you need to drag across your network.

4. Is there a a pre-built solution? This could save you the trouble, and some of the risk, of getting at your accounts data.

5. Don't waste the effort of analysing your accounting data by failing to present it as effectively as possible. Think about how best to present financial data.

First of all we'll look at extracting the data and articles covering the general principles of importing data into Excel and more specific examples referring to particular accounting software packages. We'll then consider some of the main techniques for manipulating accounting data including, of course, the ubiquitous Pivot Tables before a final excursion into presenting the data – including the use of charts and 'dashboard' techniques.

Solutions: Getting at the data – general principles

The compendium article on Working with external data (Excel 2007 update) considered the different techniques of getting at the data including using Microsoft Query to create dynamic links to external data. A related topic that has cropped up a few times in 'Any Answers' is that of parameter queries or 'run-time filters' as David Carter referred to them in one of his 2min Tips.

The use of a parameter allows one or more prompts to be displayed when data based on a query is refreshed – this prompt would allow the user to enter criteria for one or more fields. The query would then only bring records from the database to Excel where the criteria were matched. It is possible to link the criteria to cells and also to automatically refresh the data when those cells are changed. So a user can type a value into a cell, and data that matches the criteria value will magically appear.

The first step in creating a parameter query is to use the 'View or edit query in Microsoft Query' option in the final stage of Excel's Query Wizard:

This will display the data that you have linked to within Microsoft Query, you can then use the Criteria, Add Criteria option to add a filter criteria to any of the fields in your data. Instead of entering an actual data value, you can type a text prompt within square brackets to create a parameter. Here we've entered [Enter country] as the criteria for the Country field:

When you 'Add' the criteria, your prompt will appear and, if you type in a country value, then only data for that country will be displayed. Choosing File, Return data to Microsoft Office Excel will display the following dialog, note that we have set the data to be imported to row 10, leaving room above for our criteria:

In Excel 2007 the Import Data dialog doesn't have a Parameters button. To find the option you need to click on the Properties button, then the Definition tab – the Parameters button is at the bottom of this screen.

The parameters screen lets you change the text prompt, enter a criteria value or link the criteria to a particular cell's contents and, optionally, choose to refresh the data whenever the value in that cell changes:
 

In Excel 2003 we can also access the parameter settings by right-clicking in the external data range and choosing the Parameters option. In Excel 2007 you need to choose the Connections option from the Data ribbon tab, select the appropriate connection and then the Properties button, Definition tab. The Parameters button is once again at the bottom of this screen.

Getting at the data – specific applications

It's all very well understanding how to get at data in general, but once you start working with particular accounting applications, things can quickly become a lot more complicated. David Carter has produced a whole range of tutorials providing step-by-step guidance on working with data from different packages including many of the Sage range, Navision, Pegasus Opera, QuickBooks, TAS, Iris Exchequer, MYOB and Access Dimensions and Analytics.

Getting at the data – understanding databases

If you're lucky, then your software supplier will 'package' the data that you need in a query or provide instructions on how to identify the different tables and fields you need to use. However, sometimes you might need to work with 'raw' data or you might want to access and link data from more than one source. Some understanding of how relational databases work and how the data is structured will be invaluable and will add to what you are able to achieve using Excel's data import and linking capabilities. Once again there is a series of tutorials in IT Zone that should help ease you into the subject. 

Working with the data

When it comes to summarising and analysing accounting data, whether as an end in itself or as the basis for a set of reports, it's well worth investigating Excel's Pivot Table feature. Once more, it is David Carter who has covered the area very comprehensively in the most popular series of articles ever produced for Excel Zone. Excel 2007 introduced some significant changes in how Pivot Tables work and also extended their capabilities to work with other features such as conditional formatting. Excel 2007 – a new approach to pivot tables should explain the changes and improvements.

Third party tools

Fun as it is to use and experiment with all the various Excel data acquisition and manipulation tools, using dedicated third party applications can make things much easier as well as significantly reducing the risk of error. IT Zone includes an excellent introduction to Excel-driven reporting tools that covers the reasons for using third-party, and indeed Microsoft, tools in conjunction with Excel as well as describing some of the main underlying technologies such as data warehousing and Online Analytical Processing (OLAP). The introduction looks in some detail at general tools for working with accounting data as well as some of the tools provided by the software package suppliers themselves for working with their own applications. David Carter provided further guidance and examples of Excel-based report writers.

Presenting the data

Having captured your data and analysed it and summarised it as required, you may then need to create a series of reports based on the data. Communicating the important aspects of the data as effectively as possible often involves the use of Excel charts. A recent three part series looked at many aspects of presenting financial figures with Excel including a discussion of the works of Edward Tufte and his guidance on how best to present quantitative data, as well as an overview of the different types of Excel charts and an introduction to the use of 'dashboards' to present an 'at a glance' overview of financial data.

 Further reading

ExcelZone Compendium archive - more queries and answers on using external data
IT Zone guide to analytic applications for business
Want to learn about Excel pivot tables? Start here
Improve your reporting skills with self-teach tutorials

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.