Last Updated on 08/04/2024 by Ndanileka
WHAT IS A WEEK-TO-DATE RETURN
Stock price Week-to-Date (WTD) return is a measure of the change in a stock’s price from the beginning of the current trading week to the current date. The WTD return is calculated by subtracting the stock’s previous week’s closing price from its current price and then dividing the result by the previous week’s closing price. The result is then expressed as a percentage, representing the stock’s WTD return.
For example, Chevron had a previous week’s close of 169.45, and today’s current price is 170.00
WTD will be calculated as follows;
= ((Current Date – Previous Week’s Close) / Previous Week Close ) x 100
= ((169.45 – 170.00) /169.45 ) x 100
= 0.32%
To simply our equation, the above can be expressed as;
= (Current Date /Previous Week’s Close) – 1
= (( 170 / 169.45 ) -1 ) x 100
= 0.32%
RETRIEVING THE PREVIOUS WEEK’S END DATE
In this section, we will learn how to retrieve the end date of the previous week. This date will be used to get the close price of the previous week for our WTD formula.
Convert Current Date into Number
When automating calculations in Google Sheets, it is important to ensure that the formula always retrieves the last available closing price from the previous week, even if a full trading week is unavailable due to holidays or other factors.
To achieve this, we need to convert the current day of the week into a number that represents the specific day and can be used for date calculations.
We will use the following functions to convert the day into a number:
- WEEKDAY(): This function returns a number that represents the day of the week for a given date.
- TODAY(): This function returns the current date.
For example, today’s date is “Wednesday – 08/02/2023”.
By applying the WEEKDAY() function to TODAY(), the formula will return 4, indicating that Wednesday is the fourth day of the week, considering Sunday as the first day.
=WEEKDAY(TODAY())
If you want to learn more about the WEEKDAY() function, infoinspired.com has a detailed article with multiple use case example
Retrieve the Previous Week End Date
To calculate the end date, we start with today’s date (TODAY()) and subtract the number of days representing the current day of the week (WEEKDAY(TODAY())). This ensures that the resulting date is the end of the previous week.”
=TODAY()-WEEKDAY(TODAY())
The default end day in the WEEKDAY() function is Saturday, which is represented by the value 6.
WEEK-TO-DATE GOOGLE SHEETS FORMULA
With the formula in place to retrieve the previous week’s Saturday date, we can now use it to request the close prices from that specific week. By incorporating this formula into our data retrieval process, we ensure that we are consistently fetching the close prices from the previous week.
For the example below , Cell B1 = ” CVX” ( Chevron Corporation )
Step 1. Request the previous week’s close price.
Step 1.1 Request close prices from the previous 5 days
To accurately capture the previous week’s close price, we adjust our end date formula by subtracting an additional 5 days. This ensures that we retrieve the prices from Monday to Friday, accounting for any potential non-trading days.
For example, if there was no trading on Friday, we would use the price from Thursday. Similarly, if there was no trading on Thursday, we would use the price from Wednesday, and so on. By incorporating this adjustment, we account for any trading day gaps and ensure that the close price is returned.
=GOOGLEFINANCE(B1,"close",(TODAY()-WEEKDAY(TODAY()))-5,(TODAY()-WEEKDAY(TODAY())))
Step 1.2. Return The Most Recent Close price
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",(TODAY()-WEEKDAY(TODAY()))-5,(TODAY()-WEEKDAY(TODAY()))),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1 LABEL Col2 ''")
Step 2. Request Today’s Current Price
=GOOGLEFINANCE(B1,"price")
Step 3 Complete the WTD formula
WTD = (Step 2 / Step 1.2) -1 ) format as percentage
=(GOOGLEFINANCE(B1,"price")/QUERY(GOOGLEFINANCE(B1,"close",(TODAY()-WEEKDAY(TODAY()))-5,(TODAY()-WEEKDAY(TODAY()))),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1 LABEL Col2 ''"))-1
