Charts with the Alpha Vantage Excel 365 Add-in

One of the cool features in the Office 365 API is the ability to set up streaming functions in Excel that update themselves at a predetermined interval. Today we are going to demonstrate how to set up a “live” updating chart for a forex series. Users should be aware that unless you have a premium membership with Alpha Vantage, this example will burn through your data allowance quickly. The intra-day series are updated at the same frequency as the series. For example, a 1-minute series will be updated every minute.

Alpha Vantage does not offer tick by tick data. However, we do offer intra-day forex series that update every minute. That’s more than enough data to create a chart that updates during the day. We’re assuming here that you already have the Alpha Vantage Market Data Add-in installed and have entered your API key.

The first thing we note are the numbers in the Date column. No worries! This is just because dates in Excel are actually floating numbers and we have to format them to look like we want. Excel has a few standard date formats you can choose.

If you want, you can customize your format any way you like. For longer series of intraday forex data, I like seeing a datetime. There is a pre-defined format under “Custom” in the Format dialog that gives me the date time to the minute.

However, for the purposes of this example where we are only charting the current day’s data, I can format the Date column as a time of day.

When using Alpha Vantage routines with an output size of “compact” the series is limited to the most recent 100 points. This is fine for many charting applications but sometimes we want to view fewer than 100 points.

We can then apply a filter to limit data to the last 30 data points using the FILTER function.

Here we are saving the FILTER result to a separate location in the worksheet. In principle you could nest a query formula within the FILTER formula (replace E2# with the appropriate Alpha Vantage formula). However, as we’ll see later, the flexibility will help when trying to use Excel’s financial chart types.

At this moment we’ll take a short aside to explain the E2# notation. There is a formula returning a dynamic array to cell E2. The notation E2# says take the entire output range of that formula. Pretty cool, right?

So, in our spreadsheet the filtered output formula is in L5. So just select that individual cell and choose to insert an area chart from the Excel Insert tab.

Let’s start by selecting the specific series to chart. Right click on the chart and choose “Select Data…”

Then you can choose the series you want in this chart.

I only want to look at the price at the end of each interval, so I uncheck all the series except close.

Let’s adjust the time axis. Right click in the area of the times and select “Format Axis…”

We select “Categories in reverse order.”

Those of you who have used Excel’s financial chart types know that the input data must be in a specific format. For example, the first column header must be the name of the entry (instead of “Date” which is returned by the time series query). Since our data is almost in the right format, we only need to modify the FILTER function so that the header row is not reproduced. This way we can enter our own values for the header values.

Remember from previous formulas that the cell N2 contains the number of points to be charted. Now we select all the columns except Volume including the customized headers. While this region is selected, we then choose from the Insert tab to make an Open/High/Low/Close chart. As with the previous example you will have to choose to reverse the x axis so that later times are on the right.

Both of the charts we’ve built will update every minute.

Look what you can build in a few minutes with Alpha Vantage and Excel!

Questions? Comments? Feel free to leave us a message below! You can also reach out to us for spreadsheet-specific topics here.