+ Reply to Thread
Results 1 to 6 of 6

Weekly summary from daily OHLC data

  1. #1
    Registered User
    Join Date
    03-03-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    65

    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
    Last edited by macaonghus; 02-16-2014 at 01:14 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #4
    Registered User
    Join Date
    03-03-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    65

    Re: Weekly summary from daily OHLC data

    I'm impressed. Thank you!

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Weekly summary from daily OHLC data

    Happy to help and thanks for the feedback

  6. #6
    Registered User
    Join Date
    10-04-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Weekly summary from daily OHLC data

    Thanks a tonne for your answer.

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 10-01-2013, 05:05 AM
  2. Metatrader4 Daily OHLC
    By lolo3916 in forum Excel General
    Replies: 1
    Last Post: 07-29-2013, 06:57 AM
  3. Transforming Data from Daily to Weekly
    By Uberz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-02-2013, 07:09 AM
  4. Weekly data and need to match to daily
    By lnagell in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-08-2013, 02:56 PM
  5. use arrays to get daily data to weekly
    By sarar in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2010, 12:03 AM

Bookmarks

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