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!

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

[J-Riedel]

Thank you Ladarthure.  Attached is the excel file with the stock prices for the year 2021 through May that we would get from the NYSE.

[Ladarthure · Accepted solution]

Here is a way to do it !

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