+ Reply to Thread
Results 1 to 3 of 3

Convert Time Series OHLC Data Frequency (from Daily to Weekly)

  1. #1
    Registered User
    Join Date
    02-25-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    3

    Convert Time Series OHLC Data Frequency (from Daily to Weekly)

    Hello, looking for some help to convert daily Open, High, Low, Close (OHLC) data to weekly format

    Open = Open value on the 1st day of the 7 day range
    High = Max High value for the 7 day range
    Low = Min Low value for the 7 day range
    Close = Close value on the 7th day of the 7 day range
    Volume = Sum of volume added each day for the 7 day range.
    OI = Sum of open interest added each day for the 7 day range

    - the "Date" column on the weekly table should represent day 1 of each 7 day range.
    - my data spans multiple years and I only want to aggregate daily data from the same week of the same year (i.e. week number 49 from 2014 should not be added to week number 49 from 2015).


    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,031

    Re: Convert Time Series OHLC Data Frequency (from Daily to Weekly)

    According sample:
    Assum week starts on Sunday
    List of 1st day of every week:
    A2:
    Please Login or Register  to view this content.
    Open, B2:
    Please Login or Register  to view this content.
    High, C2:
    Please Login or Register  to view this content.
    Low, D2:
    Please Login or Register  to view this content.
    Close, E2:
    Please Login or Register  to view this content.
    Volume, F2:
    Please Login or Register  to view this content.
    Copy F2 to G2, OI.

    Drag all down
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-25-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    3

    Re: Convert Time Series OHLC Data Frequency (from Daily to Weekly)

    Thanks - that works great!

+ 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. Convert Time Series OHLC Data Frequency
    By prudential in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-19-2020, 11:50 PM
  2. Replies: 4
    Last Post: 11-19-2019, 01:13 AM
  3. [SOLVED] Weekly summary from daily OHLC data
    By macaonghus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-08-2015, 03:59 PM
  4. [SOLVED] Convert daily data to weekly data using SOME formula
    By Miramira in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2014, 02:14 PM
  5. [SOLVED] Convert Daily Stock date into weekly data.
    By stockgoblin42 in forum Excel General
    Replies: 13
    Last Post: 05-25-2012, 07:15 AM
  6. convert daily dates/data to weekly
    By sarar in forum Excel General
    Replies: 1
    Last Post: 09-14-2010, 06:28 PM
  7. Putting data series on same time frequency
    By MLC in forum Excel General
    Replies: 2
    Last Post: 10-02-2008, 09:54 PM

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