Convert Daily Stock date into weekly data.

I see this has been tried many times but I haven't seen a successful stab at it.

Okay, I have a list of dates and closing prices.

I've managed to use an array formula:

=INDEX(A$1:A$4000,SMALL(IF(WEEKDAY($A$1:$A$4000,2)=5,ROW($1:$4000)),ROW(1:1)))

to pick out the friday values. However, the stock market is closed on some Fridays and even Thursdays occasionally, like at christmas. Sometimes the last day of the week with data is Wednesday. How would you get only the data from the last day of the week.

Check out the Friday puller in the attached worksheet. It's close but skips a few weeks, like here:

12/11/1998 1175.25
12/18/1998 1197.25
1/8/1999 1287
1/15/1999 1248.5

I need the closing value of each week, regardless if it's a 5 day, 4 day or a 3 day week.


any help is appreciated.

Hi stockgoblin42

Welcome to the Forum ...

See if this workbook helps.

Sheet1 has some basic ideas that you might find useful.

Sheet2 has a possible solution using a helper column that can be hidden with the grouping button.
This should be faster than using an array formula.

I personally would stay away from arrays, they slow your workbook down too much. If you are open to the idea of a macro then try my workbook.


Edit: Marcol has done via formula for you, but I made the macro so a may as well post it. Cheers

Content deleted, post #5 wasn't visible when I posted.

Happy to help, I've noticed that and other apparent anomalies before.

Ok, I am up for a challenge,

Try this next file and see how it goes.

It does not do that on my machine, which is probably why I did not pick up on it.
Try this next file, this should fix the problem.

It is days of the week, so I just threw the values into an array and took the last value for the week along with the matching date, so it won't matter if there is one day in the week it will take that value and show it for that week.

So you are saying if Excel, for a date, returns week 53, and the day returned is Mon to Wed, ignore it.

See the attached workbook.

1/. Sheet1 is the original formula compared to Daves' results.
The black cells are where I have moved Daves' result to align with mine.
This reveals the dates to be removed.

2/. Sheet2 is the modified formulae, again a comparism is shown with the macro result.
Column E is not required, I've just retained it as a check column.

On both sheets you'll notice where I'm still having issues with Daves' dates. (Highlighted white on red)
This is probably due to regional settings, but it would be worth checking them if you go with the macro.

Hmm. Marcol, that is very weird. Does this happen on 2003 & 2007 versions?

It seems the month and day is getting put back to front.

Can you try this and see if it gives you the same result? I have a feeling it is due to the Japanese ver of excel.

@ japandave
That now works for me, both the formula approach, Post#12, and your VBa, Post#13 now match.

I'd go with the formula method, but then again I'm biased ...