Generate Rows for Missing Dates with data from prior day

Hi,

I need to do an average on stock prices where we get the weekday prices from the NYSE. This only includes week days, so now weekend or holidays, but to do an average price, I need to assign the Friday price to Saturday and Sunday and for a holiday assign the price from the prior day. I need to do this for a specific period, so I have added a macro to choose the period end date.

I have taken a stab at development based on other information on the Community, but I hit a problem if the last day of the month is a weekend. For example, April period end is 4/30/2021, which was a Saturday. I have the 4/29/2021 price, but I can't get it to generate a row for 4/30. So the real issue in my process is when the period end date is not a date in the data file.

Suggestions? I have attached my process below. In the Multi-Row Formula, I cannot get the new column for "DateRange" to populate.

Thanks!

EPS Average Prices.yxzp

Help

Accepted answers

Ladarthure

Here is a way to do it !

EPS Average Prices.yxwz

All comments

Ladarthure

I did a sample workflow to generate all dates, bellow what you did, tell me if it works for you!

EPS Average Prices.yxzp

Luke_C

Hi @J-Riedel

Try updating your multirow formula tool to this:

if isnull([Row+1:DateAlteryx])

then datetimeadd([DateAlteryx],1,'day')

else [Row+1:DateAlteryx]

endif

J-Riedel

Thank you Ladarthure. I am having a problem opening the file that you attached.

Ladarthure

@J-Riedel what's your version of alteryx ?

J-Riedel

V 2020.2.3.27789. I got an error message that indicated it was having an issue opening the Alteryx package and also could not open a file from a newer version.

Ladarthure

Find attached the file in 2020.2 (only the yxwz file 🙂 )

EPS Average Prices.yxwz

atcodedog05

Hi @J-Riedel

You can use the guide to downgrade packaged workflow.

https://community.alteryx.com/t5/Engine-Works/Making-Workflows-Apps-amp-Macros-Backwards-Compatible/ba-p/779546

Hope this helps : )

J-Riedel

Thank you both. Still having an issue. I tried rolling this forward for May 2021, in which the last day of the month/period is a Monday, but it is a holiday (Friday is 5/28).

Using the solution from LukeC, it did fix my missing day for April, but for May, the days stop at that Friday, so still missing Sat-Mon 5/29-5/31.

Using the solution from Ladarthure, it did fix my missing day for April, but for May, the days stop at Sundy, 5/30, so still missing Monday 5/31.

Is there a way to get all the way to the end of the period, including the end of the period day?

HomesickSurfer

Hi @J-Riedel

Will this work?

[Weekday]="Friday"

THEN

DateTimeFormat(DateTimeAdd([Date],+1,"days"),"%Y-%m-%d")

ELSEIF

[Weekday]="Monday"

THEN

DateTimeFormat(DateTimeAdd([Date],-1,"days"),"%Y-%m-%d")

ELSE

ENDIF

Generate weekend dates.yxmd

apathetichell

I'm probably missing something - but can you walk through why you are assigning the price to the successive day? Is it because of how your data updates/downloads?

I'm not quite getting it. Wouldn't it potentially double count other days?

J-Riedel

It is all in the data that we can obtain from the NYSE.

So calculate the MTD avg price, I need the Total of Daily Prices / # of Days for the average. When we download the activity from the stock exchange, we only get prices for the days in which the stock exchange was open, typically Monday-Friday.

So to use May as my example, the data that I can get includes 20 days, so these are business days and it is excluding weekends and holidays. To get to the 31 days that I need for the average, I need to carry Friday's price for Saturday and Sunday. Then if there is a holiday, I need to carry the prior day for the holiday.

So for May, the last business day of the month was Friday, 5/28 (since Monday 5/31 was the Memorial Day holiday). Therefore, I need the 5/28 price to carry for 5/29, 5/30, and 5/31.

Ladarthure

@J-Riedel in your data, I don't see the month of may, I can do something but I need somewhere to have the data or at least something around the months needed.

Ladarthure

Here is a way to do it !

EPS Average Prices.yxwz

J-Riedel

This looks great!! Thank you very much. I will use this and see if it works for all my months.

J-Riedel

Again Ladarthure, thank you. I finalized this through my process and tested for the first 6 months in 2021 and all works great. Thank you so much for the help this morning.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703