last 6 months, last 12 months, last 24 months, etc.)
for the list of symbols that appear in column B.
In addition, we want a rolling time period, that stays in sync with the current date.
Basic example
STOCKHISTORY retrieves historical stock price information for a given symbol and date range.
STOCKHISTORY takes up to 11arguments, most of which are optional.
The options are daily (0), weekly (1), or monthly (2).
In this case, we want a monthly interval, so we provide 2.
Dynamic dates
In the basic example above, the dates are hardcoded as text values.
We can do this by adding theTODAY functionand theEDATE functionto the formula.
If the date is January 8, 2021, EDATE returns the date August 8, 2021.
To calculate theend_date, we use the TODAY() function by itself.
Note: There is no need to supply a first of month or last of month date to STOCKHISTORY.
For complete months in the past, this will be the last trading day of the month.
For months not yet complete, STOCKHISTORY will return the latest close price available.
Along with this change, we also need to remove the date field and suppress the header.
The header
The last piece of the puzzle is the header.
Note that we don’t want a header that says “Date” or “Price”.
As a workaround, we can adjust the formula to output both date and close price.
Note whenintervalis set to monthly (2), STOCKHISTORY will always show “first of month” dates.
However, you’ve got the option to apply a customdate formatto display the dates as you like.
In the example shown, the date format used is “mmm-yyyy”.
The primary purpose of STOCKHISTORY is to get the history of a financial instrument over time.
The TODAY function takes no arguments.
you’ve got the option to format the value returned by TODAY with a datenumber format.
it’s possible for you to use INDEX to retrieve individual values, or entire rows and columns.
The MATCH function is often used together with INDEX to provide row and column numbers….
