Generate Rows for Missing Dates with data from prior day

[J-Riedel]

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!

[Ladarthure]

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

[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 🙂 )

[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/...

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?

IF

[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