How to get weekly open high low close from a range of data?

[Herbert Chan]

Hello,

I've set up a spreadsheet to download mutual fund prices daily from a
certain website and have stored up quite an amount of data already.

Now I want to use this data to produce weekly candlestick charts, so I need
to get the weekly open, high, low, close from the data.

The easiest way I can think of in general is to use a GROUP BY query to
extract all such data from the data range, but I don't know how to set up
such a query in Excel.

Anyone out there can tell me?

Thank you very much.

Herbert

 

[duane]

if you have daily dates in column a, and the open, hi, lo, close i
columns b-e, you could list all the Mondays in say column G (easy to d
since they are 7 days apart) and put this formula in column H to get th
min open for a given week - in this case for the week starting by th
date in cell G5 (with the next week starting by the date in cell G6.
Change min to max to het weekly high, and the reference in the offse
function to get the lo or close as opposed to the open.

=MIN(OFFSET(B$6,MATCH($G5,$A$7:$A$12,1),0,1,1):OFFSET(B$6,MATCH($G$6,$A$7:$A$12,1)-1,0,1,1)

 

[duane]

i guess i didnt entirely answer your question, but you can find th
weekly open by doing the offset...match combo to find the open for th
Monday in question, and also to find the close for a given Frida
(subtract 1 off offset result for subsequent Monday

 

[Herbert Chan]

I want to add that apart from the fact that sometimes it's holiday on a
Monday or a Friday, I sometimes also fail to get the data daily from the
website, so my data won't be continuous and there may be some missing dates.
But that will only happen like maybe 1 day or 2 in a week, and I still want
to produce weekly candlestick chart.

So using the offset method may not really be possible.

Herbert

 

[duane]

I sounds like the way to go would be to wrtie a macro to step thru you
data and figure out which data is in a given week

 

[Herbert Chan]

I find out I can use MS Query to perform the query, even with GROUP BY, but
the result may need to be reside on another workbook though. It isn't a
problem. Using SQL is the neatest way to extract the data from such a
source I think.

Herbert

 

[duane]

I wrote this which is written to only handle a small volume of data
the input is assumes to be in columns 1 (date) thru 5. I plunked th
output in columns 7-11. You can expand the data volume by enlargin
the dimensions of the variables, and enlarging the i and j loo
counters

Sub Macro1()
'
' Calculate weekly hi, lo, close and open data from daily data
'
' Define right data set size!
'
' 10 days data allowed here
'
Dim day(10) As Integer
Dim Dat(10) As Date
Dim openprice(10) As Double
Dim loprice(10) As Double
Dim hiprice(10) As Double
Dim closeprice(10) As Double
'
' 10 weeks data allowed here
'
Dim weekdate(10) As Date
Dim weekopen(10) As Double
Dim weekclose(10) As Double
Dim weekhi(10) As Double
Dim weeklo(10) As Double
'
'read in all daily data
'
For i = 1 To 6
Dat(i) = Cells(i + 6, 1).Value
day(i) = WorksheetFunction.WeekDay(Cells(i + 6, 1), 2)
openprice(i) = Cells(i + 6, 2).Value
closeprice(i) = Cells(i + 6, 3).Value
hiprice(i) = Cells(i + 6, 4).Value
loprice(i) = Cells(i + 6, 5).Value
Next i
'
j = 0
For i = 1 To 6
If i = 1 Then GoTo firstday
If day(i) > day(i - 1) Then GoTo sameweek Else GoTo newweek
firstday:
newweek:
'
' if day number < previous day number then establish new week
'
If j = 10 Then GoTo done
j = j + 1
weekdate(j) = Dat(i)
weekopen(j) = openprice(i)
weekclose(j) = closeprice(i)
weekhi(j) = hiprice(i)
weeklo(j) = loprice(i)
sameweek:
'
' if day number > previous day number (therefore same week)
' recalculate weekly hi, lo, close
'
weekclose(j) = closeprice(i)
weeklo(j) = WorksheetFunction.Min(weeklo(j), loprice(i))
weekhi(j) = WorksheetFunction.Max(weekhi(j), hiprice(i))
Next i
done:
'
'this figures out how many weeks to summarize
'
lastj = j
For j = 1 To lastj
Cells(j + 7, 7) = weekdate(j)
Cells(j + 7, 8) = weekopen(j)
Cells(j + 7, 9) = weekclose(j)
Cells(j + 7, 10) = weekhi(j)
Cells(j + 7, 11) = weeklo(j)
Next j
End Su