Accessing FX Values

[GordyM1]

Hi, I am accessing our MYOB data from the cloud. I can find the transactions I want in either JournalsTransactions table, SpendMoney or ReceiveMoney tables.

The issue I have is that I can only find the NZD amount. The transactions I am looking at are all USD transactions and I need to find the USD amount for each transaction. Each transaction is entered via Receive money in MYOB.

You help would be much approeciated.

Gordon

[Doreen_P]

Hi, @GordyM1

Thanks for your post, and welcome to the MYOB Community Forum.

If you wish to see the transactions entered through receive money, you can run the general ledger report and filter it for the specific account you use for multicurrency. Please let us know if you require further assistance with this.

Best regards,

Doreen

[Mike_James]

Hi @GordyM1 , in AccountRight Premier, to find the FX values you can display the FX bank account in Find Transactions (by account, and the FX values of the transactions are shown.

I don't think there is a way to obtain a report with a running FX balance, but once the list is displayed, right-click and choose Copy to clipboard, and paste the result into an Excel worksheet. If you set up an opening balance you can add a running balance, and repeat the copy/paste of new data each month, or as you wish.

[GordyM1]

Sorry have been away overseas. I probably didn't make myself clear. I am trying to access this data from the backend and report on it in excel. I can find the transaction in the Journal Transactions table but there is no FX amount or exchange rate I can find to convert the amount into a FX amount (it is only shown in NZD).

[Mike_James]

Hi @GordyM1 , what program are you using to read these tables? AccountRight can be accessed using the API, and the Spend Money endpoint for example includes the FX value.

I wonder if you are using a 3rd party program, which presents the data as tables; this program may not be up to date with FX.

[GordyM1]

Hi Mike, I'm using Excel via Odatafeed to access the data. I have expanded every column in the spendMoneyTransactions table and can't find the FX Value?

In the attached example the value is given in column S and column Z. Both these values (80411.70) are the NZD amounts. USD amount is 50000. I just want to find the exchange rate associated with this transaction and then can calculate the USD amount which is what I want.

[Mike_James]

Hi @GordyM1 , as I thought,  that's a 3rd party program that presents the data as tables,  and probably hasn't been updated to include the FX values. 

[GordyM1]

Mike,

Thanks for your help, is there another way I can access the tables that would show it?

[Mike_James]

Hi @GordyM1 , there is, or was, a free add-on called MYOB for Excel, but I haven't used it, as it usually doesn't install or work for me. We promote our own report writer, which handles the extraction of all data, but into a database of our own design, from which we extract data for reports with SQL queries. If you want to know more have a look at our website.

[GordyM1]

Thanks Mike, I'll download a trial of your software.