Melbourne, Australia (Wurundjeri country)

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:

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

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

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.

Related Articles

Xero Transaction Limits and larger businesses: Nov 2018 update
There are questions about how well Xero performs under "higher loads". Our first concern was for medium-sized wholesalers. Xero talks about limits as low as 1000 'transactions' a month. Our testing shows that Xero performs well at much higher transaction volumes. The first test results are based on 3 years of 2500 transaction rows per month (and then matching payments uploaded via a bank CSV). The second test run was much larger.
Note that after some discussions with Xero and based on our own experience, we (GrowthPath) believe that businesses can interpret the 'limit' of 1000 invoices a month as advisory. This limit does not form part of the terms of service. In Jan 2017 Xero published some small changes to the API limit, and these limits, which are enforced, allow a much, much higher load than Xero's advisory limits. Hence, stress testing is relevant because you will reach the practical limits of Xero before you exceed API...

Advice & recommendations on selecting and implementing a new accounting or ERP system, cloud or otherwise. Including strategies for going live, and when to to live. What are the main steps in the project? What testing is needed? When you should you go live?

Cloud ERP inventory for wholesalers, manufacturers and importers
Unleashed, Dear Inventory, Cin7, Neto and Trade Gecko compared and reviewed, in a nutshell
GrowthPath has migrated clients in Malaysia, Singapore, New Zealand, the UK, PNG and around Australia to cloud ERP solutions. The clients left desktop solutions sch as MYOB and SQL Server-based legacy systems for cloud-based accounting and supply chain solution (ERP). We have success with Xero + Unleashed, Xero + Dear Inventory and Xero + Cin7. At the moment, we recommend Dear Inventory for most clients needing a supply chain solution, and Cin7 for more advanced requirements including POS, an integrated B2B portal and better logistics and market-place integrations. We see the competitive pool also including Unleashed and Trade Gecko, but at this stage Dear and Cin7 are our recommended solutions.
Broadly, Unleashed, Dear Inventory and Trade Gecko are competing for the same target market. They are good general purpose supply chain...

Custom Integrations for Cloud APIs
About Integrations and Cloud apps
Large business systems are built from modules. This is very ancient tradition of accounting: way, way back in the day, accounting was run with different ledgers, such as the sales ledger or the stock ledger, and periodically summaries were copied to the general ledger. When you implement a system, you choose a provider of these modules. If you choose different providers for different modules, you need to understand how they integrate, which means how they exchange information.
When functionality is very generic, it's common to find a single solution with a core set of modules which integrate. It is very rare to find an accounting system which does not provide ledgers such as sales, bank, AP and AR, and you don't need to worry about the integration. Not so long ago, accounting applications still required you to post transactions from one module to another, but this vestige of the middle ages is gone in Xero.
But as...

On July 1, 2018, the ATO requires all Australian businesses with more than 20 employees to use Single Touch Payroll. The census date is actually in April 2018. Users of MYOB Account Right Premier face an interesting problem. They are using software which doesn't really get feature updates because MYOB is encouraging subscribers to move to Account Right Live, a monthly-subscription product. This product was called Account Right Live for a few years. I will refer to it as Account Right Live.
Account Right Live doesn't support foreign currency and does not have multi-location inventory, and is unattractive for other reasons (it requires a Windows computer. It can store data in the "cloud", but it's is often too slow and larger businesses are most likely going to revert to local data, which means you basically have your old MYOB, but without foreign currency). Many businesses have stayed with legacy MYOB because they can't use Account Right Live or don't find it worth the effort of...

GrowthPath's review of Xero, Saasu, MYOB and QuickBooks is a detailed comparison of the leading cloud accounting software for mid-sized and larger Australian SMEs. We discuss the pros and cons of moving to the cloud, and how cloud accounting is the building block of cloud ERP.
UPDATE: On March 8, 2018, Saasu basically announced it was withdrawing from the SME accounting market (to focus on micro-businesses). This article has not yet been updated to reflect this change.
In a hurry? This short article covers the key initial questions: Top questions about migrating to cloud erpIf you want more coverage of cloud ERP choices, see this detailed comparison of Unleashed, Dear, Cin-7 and how they work with Xero
Summary
To summarise, for most Australian SMEs, Xero is the best choice. Xero has more functionality, more extensions and more support than its competitors. It's only technical rival is QuickBooks online, but Quickbooks Online has a small market share in Australia. The dinosaur is...

Easier access to Dear Inventory advanced pricing
Dear Inventory has a powerful advanced pricing module in addition to the standard ten price lists and customer-level discounts. Not many clients use the advanced features because it's hard to understand and hard to set up; for example, the second step requires a CSV upload in many cases.
Dear has now put a graphical front end to advanced pricing, making it easier to use. Advanced pricing is the place to add quantity breaks and free shipping rules, as well as much more.
What is 'advanced pricing'? The advanced pricing rules can either provide over-riding prices, that is, the rules acts like a new price list, or they can merely modify the price the customer was going to receive anyway (discounts for higher order quantities, for example).
Advanced pricing is also the only way you can make special pricing for some but not all products for some but not all customers. Finally, the rules can have start and end dates, something else you...

