MSFT 365 Price Change For X Number Of Days

[Data123]

I recently purchased MSFT 365 for access to Excel for stock data and was super impressed with the program and data! Then I tried to create a column header for, "Price % Change X Days" and realized STOCKHISTORY counts weekends and holidays as data days. I read about the WORKDAY solution, but this would not solve the holiday issue. Is there a way to create a formula based on days with data only OR X number of days with data?

Lastly, does one have to create a formula that shows a table with dates and prices or can a formula just show the percentage result for each symbol?

 

[awoohaw]

Look at the WORKDAY.INTL() function. There is an optional argument where you can identify a range of dates to be excluded from the counts.

WORKDAY.INTL function - Microsoft Support

This article describes the formula syntax and usage of the WORKDAY.INTL function, which returns the serial number of the date before or after a specified number of workdays with custom weekend parameters.

support.microsoft.com

 

[MARK858]

There is an optional argument where you can identify a range of dates to be excluded from the counts

As there is in Workday.
Workday.Intl has the advantage of being able to treat days as the weekend other (or in addition to) Saturday and Sunday

 

[Data123]

Thanks, awooha & MARK858! I did read over the link above and Worday.Intl looks helpful. Do I have to use specific dates or can I use TODAY(0) with it? I need it to be a floating date so when I review the spreadsheet next week, month, or year the price change % 5 days will just adjust to the week.

Also, for holidays do I need to type in the exact dates for each holiday? If so, is there a way to create a formula based on days with data only OR X number of days with data?

 

[MARK858]

You can use

=TODAY()

(which is an exact date, it just changes as the date changes) or individual input dates

 

[awoohaw]

if you want to look for a date 1 workday away from today, excluding Presidents Day, and excluding Fri, Sat, Sun (4 Day Workweek). the function would be:

=WORKDAY.INTL(TODAY(),1,"0000111",B4:B4)

Book1
	A	B	C
1
2
3		Holiday:
4		2024-02-19
5
6		Today:	2024-02-15
7
8		1 WorkDay.Intl:	2024-02-20
Sheet1

Cell Formulas
Range	Formula
C6	C6	=TODAY()
C8	C8	=WORKDAY.INTL(TODAY(),1,"0000111",B4:B4)

If you want to share more information about how you are applying the weekly percentage change the forum could provide more assistance. Please post a mini worksheet of you data using the xl2bb add in (link below) or a table.

 

[Data123]

Thank you both again! I looked at the link to post my worksheet, but I don't understand how to do it. See attached pic. So column A is stock symbols and choosing stock data for them. Also, column B is "Price Change % 5 Days". It's just simply subtracting the closing price 5 days ago to today's current price. Then dividing the difference by the price 5 days ago.

I received this formula for the column B "=LET(sh,STOCKHISTORY(A2,TODAY()-5,TODAY(),0,0,1),TAKE(sh,-1)/TAKE(sh,1)-1)"

It works great, except the 5th day is on a weekend day so it only calculates 3 days back. Can your formula be added to this one above?

 

[awoohaw]

What you need is to insert this where the Today()-5 is:

=NETWORKDAY.INTL(Today(),-5,"0000011",HolidayList)

So making it this:

=LET(sh,STOCKHISTORY(A2,
NETWORKDAY.INTL(Today(),-5,"0000011",HolidayList)
,TODAY(),0,0,1),TAKE(sh,-1)/TAKE(sh,1)-1)

But you need to make that holiday list range/table

(I may have my 1 and 0 backwards in the function. )

 

[Data123]

What you need is to insert this where the Today()-5 is:

=NETWORKDAY.INTL(Today(),-5,"0000011",HolidayList)

So making it this:

=LET(sh,STOCKHISTORY(A2,
NETWORKDAY.INTL(Today(),-5,"0000011",HolidayList)
,TODAY(),0,0,1),TAKE(sh,-1)/TAKE(sh,1)-1)

But you need to make that holiday list range/table

(I may have my 1 and 0 backwards in the function. )

Thanks! I think we must be super close. I added the formula "=LET(sh,STOCKHISTORY(A2,NETWORKDAY.INTL(TODAY(),-5,"0000011",HolidayList),TODAY()-1,0,0,1),TAKE(sh,-1)/TAKE(sh,1)-1)" But I get a "#NAME" result.

I do not have your "BOOK1" you attached here above. I have no problem getting the holiday dates from the web, but I just don't know where to add it and also can I hide it as I progress and add more columns? If you can give me a link to the "(I may have my 1 and 0 backwards in the function.)" Maybe I can figure it out.

 

[awoohaw]

I think I used the wrong FUNCTION.

Can you try WORKDAY.INTL instead of NETWORKDAY.INTL