Welcome to Excel Jet Consult blog post. In this blog, we will learn how to get daily and weekly historical stocks data of IBM and Microsoft from Alpha Vantage API. Lets get started
Alpha Vantage API
The first thing we need to do is go to www.alphavantage.co to register and generate API key. Next, we are going to flip to the Alpha Vantage API documentation page: www.alphavantage.co/documentation/
Scroll down to Time Series Daily and copy the following url as seen in the screenshot below:
https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=IBM&apikey=SHH6PALI8TXJQRR5
IBM Daily Historical Stock Data
In the Get and Transform of Excel, click on From Web and paste the url in the box as seen below:
Click OK
In the Power Query editor, click on To table located at the top left corner
Note, the query has been renamed as IBM Daily Stocks and the Meta Data has been filtered out in the first column: Name
Click on the expandable icon and uncheck use original column name as prefix
Click OK. The list of the date are visible and it is horizontally displayed.
To transform the data vertically, right-click on the Name column and select Unpivot Other Columns as seen below
To display the open, high, low, close and volume of stocks data, click on the expandable icon in the Value column and click OK
In the screenshot below, we have the time series date with the open, high, low, close and volume traded
Microsoft Daily Historical Stock Data
To get the data for Microsoft, duplicate the IBM Daily Stocks and Rename the duplicate as Microsoft Daily Stocks
Click on the Source located in the Applied Steps of the Microsoft Daily Stocks
In the formula bar, carefully replace the IBM with MSFT
Click Enter
Go to the last step below which is Changed Type. There we go, we have the daily historical stock data of Microsoft as seen in the screenshot below
IBM Weekly Historical Stock Data
To fetch the weekly IBM historical Stock Data, duplicate the IBM Daily Stocks Data query and rename as IBM Weekly Stocks
Click on the Source and carefully replace the daily with weekly in the formula bar
Click Enter
Click on the Filtered Row of the Applied Steps.
Click on the filter icon of the Name column, uncheck the Time Series (Daily) and check the Weekly Time Series
Click OK
Go to the last step: Changed Type. We have the weekly stocks data of IBM as seen in the screenshot below
Microsoft Weekly Historical Stock Data
To fetch the weekly Microsoft historical Stock Data, duplicate the IBM Weekly Stocks Data query and rename as Microsoft Weekly Stocks
Click on the Source and carefully replace the daily with weekly in the formula bar
Click Enter
Click on the Filtered Row of the Applied Steps.
Click on the filter icon of the Name column, uncheck the Time Series (Daily) and check the Weekly Time Series
Click OK
Go to the last step: Changed Type. We have the weekly stocks data of Microsoft as seen in the screenshot below
