# Weekly summary from daily OHLC data

1. ## Weekly summary from daily OHLC data

I have daily Open, High, Low, Close stock prices. Separately, I need the same data but in weekly format, so:

1. The weekly open is the open of the first working day.
2. The weekly high is the highest of each of the daily highs.
3. The weekly low is the lowest of each of the daily lows.
4. The weekly close is the close of the last working day.

How can I do this using formulas?

Thank you!
OHLC sample.xlsx

2. ## Re: Weekly summary from daily OHLC data

Try this for a start...
O3=VLOOKUP(\$N3,\$C\$3:\$I\$46,4,0)
P3=MAX(IF(\$C\$3:\$C\$46>=\$N3,IF(\$C\$3:\$C\$46<=\$N3+5,\$G\$3:\$G\$46,0)))......ARRAY formula
R3=VLOOKUP(\$N3+5,\$C\$3:\$I\$46,7,1)
Im still working on Q3

ARRAY formula......confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

3. ## Re: Weekly summary from daily OHLC data

For Q3
=MIN(IF(\$C\$3:\$C\$46>=\$N3,IF(\$C\$3:\$C\$46<=\$N3+5,IF(\$H\$3:\$H\$46>0,\$H\$3:\$H\$46,0))))
ARRAY entered

4. ## Re: Weekly summary from daily OHLC data

I'm impressed. Thank you!

5. ## Re: Weekly summary from daily OHLC data

Happy to help and thanks for the feedback

6. ## Re: Weekly summary from daily OHLC data

I just have a little more question. How can I get start of the week date in \$N\$3....to the end ?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1