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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
Sub GetStockPrices() 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 xmlhttp.send 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 Next i ' 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 End Sub |
Usage Instructions
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
https://github.com/VBA-tools/VBA-JSON
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