Blog post banner

Realtime stock dashboard in Excel with the Alpha Vantage API

April 16, 2018

Alpha Vantage is an API that provides realtime and historical stock and forex data, digital/crypto currency data and over 50 technical indicators. It supports intraday, daily, weekly, and monthly quotes and technical analysis with chart-ready time series. What’s more, it’s 100% free with unlimited API calls!

What is Gridarrow?

Gridarrow is the missing server for Excel. It connects just about any data source to Excel with simple Python scripts.
Why is it awesome?
  • It's real-time, so as soon as the data is updated in the Gridarrow App it's updated in Excel, no need to poll or refresh.
  • It's multi-user so your colleagues can connect to the same data at the same time.
  • It simplifies your spreadsheets by moving data retrieval and pre-processing into Python.
Request Demo

Happy dashboarding!

With a realtime data feed in Excel, it’s possible to create some great looking dashboards without ever opening the VBA editor! Check out this example we made for our homepage.

Realtime stock dashboard in Excel with the Alpha Vantage API

Catalog

This app is part of the Gridarrow catalog, so using it is just a matter of adding it to your workspace and configuring the Alpha Vantage API_KEY and the symbols you want to stream to Excel.

50 lines of Python code

Check out the Python code below to see how easily you can feed data from Alpha Vantage to Excel.

There’s a nice community maintained Python client available for the API, but for this example we’re just using requests to grab the JSON data Alpha Vantage provide. We’re making use of the on_schedule annotation to call the API every minute between 8am and 6pm ET Mon-Fri. We use the concurrent.futures module to call the API in parallel.

But before you jump into it, remember to:

 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829303132333435363738394041424344454647484950
import os
from concurrent import futures
from datetime import datetime
import requests
from gridarrow import Arrow

arrow = Arrow()

INTERVAL = os.environ.get('INTERVAL', '1min')
ALPHA_VANTAGE = f"https://www.alphavantage.co/query?apikey={os.environ['API_KEY']}"
TIMESERIES_URL = ALPHA_VANTAGE + f"&function=TIME_SERIES_INTRADAY&interval={INTERVAL}" + "&symbol={}"

@arrow.initialize
def init():
    context = {symbol: arrow.grid(symbol, rows=100, columns=['timestamp', 'open', 'high',
                                                             'low', 'close', 'volume'])
               for symbol in os.environ['SYMBOLS'].split(",")}
    return context

@arrow.on_schedule("* 8-18 * * MON-FRI", timezone='US/Eastern', initialize=True)
def on_schedule(context):
    # Use ThreadPoolExecutor to make concurrent requests to the API
    with futures.ThreadPoolExecutor(max_workers=10) as executor:
        fut_to_symbol = {executor.submit(load_url, TIMESERIES_URL.format(symbol)): symbol
                         for symbol in os.environ['SYMBOLS'].split(",")}
        for future in futures.as_completed(fut_to_symbol):
            symbol = fut_to_symbol[future]
            try:
                data = future.result()
                if 'Error Message' in data or not any(data.keys()):
                    arrow.log.error(f"Data was invalid: {data}")
                else:
                    timeseries = data[f"Time Series ({INTERVAL})"]
                    grid = context[symbol]
                    update_grid(grid, timeseries)
            except requests.RequestException as exc:
                arrow.log.error(f"{symbol} generated an exception: {exc}")

def update_grid(grid, timeseries):
    for i, d in enumerate(timeseries.items()):
        timestamp, ohlc = d
        grid[i, grid.columns] = [datetime.strptime(timestamp, "%Y-%m-%d %H:%M:%S"),
                                 float(ohlc["1. open"]), float(ohlc["2. high"]),
                                 float(ohlc["3. low"]), float(ohlc["4. close"]),
                                 float(ohlc["5. volume"])]

def load_url(url):
    r = requests.get(url, timeout=60)
    r.raise_for_status()
    return r.json()