Last Updated on 23/02/2023 by DEUS Harvest
In an earlier article, we covered the fundamentals of using the Google Finance function. If you are new to Google Sheets and the Googlefinance function, review our initial post to establish a foundation, as this post will concentrate on obtaining historical data.
HISTORICAL DATA SYNTAX
To retrieve historical stock price data, you will use the “historical” option of the GOOGLEFINANCE() function. The syntax of the function is as follows:
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval])
- “ticker” refers to the stock symbol of the security you want to retrieve data for.
- “attribute” refers to the specific financial data you want to retrieve.
When retrieving historical data, the available attributes are “open,” “high,” “low,” “close,” “volume,” and “all.” Opting for the “all” attribute will retrieve all available data points, encompassing open, high, low, close, and volume information.”
- “start_date” and “end_date” refer to the date range you want to retrieve data for. It is important to input these dates in the format specified by your Google Sheets locale settings. You can access these settings by going to Sheets > File > Settings > General tab. It is worth noting that the “end_date” is excluded from the data retrieved. So, if you need information for that particular day, you should either add a “+1” to the end date or request one extra day of data.
- “interval” [ OPTIONAL ] refers to the frequency of the data, either “DAILY” or “WEEKLY.” By default, the interval is set to daily.
It’s important to pay attention to the format of the retrieved dataset, including the order of labels and the way the date column is organized in ascending order. Doing so lets you determine which data is stored in each column, making it easier to parse complex formulas.
=GOOGLEFINANCE("ticker", "attribute", "start_date", "end_date")
AUTOMATE HISTORICAL REQUEST
To update your stock price data automatically, you can use the TODAY() function, which returns the current date, instead of manually entering the date.
In our example below, we request all historical price data from the previous ten days.
When requesting historical price data, remember that the formula takes weekends and non-trading days into account, which means that the resulting data may consist of fewer than the number of days requested. To ensure you have enough data to work with, it’s advisable to request more days than you require and subsequently “clean” the data using the Query() function.
The Google QUERY() function allows users to filter, sort, and manipulate data within a Google Sheets spreadsheet. With the Query function, you can extract and display specific information from a large dataset based on custom criteria. The function works by taking a range of cells and applying a SQL-like query language to filter and sort the data as desired. For example, the Query function can retrieve data from multiple sheets, filter by date range, or sort data by a specific column and more.
As a result, users can easily analyze data in Google Sheets using the Query function.
In our case, we will be utilizing this function extensively to convert our data into single cell formulas.
Query function Examples:
Return the Highest Traded Price from the Previous 10 Trading Days.
Step 1 – Request Dataset
Step 2 – Clean and Format Dataset Using the Query Function.
The query reads as; ” Select Col3 (“high”) sort in date desceding order (Col1) and limit the request to 10 values. “
=QUERY(GOOGLEFINANCE("NYSE:JPM","all",TODAY()-20 ,TODAY()),"SELECT Col3 ORDER BY Col1 DESC LIMIT 10")
Step 3 – Complete the Highest Traded Price Formula
We then wrap the query from Step 2 with the MAX() function to complete the formula
=MAX(QUERY(GOOGLEFINANCE("NYSE:JPM","all",TODAY()-20 ,TODAY()),"SELECT Col3 ORDER BY Col1 DESC LIMIT 10"))
DATE RANGE PARSING.
Ensuring accuracy in date range parsing is crucial when working with stock price data, as even minor errors can result in formulas displaying incorrect calculations, potentially leading to erroneous analyses. Therefore, it’s essential to lock in the correct date range when automating data retrieval and account for any errors that could cause the range to move outside the desired period. Common events that can cause errors when retrieving stock price data in Google Sheets include weekends, non-trading days, monthly and yearly periods.
In upcoming articles, I will link below any date parsing issues I encounter to the specific section of the article that addresses the problem to help readers resolve similar issues more efficiently.
In conclusion, requesting historical data in Google Sheets is a valuable tool for anyone looking to analyze and visualize financial data. Once you understand the fundamentals, such as the attributes, date parsing, and data structure, the process becomes much easier. By formulating your equations and utilizing automated tools like the Query function, you can streamline your data analysis and achieve valuable insights in your trading journey.