Last Updated on 08/04/2024 by Ndanileka
MONTH-TO-MONTH ( MTM) EXPLAINED
The month-to-month return compares the price of a stock at the end of one month to its price at the end of the previous month. It measures the percentage change in the stock’s price over this period, indicating its performance between two consecutive months.
As an example, let’s consider the stock IBM. The closing price for November was $148.90, and for December, it was $140.89. To calculate the monthly return for December, we use the following formula:
Monthly Return = ((December Close / November Close) – 1) * 100
Applying the formula:
Monthly Return = ((140.89 / 148.90) – 1) * 100
Monthly Return = -5.38%
The monthly return for December would be -5.38%, indicating a decline in the stock price compared to the previous month.
GOOGLE SHEETS MONTH-END DATE PARSING
In this section, we will discuss how to request the end-of-month date in Google Sheets and outline the steps involved in structuring your formula to retrieve the close price using the EOMONTH() function.
LAST DAY OF THE MONTH FUNCTION
EOMONTH() is a useful function in Google Sheets that allows you to obtain the last day of the month for a given date. For instance, if you want to retrieve the last day of the month preceding the previous month, you can make the following request:
=EOMONTH(TODAY(),-2)
DATE REQUEST PARSING
When automating historical price requests, it is crucial to structure the formula in a way that considers holidays, weekends, and or any other market close date to ensure that the correct period is queried.
In this context, the month-end date can fall on a workday, a holiday, or a weekend. Therefore, our goal with parsing is not only to request the last day of the month but also to include additional days before the month-end in case the last day coincides with a market close date.
For the start_date, we add “- 5”
Adding five buffer days to the last day.
EOMONTH(today(),-2)-5To ensure that the “last day” is included in the retrieved data, it is necessary to add “+1” to the end_date when using the GOOGLEFINANCE() function. This adjustment compensates for the fact that the function retrieves data up to the day before the specified end_date. Therefore, by adding “+1” to the end_date, we ensure that the desired end_date is included in the data retrieval.
EOMONTH(today(),-2)+1MONTH-TO-MONTH GOOGLE SHEETS FORMULA
For below example cell B1 = “IBM” ( International Business Machines Corporation)
Step 1. Request the Last Traded Price for the Month Before Last Month.
Step 1.1.
Request close prices from the previous 5 Days.
=QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-2)-5,EOMONTH(today(),-2)+1))
Step 1.2.
Return the last traded 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 the Last Traded Price from Last Month.
Step 2.1.
Request close prices from the previous 5 Days.
=QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-1)-5,EOMONTH(today(),-1)+1))
Step 2.2
Return the last traded 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
