A tutorial about accessing data from MYOB Premier using SQL.This technique opens up much better insights, interactive reporting and feeding data into dashboard tools, as well as making data migration into a new system much easier. You can also automate or partially automate intercompany reconciliations, and detailed cashflow analysis.

 

Please note that this article refers to older MYOB files using the .myob file format, such as Account Right Premier and Enterprise. The hybrid semi-cloud Account Right Live versions use .myox: this is a real SQL database format, although it is an "embedded" format (similar to MS Access or SQLite).  More information about extraction tools for this newer format is here:

http://exportsqlce.codeplex.com/

The examples in this article are still relevant, because I doubt the table structure has changed much.  

I will also show some more advanced queries. Note that I won't talk about updating MYOB. This non-developer driver doesn't support that. And only the brave would try anyway.

I assume you know some SQL. SQL is a very old and mature way of specifying database queries. Learning it is useful.

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 it provides reports for. A 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.

I have used this technique to feed data into a business intelligence tool (Pentaho), and from there made drill-down queries into sales, overdues, and used MYOB data to build a dashboard. I have also used this technique to automate intercompany bookings, converting to new systems, for consolidations and for multi-year reporting. 

Often it's best to copy MYOB's data into a proper database, and query it from there. SQLServer is a good choice since many business already have it on a server, and if you don't, Microsoft offers free versions which are more than adequate. Why copy the data? MYOB's ODBC driver has some weaknesses. It supports a limited subset of SQL, it's slow, and you are contending with other users to access data. As well, you can't query multiple files, and for tables which summary account balances, you run into MYOB's calendar, which redefine "last year", "the year" and "next year" everytime you roll forward a year. When extracting data into a proper database, it's sensible to add real year numbers to the tables. 

GrowthPath actually uses sqlite: we have in-house code which extracts MYOB data into sqlite, which is fast and quite powerful single-user database. An entire MYOB database can be extracted very quickly. We do daily extractions of sales and inventory from MYOB for a feed into BI for some clients: it takes only seconds. 

Note: there is at least one third party product (ODBC Link) which also extract data into SQLServer. I haven't used it. 

What about AccountRight Live? This is a more modern MYOB product which stores data both locally and in the cloud (i.e., on the internet). It also supports a REST-based API, and no longer supports the ODBC driver. The product is different from traditional MYOB: it offers a different set of functionality, and in some cases that means less functionality. You can not run SQL queries directly on this product. 

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.

I'm going to extract cash payments for this tutorial. That is, I 'll demonstrate how to join a few essential financial tables to link bank account transactions to supplier payments.

Step 1. Install the ODBC driver software. Note that if you have third party MYOB extensions, you probably already have the drive.

Installing it is quite easy; under MYOB Tools in your program menu (Start menu) you should find "Install MYOB ODBC Direct".

Step 2. Pay for and enable the site-wide read-only licence

To buy the licence, call MYOB. To activates the licence, you'll need to go into each MYOB database as administrator, go to Setup and Company Information, and choose Manage Licences.

Step 2 is not needed any longer. The ODBC software is now free.

Step 3: Set up a System DSN in Windows control panel.

ODBC management is in a control panel called Administrative Tools. If you use a 64 bit version of Windows, you will need to use the 32 bit ODBC control panel; you need to find this. Google for help on this if necessary.

