Last Updated on 23/05/2024 by Ndanileka
What is a Month-To-Month Stock Price Return
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.
Month-to-Month Google Sheets Formula (Tutorial)
Return the last day of the month
Before we can get the “Close Price” of the previous month, we will need to find the month’s end date first.
For this, we will use the EOMONTH() function, which retrieves the month’s end date for a given date. For instance, if you need to retrieve the end date of the month preceding the previous month, the date can be obtained with the following request:
=EOMONTH(TODAY(),-2)
Return a Date Range
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)+1Data Retrieval and Calculation steps
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
Spreadsheet
Discover more from DEUS HARVEST
Subscribe to get the latest posts sent to your email.
