Xero import using Tracking feature?

Hi Kevin,

Modano is accounting package agnostic, we've just added some basic integration with Xero and QuickBooks because they both have APIs which make this quite easy to do. But it's not a primary focus, as the they're both just historical data sources and there's really only marginal benefit from using the APIs rather than simply exporting to Excel and then importing from the exported Excel file.

I'm not a Xero specialist, but I'm assuming that the Tracking feature within Xero is really just the ability to categorise your financial statements data into groups (e.g. regions, business lines, etc.), and that this is often how Modano users would want their historical data from Xero to appear in their Modano model. Let me know if I'm missing something here.

If this understanding is correct, I'd recommend using the following approach to import from Xero including your financial statements including the Tracking feature improvements:

1. Open the most suitable dynamic template via the Modano tab, New menu, Dynamic Template;
2. Export your historical financial statements from Xero into Excel;
3. Open the exported financial statements in Excel.
4. Activate the historical income statement in your dynamic template and load the Import Assumptions tool;
5. Select Excel/CSV as the source of assumptions;
6. Select the exported Xero income statement as the source workbook and range;
7. Select 'New' mapping file in the mapping criteria step to create a mapping file to automate future imports (e.g. when updating the model each month). When importing from Xero you often also need to check the 'Reverse columns' check box because Xero exports are in newest to oldest order across columns;
8. Map and reconcile the account as you'd normally do when importing directly from Xero; then
9. Repeat this process for the historical balance sheet.

Going forward, you just need to export the latest month of income statement and balance sheet data and repeat this process, but it will be very quick because the mapping files you've created will automate the mapping in all future importations.

Here's each step of the Import Assumptions process:

​​​​​​​

Let me know if this workaround works for you. M.

Hi Kevin,

This is a very interesting topic. We work a lot with clients that use Xero tracking or multiple Xero accounts across a consolidated group, and we've had to find solutions to the problem.

We use an add-in called DataDear that allows you to setup a data workbook to pull data at a tracking level (or across multiple Xero accounts) as well as at an aggregated level. We tend to then use the aggregated level + an elimination module to create our consolidated position for modelling purposes in our modular workbook. The benefit of this approach is you can set DataDear to refresh across multiple entities (and an aggregation) in one go and import once, apply eliminations and you have your consolidated historical P&L and BS. It also provides the P&L and Balance Sheet by Tracking Category or Entity across a time series, so you have access at a granular level.

This isn't ideal for all clients, and the approach noted by Michael above works nicely for fewer entities, but when you're working with 10+ entities or 10+ tracking categories, it's nice to be able to push a button and get the data you need...

Hope that helps.

Jun

Good stuff, Jun! 

Is there anything like DataDear for MYOB do you know?

Client with 30 TB's to consolidate...

Cheers. J.

Hey J.

Sorry Jimmy... Limited time in dealing with MYOB. Usually just ask for the exports unfortunately.

Ouch... 30 TB's to export, good luck... I'll keep an eye out though.

Kevin,

As far as I can tell, it must be significantly improved since. There are a few things to be mindful of when building out the consolidation, but all in all a very neat package - especially using it in conjunction with a cashflow model.

Jun

Hi All,

With DataDear stopping its support for Xero are there any alternatives for this workaround? 

In addition how would I get to that mapping account box if i would want to remap the accounts? Attached screenshot for reference.

Hi Sajid,

You're really powering through Modano, great!

There's a few options on this.

Modano does actually allow for data to be pulled on a tracking category level, so you can actually build this functionality in a Modano model. I haven't tested it yet, but I'm pretty sure the new Business Units content would be a good place to start.

Our approach was actually to set up a Modano model with a combination of entity + tracking category pulls. We had to do this to ensure that the entity level data reconciled with the tracked data that gets pulled. We then built a Power Query to transform the data into a proper dataset, which we could pivot and move around as we needed to.

We then leveraged Power Query within a Modano model to allow for BU + Entity level modelling to be combined in a single model. This approach just means we need to hit the refresh button once we make updates in the model. We built an error check in there to ensure that people realised that this needed to be done. End result is a set of financials that looks something like this.

Our clients found a lot of value out of having P&L per entity / BU, but balance sheet being done on a consolidated basis was more than sufficient, so it's a bit of a hybrid historical consolidation approach. This also allowed us to run consolidated budget variance + BU level budget variance, all whilst keeping the file size relatively small.

Hope that helps!

Jun

Hey Jun

that really helps but need further clarification on 2 points.

1. I could not follow through with this point  "We then built a Power Query to transform the data into a proper dataset, which we could pivot and move around as we needed to." is this something integrated by Modano, or does the user has to extract info from Xero then ETL it into Modano model?
2. I had attached a screenshot in my previous reply to understand how to remap the accounts in Modano import/update assumptions tools.

Lastly and not important, would you have any recommended DatatDear alternatives for Xero? I've been looking at CData, ExlCloud, and G-Accon however, it is not as robust as DataDear. Just looking to upload bank statements to several companies using Excel. it was possible via DataDear before.

Thanks,

Sajid

Guys,

This thread has drifted a long way away from Kevin's original post, which I'm going to revert back to now.

Kevin - Modano 11 now fully supports importing data based on tracking categories from Xero, and our latest Business Units Model content makes it extremely easy to build tracking category-based multiple business unit models with full reports.

We'll release more information about this over the coming weeks, but you should be able to figure it out pretty easily by upgrading to Modano 11 and then using the New Financial Model wizard (Modano tab, New group) to select a Business Units Model starting model.

It's very cool stuff, let us know what you think.

Michael Hutchens
Managing Director | Modano