Account Transaction Very Slow - Xero

Hello :slightly_smiling_face:
I have a 4 companies with over 800K ligns in Account Transactions.
I’m trying to take out the data for two or three GL within a date range.
Exemple : take me out the data for all 4 GL Software within march 2021 month.
I’m not suppose to have a lot of data, but the account transactions report takes for ever. Why ?
Have a done something wrong ?

Thanks for your help!

Hi d.jodoin,

There are a few things that you need to take into account to understand why the Account Transactions report is slow (at least initially).

The Account Transactions Report is actually a time machine report. By this I mean that it contains all transactions ever posted, including any transactions that have been reversed/voided/modified etc. You can “go back in time” by filtering on the “Created Date” but unfortuately this is just a date, not a date & time, which would be more useful.

The speed is slow the first time you run the report it has to pull in ALL the transactions. The main reason it takes a lot of time is purely because of the volume of transactions (i.e. it includes a lot more that the transactions you would see directly in Xero as mentioned above).

However, the second time you run the report DO NOT run it the same way you do it the first time, you only need to APPEND transactions. DataDear records the time the report was run and just adds lines since the report was last run so this is much quicker, you just have to make sure you don’t manually change any lines in the tab that generates the transaction lines.****

Other Options
Unfortunately there doesn’t appear to be an option to automatically refresh this report and I haven’t figured out an easy way to filter out the “non-current” transactions (voided/deleted etc) so I have found it is easier to run the transaction report directly out of Xero, save the downloaded reports directly to a specific folder that is only used for these reports (you don’t even have to open them, and then use Excel’s Power Query function, which looks complicated, but is very easy to use, to consolidate the transactions, allowing you to use the data in any Excel workbook, blank or existing, to review/report on the transactions.

Using the Power Query function in Excel also helps when you need to add data from various Data Dear reports to the transactions, i.e. Hyperlinks/Splitting out the Vendor (I can’t believe Xero doesn’t do this) or linking transactions (i.e. Batch Payments, which Xero has very limited functionality for.

In summary, while the Account Transactions Report in DataDear has cruicial detail need for some tasks, you should definitely only use the Append function after running it the first time but also consider options (like the Excel Power Query in conjunction with DataDear) to maximise DataDears benefits.

Hope this helps…

Jim

p.s.
Excel’s PowerQuery can handle A LOT of lines, as it can extract the lines from multiple workbooks/csv files and stores them in a special table, rather than a tab, it allows you to work with massive data sets.

One of the best things is that once it is set up for a particular report, it is all automated, so you just hit refresh, you don’t even need to open the source workbook and can modify the output by having the query change column titles, add account mapping, filter out transactions not needed etc.
It’s like a pivot table on steroids.

Let me know if you want some pointers on using it.

For any of the DataDear Support/Product Development, it could be a really good option to implement some Excel Power Query options in future releases.

In some instances, it may be better for a CSV file to be generated and sent and then have DataDear use Power Query to then handle the data (filtering/generate data tables etc) on the client side, rather than burning up computer processing power by pushing it directly to Excel, which is likely to always suffer speed issues with large volumes of data… just a thought…

1 Like

Hi JamesA, :slightly_smiling_face:

Thanks for your answer!
I think for the future DataDear should just download the data from the date range since it’s not efficient if it has to download ALL transactions.

As for PowerQuery, I already use it. I wanted to try DataDear because I wanted to be more efficient since I wanted to save time on downloading each Xero report for all the 4 compagnies each time manually and be able to just hit refresh.

I’m always looking for more efficient solution and I’m pro hit refresh button that remove the recurring tasks.

Hi jo.doin,

I have asked the DataDear Team to have a transaction report that is equivalent to the Xero Report, but haven’t gotten a response yet.

When you originally run the query, it is possible to put parameters on so you can choose to look at transactions only modified since a certain date, i.e. based on the transaction creation date, and/or on the posting date. I think this would help with a bit with your extraction time as you could just set both dates to the start of the period (week/month etc). This option disappears once the report is run for the first time though, so you need to start a fresh report.

You can also cheat a bit by changing the last journal number (the one in column B) as the extraction query only picks up from the last journal number, but it would be safer to use the date filters and just start a new query for a reporting period. You could still then append transactions to the old query if you wanted to see if there were any changes to the prior period since reporting if you wanted to. Another way would be to set the dates in a new report so that the transaction date finishes on a set date, i.e. end of the period but change the creation date to be after the date that you complete your end of period procedures.

Although pulling the data out of Xero is a bit annoying, just saving the individual files of each of the four companies to a single folder without even opening, will allow you to consolidate the data with a Power Query since the first transaction line and number of columns would always be the same and Power Query includes the name of the file as a field so you don’t have to cut and paste the reports into a single workbook.

Sorry I couldn’t help more, although I think the time machine function could be really useful, it does limit my ability to investigate the transactions because it includes the reversal/voided trans and is pretty slow… it must put quite a strain on their servers (probably Xero’s too).

Hopefully the DataDear team can either include PowerQuery in a future release, or at least have a report that is in line with the Xero one to improve the speed and automation options.

Kind Regards,
Jim

1 Like

You could also try running it to just extract the raw data… this still gives you the data, just without any hyperlinks, so it might help the report to run a bit quicker. :slight_smile:

Requested Product Options for future releases

Notwithstanding the request above, there is also an option to request a customer solution… not sure how much the charge/hr of development time for them though.