Dear Inventory has a fundamental B2B portal which is in its second release. Bottom line: it's easy to setup (a couple of hours at most to get up and running), it's free and it automates basic order enquiries and order entry. You control exactly which customers can use it.

Allocation Logic in Dear Inventory, explained
Dear Inventory has two order flows, Standard and Advanced. They both let you prioritise stock with soft and hard allocations if you use manual picking. The Advanced module has another option: authorised orders with no allocation at all. In this article, we see the differences between soft and hard allocations, and how to override soft allocations to prioritise which customers get scarce stock.
You can swap a Standard order to Advanced. An order created in the standard process can move to the Advanced process via the Convert button. See the picture below.

Some tips on getting per-location costing in Dear Inventory for more accurate margins.
Dear Inventory is a cloud-based inventory system which is currently GrowthPath's default recommendation for general purpose cloud ERP systems.
Dear has a well-thought approach to stock valuation. From the beginning Dear was designed to give users the chance of activate batch and serial number tracking for receipts, and Dear decided to fully support this with FIFO product costing per batch or serial. In fact, Dear users FIFO costing as its costing method for standard stock as well; the common choice is weighted average. FIFO and weighted average are the same over time.
Like its competitors, such as Unleashed, Trade Gecko and Cin7, Dear allows multiple locations, but it does not keep costs per location.
Imagine that you are receiving stock into an Australian warehouse and a New Zealand warehouse from a Chinese supplier. You may have the same FOB price, but different landed costs. Dear handles landed...

Common questions about cloud ERP
Cloud ERP (Dear Inventory, Unleashed, CIN-7, Trade Gecko, Stitch...) has huge advantages: fast to deploy, easy to learn and with much better reporting and insights that traditional ERP software. Above all, these packages integrate so easily with CRMs, accounting, online stores, third-party fulfilment, shippers. The business model is different: you pay per month, you no longer host the data. Here we quickly cover the main questions, advantages and disadvantages of cloud ERP for SMEs.
We have a much more in-depth review of common cloud ERP solutions here: A review of cloud ERP for Australian SMEs

Business risks of cloud software
Cloud software has security risks, data-lock-out risks and connectivity risks. These risks can be avoided, and cloud software eliminates other major IT risks. Overall, moving to cloud apps such as Xero probably increases overall security and lowers data risk if sensible policies are in place. Before moving to cloud accounting such as Xero, or to a cloud ERP system, it's worth reviewing these risks.

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.

Here is the IT in use at GrowthPath.
Accounting
Since our foundation, GrowthPath has used cloud accounting. Initially we used MYOB Essentials, then Saasu, and as of 2016 we are on Xero.
CRM and Project Management
We use Pipedrive. Pipedrive is an excellent CRM for deal-based businesses.
We have become big fans of Trello.
Quotes are prepared with Nifty Quoter.
Email & Cloud Apps, Client OS
We use Google Apps/Google Drive. Desktop OS is a mix: OS X preferred, Chrome OS and Windows get some use. Mobile devices are now mostly Android.
Web services, Analytics
We have servers, and client servers, hosted with AWS and Digital Ocean. We use Ubuntu as the platform of choice. We use namecheap for SSL certificates, and a variety of registrars for domain names. Analytics is 100% Google.
Content Management
GrowthPath uses Joomla, self-hosted, although we have good Wordpress skills.
Traffic Generation
GrowthPath does not currently use any paid traffic generation techniques. Our SEO is good and...

Applying relevant information to make better decisions is a very important outcome of a medium-term IT plan. Cloud solutions are really good at enabling this, even if the first phase focuses more on the operational foundations. There are many options available for cost-effective business intelligence solutions in the cloud. Amazon, for example, has developed a rich layer which has stimulated many offers: Google for “Amazon Redshift marketplace”.
Advanced reporting, or business intelligence, has three components. It has a reporting or data visualisation front end (graphs, dashboards, reports etc). It has a data store and analytical logic layer, which imports data from different sources and allows us to group and relate the data to support the visualisation layer. Business Intelligence products always include these two layers. Many of the cheaper cloud solutions, such as Zoho Reports, effectively stop with these two layers.
The third layer is very important. Technically it is know as...

A hidden gem: Neto/Saasu Integration
Neto is an excellent hosted online store. It handles wholesale and retail very well (simultaneously), and has the best-on-the-market integration with Australian shippers, anywhere. Better than Unleashed, Dear Inventory, Shopify ...
On top of that, Neto looks after multiple price lists, variations, multiple stock locations,has a decent template engine, a fast backend, good ebay integration. It does backorders like MYOB, which is actually much more suited for smaller wholesalers than the current approach of Dear and Unleashed. A startup from Queensland with Telstra as a significant shareholder. It's not perfect: It doesn't do multi-currency, its reporting is average. And it has no purchasing module. It doesn't do supplier invoices, receipts and costing. For this, is expects you to integrate it with something else. A Neto / Saasu integration offers a lot for the money.

It's great to have a well used accounting system quietly gathering thousands of data points about your business. Your bank transactions. Your invoice detail lines. Your purchases. Your stock movements. Your postage and electricity costs. The location of your customers. Their history with you. And so on.
But how to turn it into profitable insights?