Skip to main content

Articles in this section

Connect Your Data to Microsoft Excel

You can connect one or more of your Helm CONNECT reports to Microsoft Excel. Once you've established the connection, you can refresh Excel to reload the latest data from Helm CONNECT.

The sections below describe two ways to connect your data to Excel: using HTTP headers or using Web API. Use the method that works best for you; however, if you intend to share your Excel report, keep in mind that the HTTP headers method stores your API key in the report.

If you have any questions or aren't sure which method to use, please reach out to your account manager.

Prerequisites

Before you connect your report to Excel, please review the following prerequisites:

  • If you're using Excel 2010 or 2013, you must first install the Power Query add-on. Power Query is integrated with Excel 2016. For more information, please refer to your Microsoft documentation.
  • Each report has a unique CSV connection string. You must retrieve a separate connection string for each report you want to connect. For more about retrieving CSV connection strings, see Connect Your Data to Other Tools.
  • You need your Helm CONNECT API key. For more about managing API keys, see Connect Your Data to Other Tools.

Use HTTP Headers to Connect Your Data

To connect your data using HTTP headers

  1. To display the From Web window, do one of the following:
    • In Excel 2016, click the Data tab, then click From Web.
    • In Excel 2010 or 2013, click the Power Query tab, then click From Web.
  2. Select Advanced, then enter the following information:
    1. Under URL parts, in the first field, enter the CSV connection string for your Helm CONNECT report.
    2. Under HTTP request header parameters (optional), in the field on the left, type Api-Key.
    3. Under HTTP request header parameters (optional), in the field on the right, enter your Helm CONNECT API key.

  3. Click Done to display a preview of the Helm CONNECT data you're connecting to your Excel report.
  4. Click Load to load your data.
  5. (Optional) To connect more Helm CONNECT data to this Excel report, do one of the following:
    • In Excel 2016, on the Data tab, select New Query > From Other Sources > Blank Query. In the Advanced Editor, repeat the steps above to enter a new query, using the unique CSV connection string for the new Helm CONNECT report.
    • In Excel 2010 or 2013, on the Power Query tab, select From Other Sources > Blank Query. In the Advanced Editor, repeat the steps above to enter a new query, using the unique CSV connection string for the new Helm CONNECT report.

Use Web API to Connect Your Data

To connect your data using Web API

  1. To display the Query Editor, do one of the following:
    • In Excel 2016, click the Data tab, then select New Query > From Other Sources > Blank Query.
    • In Excel 2010 or 2013, click the Power Query tab, then select From Other Sources > Blank Query.
  2. Click Advanced Editor.
  3. In the Advanced Editor, enter the following query:

     

    let

    Source = Csv.Document(Web.Contents("Put Helm CONNECT CSV Connection String Here", [ApiKeyName= "apikey"]),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),

    #"Promoted Headers" = Table.PromoteHeaders(Source)

    in

    #"Promoted Headers"

     

  4. In the query, replace Put Helm CONNECT CSV Connection String Here with the CSV connection string for your Helm CONNECT report.
  5. Click Done to display a preview of the Helm CONNECT data you're connecting to your Excel report.
  6. If this is your first time connecting Helm CONNECT data to Excel, you'll see a warning that your credentials are invalid. To provide your Helm CONNECT credentials in the form of an API key, follow these steps:
    1. In the warning message, click Edit Credentials to display the Access Web Content window.
    2. Click Web API.
    3. In the Key field, enter your Helm CONNECT API key.
    4. Click Connect.
  7. (Optional) To connect more Helm CONNECT data to this Excel report, do one of the following:
    • In Excel 2016, on the Data tab, select New Query > From Other Sources > Blank Query. In the Advanced Editor, repeat the steps above to enter a new query, using the unique CSV connection string for the new Helm CONNECT report.
    • In Excel 2010 or 2013, on the Power Query tab, select From Other Sources > Blank Query. In the Advanced Editor, repeat the steps above to enter a new query, using the unique CSV connection string for the new Helm CONNECT report.

Related articles

\