Tim Richardson

Melbourne, Australia

  • Increase font size
  • Default font size
  • Decrease font size

Building Accpac reports in excel: part 2

E-mail Print PDF

In this article, I'm going to build an Excel report to query AR; that is, a pivot table aging report.

The more advanced techniques demonstrated here are

a) connecting to accpac from within an Excel macro

b) using an Accpac view to safely update the Accpac database

Pivot tables are a great reporting tool. In this case, we have our customers grouped into sales channels, so the pivot table starts by summarising per sales channel, and allows drill down to customer. Another click lets the user drill down into invoice detail. We also show credit limit, terms, last payment date. We have the same format for AP with the same drill-down capabilities.

I will assume the reader knows basic Excel macros.

Accpac does all the hard work of aging: there is a temporary table which very neatly ages all open items. However, we can't simply query this table in Excel using the SQL approach I explained in part 1, because the temporary table needs to be updated first; this aging table is not updated whenever something changes in AR. What we do is get Accpac to do the work, and this can be done from within Excel, via a macro which connects to Accpac.Accpac bundles program logic into "views"; updated AR is a certain view, and the job of our macro is to run that view.

Working out which view you want is pretty easy. I recorded a macro while running the existing AR Aging report, and in the code generated, I learnt how to invoke the view. Recording macros in Accpac is not the same as running macros in Excel; hopefully this tutorial will bridge the gap.

This was developed on Accpac 5.5A.

Last Updated on Thursday, 16 December 2010 08:00

Accpac SQL query scrapbook

E-mail Print PDF

I'm going to store here a scrapbook or cookbook for Accpac SQL Queries which may be useful.

See here about Accpac SQL queries and Excel

Last Updated on Friday, 19 November 2010 09:06

Building Accpac reports in excel: part 1

E-mail Print PDF

In my Accpac review I mentioned that I avoided using Crystal Reports. Instead, most of my queries and reports have been Excel spreadsheets. Typically, one tab is populated with data from Accpac, and then the report pages are pivottables. This allows users to drill down, summarise and work with data in a familiar environment. Basic queries also avoid any additional licences.

In this article, I will show an example: a report analysing cash transactions against bank accounts.

In next article, I will show a more advanced example: an aged receivables report, which uses an Excel macro which first connects to Accpac to update the aging tables, and then queries the result. This is a very interesting example because it shows the true power of automating Accpac with Visual Basic for Applications, a simple technology available to any one with Microsoft Office. 

Last Updated on Wednesday, 22 September 2010 11:28

Accessing MYOB data from outside MYOB with SQL

E-mail Print PDF

A lot of Australian SMEs use traditional MYOB products, such as MYOB Premier. MYOB has a few weaknesses, but it's well known, well supported and handles basic accounting quite well. In fact, it handles it so well that there is a lot more data in MYOB than you can report on. A huge weakness of MYOB is the poor reporting.

Another weakness of MYOB is its treatment of historical data. Many MYOB sites will have historical data kept in old, archived databases.A technique that allows access to this data is interesting.

This is a tutorial about accessing data from MYOB Premier using SQL.This technique opens up enormous possibilities to do interesting things with MYOB data, as well as making data migration into a new system much easier, and it helps tremendously with ATO GST Audits (I speak from experience).

I'm going to assume that the reader has some basic SQL. In this tutorial, I'll cover basic queries; you can use Microsoft Query and Excel. If you don;t know what ODBC is and how to query data into Excel, you'll want to learn that and come back here.

Last Updated on Monday, 20 September 2010 19:02

Useful Excel macro for working capital forecasting

E-mail Print PDF

Here's a useful Excel macro function for simple working capital models where you want to model changes in cash cycle inputs, such as supplier and customer payment terms. It's low on error checking for inputs :-)

I'm the author and I put this into the public domain. It's Visual Basic for Applications (VBA).

Last Updated on Monday, 02 August 2010 06:34

The importance of personal SEO skills

E-mail Print PDF

Just spent a few too many minutes trying to find an email sent by a colleague months ago, which he couldn't recall. My Windows desktop is indexed by Google Desktop, but we had a very hard time finding the email. It had two graphic attachments and only a two word subject, with no text in the body. Hard for search engines to do much with that. We are an online company with world-class expertise in making sure our content is indexed by google for good search results.

Conclusion: Use good subject lines in email and use in as many keywords as you can. Do unstructured tagging in email.


Accpac ERP review

E-mail Print PDF

Accpac 5.5A ERP review (particularly for MYOB upgrade)

This is a review of Accpac ERP from the point of view of a small business (previously using MYOB).

Context: two companies, very international trading business, turnover in the range of approx $100m AUD, with around 85 people.

Order entry and stock is handled by an internally developed system with minimal interfacing to the finance system. Until mid 2009, MYOB Premier was used by Finance, with around five to seven concurrent users. We have been using Accpac since July 2009, as our finance system. We are using version 5.5A, the current version at the time we deployed it.

We took a very minimal approach. We have no customisations to Accpac functionality, apart from a few in-house developed reports. We also did our own data-conversion from MYOB: all open items and account balances for the past two financial years were loaded with Excel macros. This was quite a lot of work. The MYOB ODBC driver was invaluable for this (it costs about $300).

Last Updated on Monday, 20 September 2010 22:07

Fixing CBA Netbank OFX exports

E-mail Print PDF

Netbank, the Commonwealth Bank of Australia's online banking service for consumers, allows export of OFX formats. I noticed this only recently; until now I've been exporting in the less-sophisticated format QIF.

OFX is a huge improvement with my personal finance software, gnuCash, because Bayesian filtering is invoked to match transactions to expense accounts. Bayesian filtering is the logic behind spam filters. It's also good at guessing how to read narrative data on a bank statement. I'd say 90% of my bank entries are classified automatically, which is a huge time saver.

However, Netbank currently causes some big problems with OFX export. It often leaves the vital "ID" field blank. This field is supposed to contain a unique identifier for each transaction. Blank entries are not trusted by gnuCash: they are ignored. I're reported this to CBA. Below is a simple Python script to fix the problem.

Last Updated on Wednesday, 15 December 2010 20:15