Wednesday, 23 January 2013

Automatic report generation: part 1

This is the first of three posts that show how Mathematica can automate the processing of data. In this example the data is in the form of Excel spreadsheets. A previous example showed importing data from a PDF.

I haven't added a voice over so a few points to note:

1. The Excel files are generic balance sheets and P&Ls. Each file containing a separate worksheet for a given month.

2. The display objective is to nest different categories. So the first step is to establish a parent-child relationship. For example current assets is a child of assets. This relationship is stored so it only has to be done once.

3. The nesting allows the user to determine the level of drill down they want to display in the accounts.

4. The raw data for this example was monthly data. Once imported and processed the user can toggle between monthly, quarterly, calendar year, financial year, and year-to-date for presentation purposes.

Note that the recording was made on a 7 year old 32 bit computer (running Mathematica V8) and some of the operations shown were slowed down by having to run the recording.

The key take home message I'd like viewers to get from both these posts and the rail freight post is that mundane data processing tasks can be automated. It doesn't matter what format your data is in, as long as the format is coming consistently your data processing can be automated. Additionally this could be taken a step further can all be done in the background and scheduled to run as given times -- but there would be no video to show in that case!

This video is best viewed in full screen mode.

No comments:

Post a Comment