Alpha Vantage is an API that provides access to financial market data such as stock prices, exchange rates, and cryptocurrency data. It is used in Excel to retrieve this data and perform various financial analyses and calculations. In this post I will walk you through an example to import stock prices and financial data directly into Excel.
What are the benefits of using Alpha Vantage API in Excel?
- Real-time and historical data: Alpha Vantage provides real-time and historical data for stocks, forex, and cryptocurrency, allowing for more accurate and comprehensive analyses.
- Easy integration: Alpha Vantage API can be easily integrated into Excel using various add-ins or by using Excel’s built-in Web queries feature.
- Customizable data: Users can customize the data retrieved from Alpha Vantage API to fit their specific needs, such as choosing the timeframe or selecting specific data points.
- Free usage: Alpha Vantage API offers a free tier that allows users to make up to 500 requests per day, making it an affordable option for individuals and small businesses.
Alpha Vantage basic service is completely free. However, you will need to register for a Free API Key. (No credit card required)
If you need additional data than is available with a free API key, you can sign up for a paid plan with a Premium API Key.
Import Stock Prices and Financial Data Directly into Excel – Alpha Vantage API
This code uses the Alpha Vantage API to retrieve daily closing prices of NVAX for the last 90 days and calculates the volatility. Novavax is an example of a stock with extreme volatility that spiked during the COVID-19 epidemic fueled by speculation. Here’s a breakdown of what the code does:
- Set up the necessary variables (symbol, API key, start date, end date, and volatility).
- Build the API URL by combining the variables into a string.
- Call the API and retrieve the response as a JSON string.
- Use the VBA-JSON library (JSONConverter.bas) to parse the JSON response into an object.
- Loop through the daily prices for the last 90 days and calculate the daily percentage change.
- Sum the squares of the daily percentage changes to get the variance.
- Divide the variance by the number of days minus 1 and take the square root to get the volatility.
- Output the volatility to a cell in the worksheet.
Dim url As String
Dim symbol As String
Dim apiKey As String
Dim startDate As Date
Dim endDate As Date
Dim vol As Double
' Set variables
symbol = "NVAX" ' Symbol of the stock you want to pull data for
apiKey = "<your_api_key_here>" ' Your Alpha Vantage API key
startDate = Date - 90 ' Start date is 90 days ago
endDate = Date ' End date is today
vol = 0 ' Initialize volatility
' Build URL
url = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=" & symbol & "&apikey=" & apiKey
' Call API and retrieve data
Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
xmlhttp.Open "GET", url, False
Dim response As String
response = xmlhttp.responseText
' Parse JSON response
Dim json As Object
Set json = JsonConverter.ParseJson(response)
' Loop through daily prices
Dim prices As Object
Set prices = json("Time Series (Daily)")
Dim i As Integer
For i = 1 To 90
Dim currentDate As Date
currentDate = startDate + i - 1
Dim price As Double
price = prices(CStr(currentDate))("4. close")
vol = vol + ((price / prices(CStr(currentDate - 1))("4. close")) - 1) ^ 2
' Calculate volatility
vol = vol / 89
vol = vol ^ 0.5
' Output volatility to a cell
Range("A1").Value = "Volatility of NVAX for last 90 days:"
Range("B1").Value = vol
To use this code, you’ll need to replace “<your_api_key_here>” with your actual Alpha Vantage API key. You’ll also need to have the VBA-JSON library installed in your Excel workbook. You can find the JSONConverter.bas file and instructions for installation at
Trading models that were designed earlier using Yahoo! Finance API have been rewritten to retrieve prices from AlphaVantage. At this time only US stocks and ETFs can be retrieved from the MACD model. In addition, to make the JSON piece of code work, you must add the following references or libraries under the VBA code editor as per this screen grab. Under Developer->Visual Basic->Tools->References, add the highlighted objects.
I am giving you a free downloadable spreadsheet that uses Alpha Vantage API to pull daily closing stock prices and calculates volatility, a real bonus! You can go codeless through Excel’s Web Query feature.Download Import Stock Prices and Financial Data Directly into Excel through Alpha Vantage Sample