Create a new system connection. The MYOB driver is in the list of driver types. The version numbering and exact name of the driver changes (I'm testing this on a machine where the driver is called MYOAU0901). The driver selection dialog also shows company name in a column; if you look for the installed drivers from company MYOB Limited, you'll quickly find what you need. To learn about this, you may want to copy a database file to your local hard driver. You'll need to choose the path to the database file, and provide a user name and password; I've always used the administrator user. You'll need to refer to the system DSN name so choose something logical, example CompanyNameFY10. My example is StompGLFY10.

If you do copy the file, make sure that the ODBC Licence is still valid by repeating the licensing steps above.

Using SQL and MYOB: Two approaches

MYOB's SQL driver is slow, lacks some important SQL features like outer joins and subqueries, and crashes. As well, accessing data in multiple MYOB files is difficult.

However, if your requirements are simple, you can pull data into MS Excel using Microsoft Query. That's the approach I'll take in first section of this tutorial.

A more advanced approach is to copy data from the tables into a real SQL server, such as Microsoft SQLServer. There's a free edition which is more than adequate. I'll come back to this in a later article.

MYOB's SQL Documentation and Tables

On drive C you'll find a folder installed by the MYOB ODBC Direct installation.

There is a subdirectory HELP with a PDF user guide; this is the documentation to the tables.

The user guide splits the tables into master data, under the heading “Miscellaneous Information”, and transactional data is under Journal Records and Transactions. But some other important master data is found in the section Definitions. Go over those three sections.

Some practical examples of financial transaction queries

These queries can be pasted into Microsoft Query. In a spreadsheet on a new tab, start Microsoft Query (In Excel 2007 or later, go to the Data tab, choose From Other Sources... and then choose Microsoft Query).

Choose the System DSN you made above (or if the ODBC driver was installed by some third party MYOB addons you may have, use the existing System DSN).

You don't want the query wizard.

When you finally get to the dialog for entering queries in MS Query, cancel the Add Table dialog, and choose the View SQL button. Paste in the query.

First, journal entries.

Each financial transaction creates a record in the table JournalRecords.

To get account numbers, this needs to be joined with the table Accounts.

Some notes on foreign currency

MYOB has a retrofitted and primitive ability to pretend that it is multi-currency. MYOB records all values in transactions at the home currency (for me, AUD). An account which is a foreign currency account (say a USD bank account) has two accounts. When these values are added together (ignoring units; you literally just sum the two values), you get the AUD equivalent of the foreign currency balance. This means that as long as you include both of these accounts, you'll always be dealing with what MYOB thinks is the AUD balance. One of the two accounts stores the foreign currency balance; in my example, it would be the USD bank balance. The second account is a running total of conversions to AUD, each calculated at the exchange rate MYOB used for that transaction (which causes serious foreign currency inaccuracies, requiring manual correction each month, in my experience).

Example query one: Joining JournalRecords and Accounts with a date filter.

SELECT Accounts.AccountID, Accounts.AccountName, Accounts.AccountNumber, Accounts.AccountClassificationID, Accounts.SubAccountClassificationID, Accounts.AccountTypeID, JournalRecords.Date, JournalRecords.IsExchangeConversion, JournalRecords.IsForeignTransaction, JournalRecords.SetID, JournalRecords.TaxExclusiveAmount, JournalRecords.TransactionDate, Accounts.CurrencyExchangeAccountIDFROM Accounts Accounts, JournalRecords JournalRecordsWHERE Accounts.AccountID = JournalRecords.AccountID AND ((JournalRecords.Date>{d '2009-06-30'}))

AccountID AccountName AccountNumber AccountClassificationID SubAccountClassificationID AccountTypeID Date IsExchangeConversion IsForeignTransaction SetID TaxExclusiveAmount
98 Wages & Salaries 6-5190 EXP EXP D 2009-07-01 N N 250588 -7529.72
343 Accrued Wages 2-1610 L OL D 2009-07-01 N N 250588 7529.72
148 Foreign Exchange Adjustments 5-1010 COS COS D 2009-07-01 N Y 251311 66.68
183 Trade Creditors GBP 2-1202 L OL D 2009-07-01 N Y 251311 10
183 Trade Creditors GBP 2-1202 L OL D 2009-07-01 N Y 251311 2015

Example Query 2: This query shows the journal type for each transaction

SELECT Accounts.AccountID, Accounts.AccountName, Accounts.AccountNumber, Accounts.AccountTypeID, JournalRecords.Date, JournalRecords.IsExchangeConversion, JournalRecords.IsForeignTransaction, JournalRecords.LineNumber, JournalRecords.SetID, JournalRecords.TaxExclusiveAmount, JournalTypes.JournalTypeIDFROM Accounts Accounts, JournalRecords JournalRecords, JournalSets JournalSets, JournalTypes JournalTypesWHERE JournalSets.JournalTypeID = JournalTypes.JournalTypeID AND JournalRecords.SetID = JournalSets.SetID AND JournalRecords.AccountID = Accounts.AccountID AND ((JournalRecords.Date>={d '2009-07-01'}))

Below, a partial extract of Example 2 (some columns have been removed from the output)

AccountID AccountName AccountNumber AccountTypeID Date SetID TaxExclusiveAmount JournalTypeID Description
6 Electronic Payments Clearing 1-1120 B 2009-07-07 252042 -953.74 WP Write Paycheque
6 Electronic Payments Clearing 1-1120 B 2009-07-07 252043 -1451.06 WP Write Paycheque
6 Electronic Payments Clearing 1-1120 B 2009-07-07 252044 -1221.87 WP Write Paycheque
6 Electronic Payments Clearing 1-1120 B 2009-07-07 252045 -1209.9 WP Write Paycheque
6 Electronic Payments Clearing 1-1120 B 2009-07-07 252046 -1443.58 WP Write Paycheque
6 Electronic Payments Clearing 1-1120 B 2009-07-07 252047 -1174.15 WP Write Paycheque
6 Electronic Payments Clearing 1-1120 B 2009-07-07 252048 -878.24 WP Write Paycheque

Showing Supplier Payments linked to bank accounts

Types of transactions in MYOB:

The basic data of an accounting entry is kept in the JournalRecords table.

Individual types of journals are joined via the JournalSets table.

So if you wanted to link SupplierPayments to JournalRecords, you could use a query like this,

which shows accounting entries such as bank withdrawals and the associated supplier payment.

SELECT Accounts.AccountName, Accounts.AccountNumber, Accounts.AccountTypeID, JournalRecords.Date, JournalRecords.IsExchangeConversion, JournalRecords.IsForeignTransaction, JournalRecords.SetID, JournalRecords.TaxExclusiveAmount, JournalTypes.JournalTypeID, SupplierPayments.CardRecordID, SupplierPayments.Payee, SupplierPayments.MemoFROM Accounts Accounts, JournalRecords JournalRecords, JournalSets JournalSets, JournalTypes JournalTypes, SupplierPayments SupplierPaymentsWHERE JournalSets.JournalTypeID = JournalTypes.JournalTypeID AND JournalRecords.SetID = JournalSets.SetID AND JournalRecords.AccountID = Accounts.AccountID AND JournalSets.SetID = SupplierPayments.SupplierPaymentID AND ((JournalRecords.Date>={d '2009-07-01'}) AND (JournalTypes.JournalTypeID='SP'))

Showing SupplierPayments when they are linked to bank accounts

So, we are now ready to answer one of the queries mentioned at the beginning: showing SupplierPayments linked to bank accounts. This answers the question of showing cash payments out of bank accounts in a certain data range and how they link to supplier payments.

SELECT Accounts.AccountName, Accounts.AccountNumber, Accounts.AccountTypeID, JournalRecords.Date, JournalRecords.IsExchangeConversion, JournalRecords.IsForeignTransaction, JournalRecords.SetID, JournalRecords.TaxExclusiveAmount, JournalTypes.JournalTypeID, SupplierPayments.CardRecordID, SupplierPayments.Payee, SupplierPayments.MemoFROM Accounts Accounts, JournalRecords JournalRecords, JournalSets JournalSets, JournalTypes JournalTypes, SupplierPayments SupplierPaymentsWHERE JournalSets.JournalTypeID = JournalTypes.JournalTypeID AND JournalRecords.SetID = JournalSets.SetID AND JournalRecords.AccountID = Accounts.AccountID AND JournalSets.SetID = SupplierPayments.SupplierPaymentID AND ((JournalRecords.Date>={d '2009-07-01'}) AND (JournalTypes.JournalTypeID='SP') AND (Accounts.AccountTypeID='B'))This rows here also show the handling of foreign currency by MYOB, as mentioned above.

AccountName AccountNumber AccountTypeID Date IsExchangeConversion IsForeignTransaction SetID TaxExclusiveAmount JournalTypeID CardRecordID Memo
Westpac GBP account 1-1118 B 2009-07-01 N Y 251311 -4425 SP 2186 Payment; Republic of Music
Westpac GBP account Exchange 1-1121 B 2009-07-01 Y Y 251311 -4670.58 SP 2186 Payment; Republic of Music
Westpac EURO account 1-1125 B 2009-07-01 N Y 251312 -192.32 SP 2072 Payment; Lifeforce
Westpac EURO Exchange 1-1126 B 2009-07-01 Y Y 251312 -144.61 SP 2072 Payment; Lifeforce
Westpac EURO account 1-1125 B 2009-07-01 N Y 251313 -1377.76 SP 2568 Payment; Inandout Distribution
Westpac EURO Exchange 1-1126 B 2009-07-01 Y Y 251313 -1035.97 SP 2568 Payment; Inandout Distribution
Westpac US Dollar Account 1-1114 B 2009-07-01 N Y 251314 -2952.64 SP 1407 Payment; Super D
Westpac US Dollar Account Exch 1-1117 B 2009-07-01 Y Y 251314 -718.43 SP 1407 Payment; Super D
Westpac GBP account 1-1118 B 2009-07-01 N Y 251329 -1685.77 SP 2405 Payment; Ministry of Sound UK

You will probably want to do the same thing for MoneySpent transactions. This is very similar; the MoneySpent table is joined by SetID, just like SupplierPayments.