MONTH-TO-MONTH ( MTM) EXPLAINED
The month-to-month return compares the value of a stock at the end of one month to the value of the stock at the end of the previous month. For example, the November close for IBM was 148.90, and the December close was 140.89. Therefore the monthly return for December would be calculated as,
= ((Last Month Close / Month Before Last Month Close) -1) x 100
= ((December Close / November Close) -1) x 100
= (140.89 / 148.90) -1 ) x 100
= -5.38%
GOOGLE SHEETS DATE REQUESTS
Last Day Of The Month Function
EOMONTH() is a function in Google Sheets that returns the last day of the month for a given date.
Example: The last day of the month before last month can be requested as follows;
=EOMONTH(TODAY(),-2)
Date Request Parsing
When automating historical price requests, we need to structure the formula in a way that will account for holidays and weekends and to ensure that the correct period is always “queried.”
= GOOGLEFINANCE("ticker", "attribute", "start_date", "end_date")“start_date”
EOMONTH(today(),-2)-5For the start_date, we add “- 5”.
This accounts for a “last day” that falls on a weekend and, in case of a holiday, on Friday/Thursday.
“end_date”
EOMONTH(today(),-2)+1To ensure that the “last day” is included in the retrieved data, we add “+1” to the end_date as GOOGLEFINANCE() function retrieves a day less from the end_date.
MONTH-TO-MONTH GOOGLE SHEETS FORMULA
In the example below cell B1 = “IBM” ( International Business Machines Corporation)
Step 1 Request Close Price ( Month Before Last Month)
Step 1.1 Request The Last 5 Days
=QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-2)-5,EOMONTH(today(),-2)+1))
Step 1.2 Return Close Price as a Single Value.
When then use the QUERY() function to select and order the “close prices” in date descending, limit our request to a single value and remove the column label.
=QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-2)-5,EOMONTH(today(),-2)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1 LABEL Col2 ''")
Step 2 Request Close Price ( Last Month)
Step 2.1 Request The Last 5 Days
=QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-1)-5,EOMONTH(today(),-1)+1))
Step 2.2 Return Close Price as a Single Value
We use the Query() function as in step 1.2, to select and order the “close prices” in date descending, limit our request to a single value and remove the column label.
=QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-1)-5,EOMONTH(today(),-1)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1LABEL Col2 ''")
Step 3 Complete the Month-to-Month Formula
MTM = (Step 2.2 /Step 1.1) -1 format as a percentage.
=(QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-1)-5,EOMONTH(today(),-1)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1 LABEL Col2 ''") /QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-2)-5,EOMONTH(today(),-2)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1LABEL Col2 ''"))-1