If you invest in the stock market, you may be interested in obtaining market data for further analysis. Here I want to share how to use Alteryx to download stock market data from Yahoo Finance API.
There are several stock-market APIs available. If you decide to try a free one first, the Yahoo Finance API is a good choice. The official API of Yahoo Finance was closed in 2017, but there are still some unofficial APIs/libraries available. If you are a Python user, the python module yfinance may be a good choice. Since I use Alteryx to access the API, I decided to use the Yahoo Finance API provided by RapidAPI.
You need to register an account on RapidAPI to access it, and its basic plan allows you to have 500 free requests per month. The RapidAPI Yahoo Finance API has 4 endpoints. I won’t go into details about these endpoints. You can find more information here. In the following, I will demonstrate how to download SPY ETF price data for the past five years.
Parameters
After registering on the RapidAPI website and entering the endpoint page of Yahoo Finance API, you can see all endpoints in the left chunk. Since we need price data, we will use the “stock/v2/get-chart” endpoint. You can also find the header parameters, required parameters, and optional parameters for this endpoint in the middle chunk.
The header parameters consist of X-RapidAPI-Key and X-RapidAPI-Host, which basically tells RapidAPI who you are and which API you want to access. The required parameters are the essential parameters you need to pass in. In our example, we want the interval to be 1d, the symbol to be SPY, and the range to be 5y. For optional parameters, we set the region to the US. We put these parameters in the same row in Alteryx, as shown below. Note that the column headings in Alteryx must match the parameter names on the RapidAPI website to pass the values correctly.
Alteryx Download tool configuration
Now we need to use the Alteryx Download tool to download data from the API. In the Basic panel, select the endpoint url as the field. In the Headers panel, tick the header parameters, and in the Payload panel, tick the remaining parameters. Run the Alteryx workflow, and you can see that all data is downloaded in JSON format in the DownloadData column, as shown below.
Parse JSON data
Alteryx has a JSON Parse tool that can easily parse the JSON format. Drag the JSON Parse tool, connect with the Download tool, and rerun the workflow. You will see that all the data is now parsed into two columns, which contain meta-information, Unix timestamp, dividends, opening price, closing price, adjusted closing price, high and low prices.
Now you have the data! You can decide how to process it further based on what you want to do. Have fun!
Author: Binbin Chen
Binbin came to Australia and studied mechanical engineering at Monash University. After earning a bachelor's degree, he pursued a PhD degree and worked as a part-time teaching associate at Monash University during the period. When analysing data in research projects involving human eye movements, he discovered his enthusiasm for data. He was delighted to learn about Data School Down Under as it provides a unique opportunity to start a career as a data analyst through a thoughtful training program. Outside of work, Binbin enjoys watching historical and political videos. He is also an anime fan!