Calculating weekly average

[Milandereede]

Hello there! Apologies if this has been answered before but after a long time searching I've given up.

I have a data sheet with daily stock data for 36 different stocks over a 5 year period. What I want to do now is convert the daily stock bid/ask price into a weekly return. This should be as easy as =(G9-G5)/G5, since I am just trying to find the average change between the start of the week and the end of the week (monday to friday). The problem I however run into is that the stock market has not been open everyday, therefore I can't simply drag this formula down the entire length of the document and would have to manually input this for every week as some have 4 business days and some have 5.

There must be some faster way to do this, but I really just can't seem to figure it out.

I hope the image offers some clarification in case I was unclear. So far I have been trying to do it manually but since I literally have tens of thousands of rows I was hoping there would be a faster way.

Any help would be greatly appreciated!
[TABLE="width: 1164"]
<colgroup><col><col><col><col><col><col><col span="5"><col></colgroup><tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]

[/TR]
[TR]

[/TR]
</tbody>[/TABLE]

 

[Pup Denab]

HI Milandereede
Welcome to the forum, here's an easy way, I'm sure their is a better way
put this in cell M2 =WEEKNUM(B2)&YEAR(B2)
put this in cell N2 =SUMIF(M:M,M2,G:G)/COUNTIF(M:M,M2)
all it does is workout the week number & year, then use that to work out you average

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

It seems to be quite hard to do using worksheet formulas.
How about a macro solution?

Sub xx()
    Dim rAll As Range, r As Range, strt as Double
    Set rAll = Range("A1").CurrentRegion
    Set rAll = rAll.Offset(1).Resize(rAll.Rows.Count - 1)
    
    For Each r In Intersect(rAll, Columns(2))
        If Format(r, "ww") <> Format(r.Offset(-1), "ww") Then strt = r.Offset(, 5)
        If Format(r, "ww") <> Format(r.Offset(1), "ww") Then r.Offset(, 10) = (r.Offset(, 5) - strt) / r.Offset(, 5)
    Next
End Sub

It finds the CurrentRegion then discards the headers and loops round all the dates.

Format(r, "ww") finds the week number.
If it is the first week then it saves the value in G to a variable called strt.
If it is a last day then it does the calculation and outputs it.

 

[Milandereede]

First of all thanks to both of you for replying!

HI Milandereede
Welcome to the forum, here's an easy way, I'm sure their is a better way
put this in cell M2 =WEEKNUM(B2)&YEAR(B2)
put this in cell N2 =SUMIF(M:M,M2,G:G)/COUNTIF(M:M,M2)
all it does is workout the week number & year, then use that to work out you average

I tried this, however this only works for B2. If I try inputting the same formula for B3, the week number is 92 in year 008 which doesn't entirely seem right.

RickXL, your solution seems to work better except for some reason it doesn't work in all cases.

From what I can tell with my limited knowledge it seems to calculate fine for most cases, though for some reason it displays 0.00% in a lot of cells. Furthermore it seems to run into some problem when a week runs into the next months as is the case in cell L22. I've been looking at the Visual Basic code for a while now, and I can't even exactly figure out what it actually does so it's hard for me to fix it (guess one course in Visual Basic 5 years ago doesn't do the trick..).

Not sure if this works, but this should be the dropbox link to the file: https://www.dropbox.com/s/emdg2cjthe7q386/Data sheet with only selected stocks with macro.xlsm?dl=0. Maybe that clarifies my problem.

Again, many thanks and apologies for knowing so little about how to get this done.

 

[RickXL]

Hi,

Not sure what to say ...

I downloaded your spreadsheet, cleared column L then ran the macro in Module1.
It looks OK to me. No 0% entries and L22 is coped with.

I did notice that your dates are character strings and not in Excel date format (i.e. days from 1900) but it seems that the date to week conversion is working anyway.

How it works:
It loops through each row looking at the date. It works out the week number of the current row and the week number of the previous row. If they are different then the current row is the first day of this week.

It also looks at the current row and the next row. If the weeks are different then it is the end of the week.

At the start of the week it makes a note of the value that is in column G (i.e. offset 5 columns from the date) in the variable strt.
At the end of the week, it uses the value in G and strt to perform the calculation. It outputs that value.

I have added some debug satements to the code so that it displays what is happening in the Immediate window. If you step through using the F8 key you should be able to watch the process. I have also added a line of code that will clear column L. (Intersect(rAll, Columns(12)).ClearContents)

Sorry about the Intersect thing. That is me being over clever. Basically, rAll is all the data without the headers. Column(12) is all of column L. So intersect one with the other and you have the part of column L with the data in. The For loop works similarly. It just loops through column 2 of the data.

The debug statements will slow down the macro and the Immediate window only retains so many lines. So if you run the whole macro you will only be able to see part of the log.

I hope this helps.

Sub xx()
    Dim rAll As Range, r As Range, strt As Double
    Set rAll = Range("A1").CurrentRegion
    Set rAll = rAll.Offset(1).Resize(rAll.Rows.Count - 1)
    Intersect(rAll, Columns(12)).ClearContents   ' Clear column L
    
    For Each r In Intersect(rAll, Columns(2))
        Debug.Print "Row: "; r.Row
        If Format(r, "ww") <> Format(r.Offset(-1), "ww") Then
            strt = r.Offset(, 5)
            Debug.Print "Date: "; r; " strt: "; strt
        End If
        If Format(r, "ww") <> Format(r.Offset(1), "ww") Then
            r.Offset(, 10) = (r.Offset(, 5) - strt) / r.Offset(, 5)
            Debug.Print "Date: "; r; " Ans: "; r.Offset(, 10); " Strt: "; strt; " End: "; r.Offset(, 5)
        End If
    Next
End Sub

 

[Pup Denab]

HI Again
I have also downloaded your sheet, Rick's macro, looks fine, I have also tried the formulas, I suggested, which also work, however their are both resource heavy, Please try the link below, it took 8 minutes to run on my PC, I have copied the results & pasted back as values, sorry if my answer was unclear, you need to drop the formula to cover your data, so M2 would then become M3 ect

Each work week contains anywhere between 1 & 180 entries, which makes for a lot of calculations indeed


Hope this helps

https://www.dropbox.com/s/xhr5f8duy... selected stocks with macro updated.xlsm?dl=0

 

[RickXL]

For me the macro with the debug statements takes 32 seconds and the original one takes 2 seconds.
That's on an Intel Q6600, so not new by any means. 8GB of RAM, Win 8 and Office 2013.