Calculate last working/business day of week

[Rkornack]

I am working with a dataset of stock market trade data going back to 1998. There are only line items on trading days, so no weekends or holidays. I need to calculate weekly performance  as:

Close value on last trading day of the week (mostly Friday, but not always) – close value on last day of the prior week

Over

Close value on last lay of the prior week

To do this, I need to determine the last working/business day of week, and the last business/working day of the prior week. Any help would be appreciated.

[caltang]

Getting weekends and weekdays is doable on Alteryx, but for Holiday, you'll need to have a separate Excel file that has all the holidays from 1998 - and depending on where the holiday is for. That requires some online searching to get what you want. 

You can make a Master Calendar using the generate rows tool, and start from 01-01-1998 until your desired end date. 

Here's a cheat sheet for ya: https://community.alteryx.com/t5/Engine-Works/DateTime-Functions-Cheat-Sheet/ba-p/844353 

[Qiu]

@Rkornack 
Agree with @caltang  a dateset with the publish holiday defined is necessay.

[Rkornack]

That does not answer my question on how to grab the last trading day of the week

[Rkornack]

Wouldn't there be some way to rank the days of the week using DateTimeFormat([Date], '%u'), then lumping in a week number DateTimeFormat([Date], '%W') and year and doing some type of multi row forumula to day if dayofweek is 1 or 2 in some cases then lookup 1 or 2 rows earlier? 

[AndrewDMerrill · Accepted solution]

Is everything that you need in your dataset? If this is the case, then you can summarize your dataset with an effective year/week and use that to determine the last day of each week that's in your dataset:

[Rkornack]

Brilliant solution!!! Thank you!! Would this be easy to adapt to find the last trading day of the month??

[AndrewDMerrill]

@Rkornack

Yes! Actually, its even easier than the workflow above since months don't overlap year to year. Just substitute Summarize Group By [Week] to Group By [Month]. I may be missing a small detail or two, but that should get you most of the way there.

Best wishes!!!