+ Reply to Thread
Results 1 to 9 of 9

converting time frames

  1. #1
    Registered User
    Join Date
    03-05-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    10

    converting time frames

    i have an excel spread sheet of forex market prices. i'm trying to convert a 5 minute chart into a 15 minute chart. i don't understand how i get the macro to run down the entire column. i have four numbers, the open, the high, the low, and the close. to get the open i just transfer the open of the first 5 minute block over, skip two cells then do it again, like this:

    ActiveCell.FormulaR1C1 = "=RC[-7]"
    ActiveCell.Offset(2, 0).Range("A1").Select

    but then i can't figure out how to get that to keep going throughout the entire column. for the high, i would just do =max(c1:c3), then =max(c4:c6) but i also can't figure out how to repeat that for the whole column.

    i then have one other problem. for some odd reason the market doesn't close at 9:59:59 on friday but closes on 10:00:59, so there's one section that is actually a 16 minute block, or there is an orphan minute. i have to make an exception and include that 10:00:00 - 10:00:59 to the previous 15 minute block, but to do that i need to make an exception. i was thinking something like

    if one block with 10:00 is followed by another block with 0:00 because that's when the market opens on monday, then combine the 10:00 block with the block before it, but i don't know how to write that code.

    here's a spreadsheet (i don't see anywhere here for uploading attachments)

    http://spreadsheets.google.com/ccc?k...4UURjTXc&hl=en
    Last edited by kylefoley76; 03-06-2010 at 06:24 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: converting time frames

    I would suggest you post a sample illustrating both "before" and "after" so people can validate their code against the requisite logic.

    What you want to do sounds simple enough to setup I'd say - visualising the specifics is perhaps a little trickier (at least, it is for me at 8am on a Sat morning).

  3. #3
    Registered User
    Join Date
    03-05-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: converting time frames

    donkey ote, thanks for aggreeing to try and help me, i supplied a screenshot on the original post

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: converting time frames

    Can you post a workbook ?

    People (self included) are generally disinclined to recreate a file from a screen shot - if you have confidential names etc - remove them / dummy them.

  5. #5
    Registered User
    Join Date
    03-05-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: converting time frames

    ok, i got a spreadsheet over at google docs posted

  6. #6
    Registered User
    Join Date
    03-05-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: converting time frames

    anyone know the answer to this?

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: converting time frames

    Quote Originally Posted by kylefoley76
    ok, i got a spreadsheet over at google docs posted
    So are you looking for a Google Docs solution ?

    Yes: try http://www.google.co.uk/support/foru...70adf580&hl=en

    No: generally better to post an Excel file with a respectable amount of data.

    On a final note - do you want the final values to appear in a contiguous range or with blanks interspersed as suggested by your Google Doc ?

  8. #8
    Registered User
    Join Date
    03-05-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: converting time frames

    thanks for answering my post. i don't see how to post an excel doc. i see no room here for attachments.

    yes, the rows should be contiguous, no blanks between them.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: converting time frames

    To post an Excel file - in the reply window click the paperclip icon - if you can't see it click GoAdvanced.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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