Working with Polygon.io Functions

If you are interested in real-time data for the US market, we have partnered with Polygon.io, a leading provider of real-time market data that counts Google and Robinhood as its customers. Alpha Vantage users will enjoy a lifetime 10% discount for their Polygon subscriptions. To unlock the discount, simply sign up for Polygon using your Alpha Vantage user email and enter the code ALPHAV on the subscription page.

Currently, our Excel add-on supports the following Polygon functions:

Setting the API code

First look for the AlphaVantage(Web) ribbon on Excel's ribbon bar.

When you click the Polygon.io icon it will open a task pane with links to documentation (including this document) and there will be a text field where you can enter your API key (highlighted).

Once you have entered your key and clicked Update you should be ready to go.

About Timestamps

Because many trades can occur even in 1 second Polygon.io has adopted the Unix timestamp format at nanosecond resolution. On the other hand Excel dates have a maximum resolution of milliseconds but only have a practical resolution of seconds. To allow Excel to interact with the Polygon.io API we have adopted some conventions and added 2 spreadsheet functions to help you.

The first convention is that at least within Excel we are treating timestamps as strings. In reality the timestamp represents the number of nanoseconds since 00:00:00 UTC on 1 January 1970 and is an integer. However, since the Office 365 api is in JavaScript there is not sufficient numerical precision to represent the timestamps accurately.

As part of our conventions we will allow timestamp strings to have a prefix of “TS” (case insensitive). The rationale is that in cases where you are entering a timestamp in a formula, this prevents Excel from converting it to a number. All functions that take a timestamp can accept either an Excel date (essentially a floating point number) or a string representing the timestamp.

The other convention we have adopted is that all dates and timestamps going in or out of Polygon.io functions are assumed to be in UTC. This release of the add-in does not convert to or from your local timezone!

The function AlphaVantage.UTCDateTimeNanoToUnixTimestamp converts an Excel datetime plus a nanosecond amount to a timestamp. This function also has an optional argument for you to set the prefix for the output to a value different from “TS” (including no prefix, i.e. “”).

The function AlphaVantage.UnixTimestampToUTCDateTimeNano converts the timestamp to an Excel datetime plus a nanosecond component. This function requires an input with either a prefix of “TS” or no prefix to the timestamp. It will also convert entire ranges of timestamps.

Polygon.io Functions

Polygon functions are integrated with Intellisense and begin with "AlphaVantage.PG."

An example of output for AlphaVantage.PG.HistoricTrades is (results truncated do to space constraints in this document)

The following shows the sip_timestamp fields converted to human readable form



Example Spreadsheet

PolygonExamples.xlsx