Start Here: Download NGI Excel Add-in
Requirements
The Excel Add-in requires the following:
- Windows 7 or greater
- Excel 2013 or Office 365
- Ability to install Excel Add-ins in Excel (some users may be restricted due to their company’s IT policies)
- An NGI Excel access token (if you don’t have an access token and would like one, please reach out to ngidata@naturalgasintel.com or your account representative)
Setup
To begin using the Excel Add-in, follow our step-by-step directions to pull your first Datafeed. If you encounter a ‘Microsoft Excel Security Notice’, please click “Enable”. Place your Excel access token in a cell somewhere in the workbook you’re building (our examples below use cell A1) and reference it when using the below formulas.
Parameter Definitions
excel_access_token: You just plug in your Excel token here. The easiest way to do this is to drop it into a cell and then reference it for all your formulas
series: This where you specify whether you want Daily/Weekly/Bidweek etc data. The available options are:
- Daily: “daily”
- Weekly: “weekly”
- Bidweek: “bidweek”
- Shale: “shale”
- Mexico Daily: “mexico-daily”
- Mexico Bidweek: “mexico-bidweek”
- MidDay: “midday”
- Forward Look: “forward” (use with NGIForwardHistory() only, returns both fixed and basis prices), “forward-fixed” (use with NGIDatafeed() only, returns fixed price datafeed, “forward-basis” (use with NGIDatafeed() only, returns basis price datafeed
- Mexico Forward: “mexico-forward” (use with NGIForwardHistory() only, returns both MX/Gj and USD/MMBtu, “mexico-forward-usd” (use with NGIDatafeed() only, returns USD/MMBtu datafeed, “mexico-forward-mxp” (use with NGIDatafeed() only, returns MXP/Gj datafeed
start_date: This is the start date for historical data functions. It will take a cell reference if you just point it at a cell with the date in it and should work with any format; however, if you type this date into the function manually, you need to encapsulate it in double quotes and use YYYY-MM-DD format.
end_date: This is the end date for historical data functions. It will take a cell reference if you just point it at a cell with the date in it and should work with any format; however, if you type this date into the function manually, you need to encapsulate it in double quotes and use YYYY-MM-DD format.
pointcode: Pointcode of the location you’re trying to query with the historical data functions. If you don’t know the pointcode, you can get a list of available pointcodes for the publication using the NGILocations() function. Note: Capitalization and spelling matter. Misspelled/lowercase pointcodes will result in an error.
issue_date: This is the issue date of the datafeed you’re trying to access with the NGIDatafeed() function. It will take a cell reference if you just point it at a cell with the date in it and should work with any format; however, if you type this date into the function manually, you need to encapsulate it in double quotes and use YYYY-MM-DD format.
Historical Data (everything except forwards)
To retrieve time-series historical data for all non-forward price series, use the NGIHistory function. If you don’t know the pointcode for a location, you can get a list of locations and their corresponding pointcodes using the NGILocations function described below.
=NGIHistory(excel_access_token, series, start_date, end_date, pointcode)
Example: =NGIHistory(A1, “daily”, “2021-03-15”, “2022-03-30”, “WTXWAHA”)
Historical Forward Data
To retrieve time-series historical data for forward series, use the NGIForwardHistory function. The forward series historical data is not available under the NGIHistory function because requesting these data requires an additional parameter, contract, which is not applicable for other, non-forward series.
If you don’t know the pointcode for a location, you can get a list of locations and their corresponding pointcodes using the NGILocations function described below.
=NGIForwardHistory(excel_access_token, series, start_date, end_date, pointcode, contract)
Example: =NGIForwardHistory(A1, “forward”, “2022-03-15”, “2022-03-30”, “WTXWAHA”, “2022-04-01”)
Datafeeds (All locations/prices for a single day/week/month)
To retrieve the full contents of a datafeed, use the NGIDatafeed function. Issue date is the day that a datafeed was published; not the day the data were traded. Data must be referenced by issue date because not all series have a single trade date (ex. Weekly).
=NGIDatafeed(excel_access_token, series, issue_date)
Example: =NGIDatafeed(A1, “daily”, “2022-03-21”)
Lists of Locations/Pointcodes
To retrieve a list of locations and their corresponding pointcodes, use the NGILocations function.
=NGILocations(excel_access_token, series)
Example: =NGILocations(A1, “daily”)