Create Reports Using Excel

Extracting data from MTS Franchisee software could not be easier, as the latest versions of programs like Excel (version 2016 and later) have the ability to communicate with web applications like MTS Franchisee built in.

In this guide we’ll explain how to download data from your MTS Franchisee software into Excel.  Specifically, in this example we’ll download information about sales and refunds.  However your MTS Franchisee has a fully featured API allowing you to download lots of different data, from targets to rate cards and more.

First open up a new Excel file —> click the Data tab in the ribbon (1) —> click From web (2):

When you click this button, the system shows you the API URL for the page you are on, in this case sales: 

Click OK in Excel, then click ‘Basic’ and type your username and password that you use for MTS Franchisee.  Ensure the user has the permission ‘Access API’ in their access profile, or this step will fail: 

Now Excel will open Power Query.  Here you need to do some quick clicks in order to transform the data from the API into a table for use in Excel.  Click ‘List’ next to ‘listData’: 

Right click on the ‘List’ header and click 'To Table' (and press ok to confirm convert to table): 

Finish the transformation by expanding the columns of the table:

Now click 'Close and Load' and your data will appear in Excel:

Now your data is in Excel! You can manipulate it in all the usual ways, for example using Pivot tables and Charte=s.

You can refresh you data any time by simply right clicking on the table and pressing Refresh, or by selecting the refresh icon in the Table Design tab:

To take it to the next level, consider exporting your data to Power BU for even more powerful ways to analyse your data!

You can also learn how to filter the data that the API will return, by including parameters in th URL.