Month-to-Month Stock Price Return: Google Sheets Formula

MONTH-TO-MONTH ( MTM) EXPLAINED
Month-to-Month calculation, candle chart illustration.
Month-to-Month calculation, candle chart illustration.

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)
EOMONTH() function example
EOMONTH() function example

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)-5

For 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)+1

To 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))
Return Last 5 Days of the month close prices
Return Last 5 Days of the month close prices

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 ''")
Return last day of the month close price as a single value
Return last day of the month close price as a single value

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))
Return Last 5 Days of the month close prices

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 ''")
Return end of the month close price as a single value google sheets  forumla
Return last day of the month close price as a single value.

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
month-to-month google sheets formula
Month-to-Month Google Sheets Formula

Leave a Reply