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 an improved 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:

https://github.com/ErikEJ/SqlCeToolbox

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, which is why SQL extracting to a data warehouse, such as Zoho Analytics, is valuable.

I have used this technique to feed data into a business intelligence tool (Pentaho and Zoho Analytics), 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. I have also used these techniques to send data to other BI tools in CSV formats. It is also a great way of doing advanced data migration if you are moving to a cloud ERP or cloud accounting package. You can read more about how Xero and others compare to MYOB here: Review of Cloud Accounting Choices 

Advanced techniques

I don't demonstrate the most advanced queries: it is possible to reconstruct cash P&L, for instance, by linking bank transactions, invoices, payments and journals. Please contact me at This email address is being protected from spambots. You need JavaScript enabled to view it. if you need advanced assistance; I will treat this as a consulting opportunity. There is an inquiry form at the top of the page.

Also, MYOB has an unfortunate habit of deleting data, if you don't have the full audit trail functionality enabled. Most ERPs do not delete data, they 'void' instead, so normally you can discover data which has changed by relying on record timestamps (last-modified), but MYOB will simply delete rows. Therefore if you are attempting to stay in sync with MYOB, this is going to cause errors if you are not careful, because not only do you need to worry about changed data, you have to worry about disappeared data. The laziest way to deal with it is completely copy MYOB before you process it, but this is not a good solution if you are uploading data to the web.

When I load data to analytics software or data warehouses I hash data to detect row changes and deletions.

Copy data to an real database first

Often it's best to copy MYOB's data into a proper database, and query it from there. If you are joining tables, it is almost essential, due to performance and SQL limitations of MYOB's driver.

Why copy the data? MYOB's ODBC driver has some weaknesses. It supports a very limited subset of SQL, it's slow, it is not robust, and you are contending with other users to access data. As well, you can't query multiple files, and for tables which summarise 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. 

sqlite is a single user database; it has excellent value if you are merely processing the MYOB data before sending it somewhere else, such as to Zoho Analytics.

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 driver.

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

Step 2: 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.

Using SQL and MYOB: Two approaches

MYOB's SQL driver is slow, lacks some important SQL features like outer joins and subqueries, and it crashes a lot with complex or large queries. As well, accessing data in multiple MYOB files is difficult since they are different databases.

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. MS Query is basically hidden in modern versions of Excel, though.

A more advanced approach is to copy data from the tables into a real SQL format, such as Microsoft SQLServer. If you have done any coding, you will have come across SQLite, a perfect tool for a single user who wants to do advanced queries of MYOB data. You can also LibreOffice instead of MS Office. It has a database component which can connect to ODBC sources. It may even be better the MS Office for this purpose.

Here are some links to learn more:

SQLite: http://www.sqlitetutorial.net/

GUI tools: https://sqlitebrowser.org/

LibreOffce : https://www.libreoffice.org/

 

MYOB's SQL Documentation and Tables

On drive C of a computer with a MYOB installation 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).

Tip: If you have not looked at the open source LibreOffice for a while, look again. It has a database component and can access ODBC easily. It handles csv data better than Excel, as an added bonus. And it's free.

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.