It is already well documented in this forum that QBO online does not, in any way, provide a way to run a report showing the foreign balance of an account.
Unfortunately, there is an incorrect understanding of how the balance sheet detail report works, even among QB employees that have responded to these threads. They assume that because you can see exchange rates for individual transactions that from the balance sheet detail report you could somehow calculate the foreign balance. This is not possible because on any account that is of a foreign currency type the beginning balance on the balance sheet detail report is shown in the home currency and there is no exchange rate given. It is therefore, not possible to calculate the ending foreign balance when you begin with an unknown beginning foreign balance.
In addition to not being able to report on the foreign balance of an account is is true that it is also impossible to calculate the foreign balance of an account from any report that QBO is able to export?
Will QBO add the ability to accurately calculate a foreign balance?
It was suggested by QB employees to use the balance sheet detail report and show the exchange rate column to manually calculate this. As previously stated this will not work because you don't know the beginning balance rate.
The only way that I have found to find the ending foreign balance is to run a General Ledger report and have the 'Foreign Amount' column showing. In addition you have to select to generate the report for 'All Dates.' If you run it for 'All Dates' then you can see the first line should be 'Opening Balance.' It will show the home currency equivalency in the 'Amount' and 'Balance' columns and it will show the foreign currency amount in the 'Foreign Amount' column. If you export the file as an excel file then you can select this first foreign amount and drag down to the date you need to see the balance for that account and in the excel footer window you can see the sum to that date.
As far as I know this is the only way to see a foreign balance at a specific point in time. Unfortunately it has to be done manually and unfortunately you have to export all the data for all dates to get the opening balance to work from but at least there seems to be some way to get the data out of QBO.
Thanks for the details and feedback about the multicurrency in QuickBooks Online, Drew_E.
When running reports with foreign currencies, there are always differences of a dollar or cents. This is because the currency rates may differ from the time you recorded the transactions and when they were cleared from your banks.
Hence, we can only recommend to customize the reports on the other links you've attached on this thread. And that would be our best option we have when pulling up reports with foreign currencies in QuickBooks Online. Your accountant might also have some other ways to pull up the data for your foreign currencies.
Your feedback is an eye-opener especially to the other QBO users that use the multicurrency feature. As we get the bottom of this, you can always visit the QuickBooks Blog for any updates.
We really appreciate these details and we want to hear more on how we can improve the product to suit your business setup and needs. Have a great day!
I would like to point out that the suggestion that I gave with the General Ledger would be 100% exact with 'Cash in Hand' account and have no difference at all because you would be looking at a foreign balance account. When you start with that opening balance and subtract out only transactions in that currency then you would be left with an ending foreign balance. This balance isn't impacted by currency rates. Those changing rates only impact the home currency valuation. That is why we do 'HOME CURRENCY' adjustments and not foreign currency adjustments. Again, if you begin with the opening balance then this is 100% accurate because it removes exchange rates from the equation.