Month-to-Date (MTD) Stock Price Return: Google Sheets Formula

Month-to-Date (MTD) Stock Price Return: Google Sheets Formula

Last Updated on 24/05/2024 by Ndanileka

What is a Month-to-Date Return?

Month-to-date (MTD) return measures the percentage change in the stock price from the beginning of the current month to the current date. The change is calculated by subtracting the previous month’s close from the current price and then dividing the sum by the previous month’s close.

month to date calculation
Month-to-date calculation

Calculation:

MTD = ( ( Today’s Price – Last Month Close Price) / Last Month Close Price ) * 100
= (( 97.25 – 84.00 ) / 84.00) x 100
= 15.77 %

To simply our equation, we can express the above sum as,
= ( (97.25 / 84 ) -1) *100
= 15.77%

Month to Date Google Sheets Formula (Tutorial)

For the below example, Cell B1 = ” AMZN” ( Amazon.com, Inc. )

Step 1 Request Close Price ( Last Month)

Learn how the EOMONTH() function is utilized in this formula by reading this post.

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

Step 1.2 Return Close Price as a Single Value

Use the QUERY() function to select and sort the “close prices” by date in descending order, limit the request to one 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 ''")
Last Day of the Month Close Price as a Single Value
Last Day of the Month Close Price as a Single Value

Step 2 Request Today’s Current Price

=GOOGLEFINANCE(B1,"PRICE")
Current Price google sheets formula
Current Price

Step 3 Complete the MTD formula
MTD = (Step 2 / Step 1.2) -1 ) format as percentage

=      (GOOGLEFINANCE(B1,"PRICE")/QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-1)-5,EOMONTH(today(),-1)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1LABEL Col2 ''"))-1
Month-to-Date Google Sheets Formula
Month-to-Date Google Sheets Formula

Spreadsheet

Happy Trading!


Discover more from DEUS HARVEST

Subscribe to get the latest posts sent to your email.

Leave a Reply

Back To Top