Accessing MYOB data from outside MYOB with SQL

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.

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

Step 1. Install the ODBC driver software.

Installing it is quite easy; under MYOB Tools in your program menu (Start menu) you should find "Install KYOB 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 3: Set up a System DSN in Windows control panel.

ODBC management is in a control panel called Administrative Tools.

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.

Mine is c:\MYOBODBCAU9

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, go to the Data tab, choose From Other Sources... and then choose Microsoft Query).

Choose the System DSN made above. You don't want the query wizard. When you finally get to 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 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, 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 AUD. 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, by the way).

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.CurrencyExchangeAccountID FROM Accounts Accounts, JournalRecords JournalRecords WHERE 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.JournalTypeID FROM Accounts Accounts, JournalRecords JournalRecords, JournalSets JournalSets, JournalTypes JournalTypes WHERE 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.Memo FROM Accounts Accounts, JournalRecords JournalRecords, JournalSets JournalSets, JournalTypes JournalTypes, SupplierPayments SupplierPayments WHERE 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.

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.

To come: more advanced queries, including account balance information sufficient to make trial balances and P&Ls.

About the author

Tim Richardson is an experienced finance professional who differentiates himself by his versatility, his IT and system skills, and his people skills.He has an enormous range of leadership experience gained in the FMCG sector, online B2C, manufacturing and international supply chains, and he has worked in emerging Asia, mature Western Europe and transforming Central and Eastern Europe. His qualifications span the communication strengths of an Arts degree, the analytical skills of a Computer Science major and the professional skills of a Masters degree in Finance. His has built teams of all kinds of talent mix and lead them through change, and has a strong record in recruiting and developing people. His practical IT skills and systems background is very strong in maximising the value of information spread throughout the organisation, and his abilty to help experienced management focus on the issues and consequences of decisions enables him to add value very quickly. He is currently enjoying his CFO role with an Australian SME with a major international online presence.

Add New
Search
RSS

Comments (0)

Tags:

MYOB

Add this to your website

< Prev

Tim Richardson