As the most popular table calculation program worldwide, Microsoft Excel is at the center of many company processes. On this page, we will show you how to import data from the SISTRIX into Excel with the help of the API.
Unfortunately, Microsoft Excel does not offer a function to simply import external XML files for direct processing. Therefore, the integration is not as simple as with Google Tables – but it is still possible. The simplest method is the Excel plugin “SeoTools for Excel” by Niels Bosma. You can download it for free on his page.
The installation of the plugin is explained in detail in PDF file within the download. The short version: Pull the correct .xll file into the current Excel document and confirm the warning prompted by Excel. After that, “SeoTools” appears as additional menu item.
Additionally, starting with the 5.1 version of SeoTools for Excel Pro, there is now a direct SISTRIX integration. You can find all the details on http://seotoolsforexcel.com/sistrix/.
Import of API data via XPathOnUrl
The feature XPathOnUrl of the “SeoTool for Excel” plugins corresponds roughly with the Google Table feature ImportXML: An external XML file is read and the filtered data is available in Excel afterwards. For example, in order to read the current SISTRIX Visibility Index of a domain via the API and use it in Excel, a query with 3 parameters is necessary:
=XPathOnUrl("URL"; "XPath-term"; "value")
URL
The first field of the URL needs to be the exact URL of the SISTRIX API including your API key as well as all parameters. You can request or view your API key on this page. An overview of all API methods is available in the existing documentation. In order to retrieve the domain overview for the domain sistrix.de, for example, enter this URL:
https://api.sistrix.com/domain.sichtbarkeitsindex?api_key=[API_KEY]&domain=sistrix.com
XPath-term
The second field of the feature explains which data of the XML answer needs to be transferred into your Excel table. This description is given as an XPath definition. XPath may look a bit cryptic at first, but you will pick it up very quickly and the answers to the SISTRIX API only contain simple XML.
With an XPath tester like the one from Codebeautify.org, you can achieve results quickly. On that page, you can load the XML of the API with the button “Load URL” and test possible XPath variations at the bottom. You will then see the result on the right hand side.
In the example above, you will receive the domain overview of the page with this term:
"//sichtbarkeitsindex"
Value
The third parameter finally determines whether the data is meant to be read from an attribute of the XML instruction. In our case, the name of the attribute is “value”, so we will transfer this name.
The combined entry for the table field is therefore this function:
=XPathOnUrl("https://api.sistrix.com/domain.sichtbarkeitsindex?api_key=[API_KEY]&domain=sistrix.com"; "//sichtbarkeitsindex"; "value")
Receiving data
After the entry of this function, Excel will perform the API request for you and insert the current data from the SISTRIX API into your Excel table. All other API methods can be incorporated analogously to this process.
