In my previous blog, I reviewed MYOB’s initial release of the foreign exchange routines for their flagship product, AccountRight 2018.3.

I covered the setup and configuration of a foreign bank account and the transaction flow of a foreign spend money transaction as it appears in your chart of accounts. Its was a big change to the original method provided in the v19 “Classic” product but a long-overdue improvement and simplification of their overall master chart of accounts.

Almost a year later foreign currency has now extended to Customers and Suppliers so let’s examine how the new currency solution works with the MYOB Customer ledger on version 2019.3 and 2019.4.

Essentially MYOB has removed the exchange account for your debtor control account and all amounts are shown in the local currency of $AUD. A great simplification however reporting on the originating currency transaction values still a work in progress as I write this blog. I do like that MYOB has surveyed its customers for the most important reports and feature missing they want which is promising. I’m sure clients will be looking forward to the improvements in the coming releases.

Initial results for the foreign currency solution are promising and I’ve had many client queries about how this functionality can work with Microsoft Power BI desktop.

With this latest release, version 2019.4 users now have access to the updated API to access customer foreign currency endpoints.

Here’s a brief review of the new features and how you can use Power BI to get a better reporting experience on your currency transactions.

As documented in MYOB’s AccountRight v2 API, the foreign exchange currency endpoints are shown at the Sales Invoice “Control” & “Sub Ledger” levels.
https://developer.myob.com/api/accountright/v2/sale/invoice/

The API will now allow users to interact with the customer sales ledgers to output the currency records and reveal exchange rates, default customer currencies and foreign currency amounts. These new API features provide Excel or Power BI Desktop users with the ability to query the data and output the sales ledger table data.

MYOB version 2019.3 currently supports foreign currency endpoints for both item and service customer invoice layouts. Professional and miscellaneous invoice layouts don’t support foreign currency endpoints at the time of writing this blog. Version 2019.4 adds the supplier side and also some other foreign currency key fields.

As I mentioned previously, the new foreign currency functionality has been re-designed and operates completely differently to the “Classic v19” product. The new configuration is definitely a major improvement and really aligns well against current best practices surrounding foreign currency accounting concepts.

I have found, however, that MYOB clients are still requesting additional foreign currency reporting options which need improvement and will hopefully be coming in future releases. MYOB has outlined on the support portal what functionality is currently not available for users. Here is the link to that support reference.

As I demonstrate below, Microsoft’s Power BI Desktop is a free software application that can be used to bridge reporting gaps in systems and provides a fantastic alternative for management reporting.

Before you start, there are a couple of pre-requisites required for your MYOB reporting solution. You need to install the latest version of Power BI Desktop, MYOB AccountRight 2019.3, MYOB API and of course a copy of your datafile.

Next, you need to open a web browser (on MYOB pc) and enter the localhost web URL shown below to retrieve your data file information.

Note the ID shown in the image below (Client User ID) will be required by Power BI to work out which MYOB data file you want to query. If you cant get to this point means your API software is not installed or configured properly and you need to troubleshoot the installation of the API.

To start your Power Query, open Power BI desktop and enter a blank query by clicking the “HOME” tab from the top menu selection and “EDIT QUERIES“.

Note the November 2019 release changed the Power BI ribbon design to be more in line with Office 365 hence the EDIT QUERIES is now called TRANSFORM DATA

Select the “NEW SOURCE” button and choose a “New Blank Query“. This opens the blank query in the editor.

Then click the “Advanced Editor” button and paste my query below, note you will need to change the ID to the value returned from your own API shown in the web browser.

let
Source = Json.Document(
Web.Contents(
“http://localhost:8080/AccountRight/73d88ab3-d697-475e-aa0c-0c99677edef0/Sale/Invoice?api-version=v2″))
in
Source

Click the “OK” button and it will return the initial output shown below. You can see the end point’s metadata returns a list of 62 items and there are no more pages shown as the Next Page Link is null. If you have further data shown in the next page link you will need to perform additional steps which are not covered in this blog.

Clicking on the yellow coloured “List” (shown above in the green highlight) will perform the “Navigation” step in the “Applied Steps” panel shown in the image below on the right.

This action reveals a list of records containing the columns and rows of sales data.

I then complete some basic power query transformations such as converting the list to a table, expanding the sales records, choosing column names and filtering the required data.

After performing these steps you should be presented with the sales customer ledger data as shown below.

I have just selected a few columns for my report. Note each of the key values for reporting as follows:
1. The Total Local Amount of the invoice converted to the local AUD currency.
2. The Total Foreign Amount based on the chosen currency
3. The currency code of the transactions.
4. The exchange rate based on the currency chosen and exchange rate entered into the invoice.

Now that you have your key data columns it’s possible to format these values and build measures to break out the currency data and various business metrics required by management.

Here is an example of my Customer Sales Ledger Model and a custom foreign exchange report I have built using MYOB’s demo file, Clearwater.

Using Chris Webb’s blog explained here, I have added some base64 images for a customer logo, country flag, currency symbol slicer.

This really adds a dynamic impact to reports and saves valuable model size with the improved image techniques so thanks to again to Chris and Jason Thomas and Gerhard Brueckl for the initial post offerings on this subject matter.

master-customer-ledger-forexblog-1

 

Power BI Desktop really adds a powerful dimension to your business reporting systems so I would encourage all accountants out there to give it a try!

It’s free to download and use, you only have to pay a subscription to Microsoft if you want to share your reports with others or automate and schedule the data refreshing capabilities.

Look out for my future blog posts where I will examine foreign currency hedge contracts and further currency endpoints as they are released by the MYOB.