Adaco: Accessing the Property Audit API using Microsoft Excel

Overview

In addition to the Adaco desktop application and mobile-enabled apps, Adaco also provides access to your data via its Web API. The API is typically used by external point of sales (POS) or finance systems to get and send data to and from Adaco but it is also possible, in fact quite simple, to connect to the API using Microsoft Excel to retrieve data directly from Adaco.

Whilst the Adaco API has many endpoints that receive and return data for different business objects (as documented on our developer website), this guide specifically refers to retrieving data from the Property Audit endpoint. Typically, the Property Audit would be used by someone at head office to assess how each property is utilising the system.

The Property Audit endpoint returns the following key data points for each property within an Adaco site. 

• Property Number – the internal unique number for each property
• Property Name
• Active – will be true or false
• Property Type – will be either Enterprise, CentralPurchasing or Property
• Parent Property Number – Will be shown as -1 for an Enterprise, a CP which is not under an Enterprise, or a Property which is not under a CP
• First Order Date – the date that the first purchase orders were created in the property
• Last Order Date – the date of the most recent purchase order
• Number of Orders Last 12 Months – this is the number of orders created which is not necessarily the same as the number of orders received
• Number of Open Orders – the number of orders not yet fully received
• Last Recipe Modified Date – the last time a recipe was changed, which could include an update to its cost price
• Number of Uncosted Recipes – recipes which cannot be costed due to non-convertible units
• Last POS Load Date – the last date and time that sales were loaded from an external POS system
• Last POS Load Records – the number of sales records in the most recent batch
• Last POS Load Processed Records - the number of records in the most recent batch that were processed
• Last POS Load Value – the value of records that were processed in the most recent batch
• Number of Vendors – the number of active vendors assigned to the property
• Number of Integrated Vendors – the number of vendors who have an interface, other than email, configured to send purchase orders. For example, vendors that can receive purchase orders using EDI, Trade Simple or cXML would all be considered as integrated

Accessing the Property Audit endpoint

To access the Property Audit endpoint you will need the user credentials (username and password) or a user that is assigned to a user group which has the View option for the Property API access right. This is set within the API Access sub-menu of the Property section within a user group.

Fig.1 - View access for 'Property API

Additionally, you will need the URL for the API endpoint which will be the URL you use to access Adaco with service/webapi/property/audit appended. 

For example, if your Adaco instance is at https://emea7.adaco.com/myhotelgroup then the URL for the Property Audit endpoint would be https://emea7.adaco.com/myhotelgroupservice/webapi/property/audit

Accessing the Property Audit Endpoint using Excel

• Open a new workbook in Excel
• On the Data tab, within the 'Get & Transform Data' section, select From Web
• In the dialogue box that appears, enter the URL for the endpoint (as above) and select OK

Fig.2 - Entering the endpoint URL

Excel will attempt to connect to the API. The first time you connect to the API you will be asked for authentication. 

• Select Basic from the left side and then enter your User name and Password
• Leave the drop-down with the default selection and select Connect

Fig.3 - Entering credentials

The Power Query Editor will open (see Fig.4).

• In the top left corner select To Table, and then OK

Fig.4 - Power Query Editor

• Next, expand the available fields by selecting the double-headed arrow to the right of the column header in Column1 (see Fig.5)
• Tick or untick fields as required
• Then select Close & Load

Fig.6 - Selecting fields

This will return the selected data to the Excel file from where the data can be filtered, sorted, or otherwise manipulated.  

• Finally, Save the workbook

When reopening the workbook, Excel will likely advise that the connection has been disabled.

• Select Enable Content to allow the connection to be re-established

To refresh the data from Adaco there are two options:

• Manually – in the Data section, select Refresh All
  or
• Automatically – in the Data section, select Queries & Connections
• Right-click on the query (from the righthand side) and select Properties
• Tick the option Refresh data when opening file
• Save the workbook

The data will refresh after a few seconds each time the file is opened.

Fig.7 - Options for refreshing data