+ Reply to Thread
Results 1 to 17 of 17

Condensing 1-minute forex data to 20-minute forex data

  1. #1
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Condensing 1-minute forex data to 20-minute forex data

    I have the following data:-
    Local time Open High Low Close Local time Open High Low Close
    01.07.2010 00:00:00.000 GMT+1200 1.22644 1.22731 1.22625 1.22716 01.07.2010 00:00:00.000 GMT+1200 1.22716
    01.07.2010 00:01:00.000 GMT+1200 1.22728 1.22732 1.22684 1.22687 01.07.2010 00:20:00.000 GMT+1200 1.22716 1.22732 1.22466 1.22499
    01.07.2010 00:02:00.000 GMT+1200 1.22691 1.22727 1.22672 1.22691 01.07.2010 00:40:00.000 GMT+1200 1.22499 1.2266 1.22466 1.22619
    01.07.2010 00:03:00.000 GMT+1200 1.22694 1.22701 1.22633 1.22661 01.07.2010 01:00:00.000 GMT+1200 1.22619 1.22705 1.22431 1.22462
    01.07.2010 00:04:00.000 GMT+1200 1.22653 1.22667 1.22629 1.22629
    01.07.2010 00:05:00.000 GMT+1200 1.22621 1.22686 1.22599 1.22669
    01.07.2010 00:06:00.000 GMT+1200 1.22671 1.22672 1.22582 1.22609
    01.07.2010 00:07:00.000 GMT+1200 1.22607 1.22651 1.22593 1.22644
    01.07.2010 00:08:00.000 GMT+1200 1.22632 1.22656 1.22595 1.22643
    01.07.2010 00:09:00.000 GMT+1200 1.22637 1.22659 1.22587 1.22614
    01.07.2010 00:10:00.000 GMT+1200 1.22604 1.22645 1.22576 1.22624
    01.07.2010 00:11:00.000 GMT+1200 1.22643 1.22691 1.22613 1.22688
    01.07.2010 00:12:00.000 GMT+1200 1.22671 1.22713 1.22654 1.22692
    01.07.2010 00:13:00.000 GMT+1200 1.22698 1.2271 1.22661 1.22665
    01.07.2010 00:14:00.000 GMT+1200 1.22661 1.2268 1.22605 1.22651
    01.07.2010 00:15:00.000 GMT+1200 1.22682 1.22705 1.22574 1.22639
    01.07.2010 00:16:00.000 GMT+1200 1.22611 1.22656 1.22572 1.2258
    01.07.2010 00:17:00.000 GMT+1200 1.22573 1.22592 1.22518 1.22535
    01.07.2010 00:18:00.000 GMT+1200 1.22521 1.22584 1.22498 1.22549
    01.07.2010 00:19:00.000 GMT+1200 1.2254 1.22577 1.22518 1.22559
    01.07.2010 00:20:00.000 GMT+1200 1.22543 1.22543 1.22466 1.22499
    01.07.2010 00:21:00.000 GMT+1200 1.22497 1.22572 1.22486 1.2256
    01.07.2010 00:22:00.000 GMT+1200 1.22531 1.22594 1.22505 1.22529
    01.07.2010 00:23:00.000 GMT+1200 1.2255 1.22614 1.22513 1.22607
    01.07.2010 00:24:00.000 GMT+1200 1.22604 1.22623 1.22566 1.22566
    01.07.2010 00:25:00.000 GMT+1200 1.22566 1.22579 1.22516 1.22543
    01.07.2010 00:26:00.000 GMT+1200 1.22531 1.22634 1.22517 1.22626
    01.07.2010 00:27:00.000 GMT+1200 1.22627 1.22648 1.22527 1.2259
    01.07.2010 00:28:00.000 GMT+1200 1.22571 1.22609 1.22522 1.22587
    01.07.2010 00:29:00.000 GMT+1200 1.22586 1.22635 1.22564 1.22615
    01.07.2010 00:30:00.000 GMT+1200 1.2261 1.22637 1.22574 1.22595
    01.07.2010 00:31:00.000 GMT+1200 1.22612 1.22657 1.22586 1.22645
    01.07.2010 00:32:00.000 GMT+1200 1.22657 1.2266 1.22607 1.22627
    01.07.2010 00:33:00.000 GMT+1200 1.22607 1.22634 1.22572 1.22617
    01.07.2010 00:34:00.000 GMT+1200 1.2261 1.22634 1.2258 1.22609
    01.07.2010 00:35:00.000 GMT+1200 1.22594 1.22613 1.22559 1.22559
    01.07.2010 00:36:00.000 GMT+1200 1.22551 1.22598 1.22506 1.22538
    01.07.2010 00:37:00.000 GMT+1200 1.2255 1.22589 1.22503 1.22572
    01.07.2010 00:38:00.000 GMT+1200 1.22577 1.226 1.22535 1.22562
    01.07.2010 00:39:00.000 GMT+1200 1.22565 1.22634 1.22558 1.2263
    01.07.2010 00:40:00.000 GMT+1200 1.22618 1.22645 1.22592 1.22619
    01.07.2010 00:41:00.000 GMT+1200 1.22624 1.2265 1.22597 1.22614
    01.07.2010 00:42:00.000 GMT+1200 1.22615 1.22666 1.22595 1.22648
    01.07.2010 00:43:00.000 GMT+1200 1.22648 1.22656 1.22597 1.2262
    01.07.2010 00:44:00.000 GMT+1200 1.22629 1.22668 1.22606 1.22643
    01.07.2010 00:45:00.000 GMT+1200 1.22643 1.22674 1.22624 1.22663
    01.07.2010 00:46:00.000 GMT+1200 1.22661 1.22705 1.22647 1.22694
    01.07.2010 00:47:00.000 GMT+1200 1.22675 1.22696 1.22626 1.22673
    01.07.2010 00:48:00.000 GMT+1200 1.22669 1.22682 1.22588 1.22609
    01.07.2010 00:49:00.000 GMT+1200 1.22598 1.22626 1.22543 1.22574
    01.07.2010 00:50:00.000 GMT+1200 1.22574 1.22586 1.22533 1.22557
    01.07.2010 00:51:00.000 GMT+1200 1.22561 1.22588 1.22497 1.22571
    01.07.2010 00:52:00.000 GMT+1200 1.22573 1.22588 1.22515 1.22529
    01.07.2010 00:53:00.000 GMT+1200 1.22537 1.22567 1.22494 1.22542
    01.07.2010 00:54:00.000 GMT+1200 1.22564 1.2259 1.2252 1.22545
    01.07.2010 00:55:00.000 GMT+1200 1.2254 1.22569 1.22506 1.22548
    01.07.2010 00:56:00.000 GMT+1200 1.22546 1.22567 1.22511 1.22553
    01.07.2010 00:57:00.000 GMT+1200 1.22545 1.22593 1.22535 1.22564
    01.07.2010 00:58:00.000 GMT+1200 1.22552 1.22587 1.22523 1.22558
    01.07.2010 00:59:00.000 GMT+1200 1.22537 1.22564 1.22442 1.22469
    01.07.2010 01:00:00.000 GMT+1200 1.2246 1.22498 1.22431 1.22462

    I am seeking formulae to create 20-minute forex data - see data on RHS at top
    The Open price = Close price from line above
    High price = maximum of the previous 20 minutes of data
    Low price = minimum of the previous 20 minutes of data
    Close price - close price of the same Date and Time

    Any suggestions ?

    Bob M

    p.s. I have manually started to produce 20-minute data

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Condensing 1-minute forex data to 20-minute forex data

    I see minor discrepancies between your description and your manual data, so I am not sure if I am understanding correctly. Assuming I understand correctly, here's what I did:

    0) Using the . as a separator prevents my copy of Excel from recognizing the dates, so I execute a find/replace to find the . in column A and replace with /. Then I added a column to combine the date + time so that the time stamp for each data point is a complete date/time serial number. This may not be necessary for you if Excel already has these entered as dates/times. Copying from the forum text and I needed to jump through these hoops to make sure that I had complete date/time serial numbers for the time stamps.
    At this point, I have 1 minute date/time stamps in column A, columns B:D are the time stamp information from your text, E:H are your open high low close data. I2:O5 are your manually entered 20 minute data.
    1) To get the moving max/min, I added some helper cells. In I22 (adjacent to the 20 minute entry), I entered =MAX(E2:H22) and copied down. In J22, I entered =MIN(E:H22) and copied down. This gives me a helper column of 20 minute max/min data.

    Now to build the 20 minute table -- mostly lookups.
    2) In P2, I entered =I2+J2 to combine the date and time into my 20 minute date/time serial numbers. Again, this step is not necessary if your time stamps are already proper date/time serial numbers.
    3) Q2=H2, Q3=Q2
    4) Lookups for the high/low/close data.
    4a) High in R3 =INDEX(I$2:I$62,MATCH($P3,$A$2:$A$62,1)) -- note the mix of relative and absolute references for easy copying.
    4b) low in S3 -- copy of R3.
    4c) close in T3 -- =INDEX(h$2:h$62,MATCH($P3,$A$2:$A$62,1)).
    5) Q4 =T3, R4:T4 -- copy of R3:T3
    6) copy P4:T4 down as far as needed.

    That should work -- or something very similar. Because of the difficulties of going from forum text to spreadsheet, your columns may be different than mine, as long as you understand what I did.

    Did I understand correctly? Will something like that work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: Condensing 1-minute forex data to 20-minute forex data

    Column P has time stamps at 20 minute intervals
    Column Q has Open Price which equals Close price of previous row (i.e. column T)
    Column R = max(High Prices - 20 of, starting at the time stamp for that row and looking at it and the previous 19 High Prices
    Column S = min(Low Prices - 20 of, starting at the time stamp for that row and looking at it and the previous 19 Low Prices
    Column T = Close price which equals the Close price of the current row's time stamp

    i.e. each row looks at what prices have been over the last 20 minutes

    The index formulae you have given should read I2:I22 NOT I2:I62 etc for the second row onwards
    However, in the third row we need I23:I43 etc
    Fourth row - we need I44, I64 etc

    I think ?

    Bob M

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Condensing 1-minute forex data to 20-minute forex data

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Condensing 1-minute forex data to 20-minute forex data

    BTW I have no idea what " forex" means?

  6. #6
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: Condensing 1-minute forex data to 20-minute forex data

    see attached sheet

    Bob M
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: Condensing 1-minute forex data to 20-minute forex data

    foreign exchange market

  8. #8
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Condensing 1-minute forex data to 20-minute forex data

    Hi

    Try this (your data in A3:G63)
    First row
    Formula to H3:N3 =A3 and drag forward
    Formula to H4:J...
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula to K4,L4,M4,N4 are
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Copy down and forward
    Note: You can change value in B1
    See the file
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: Condensing 1-minute forex data to 20-minute forex data

    Thank you for your brilliant effort

    when I select and drag down H6...N6

    K7 is correct but the remaining cells have #REF!

    What am I doing wrong ?

    The dragged down formulae look OK

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Condensing 1-minute forex data to 20-minute forex data

    You might want to have a look at https://www.excelforum.com/excel-pro...ime-frame.html

    If in you first column you find and replace .0* with nothing, and then replace . with / it converts the values to dates.
    Last edited by shg; 02-12-2019 at 02:04 PM.
    Entia non sunt multiplicanda sine necessitate

  11. #11
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Condensing 1-minute forex data to 20-minute forex data

    Hi

    Your data in column A is a string 01.07.2010 01:20:00.000 GMT+1200

    That data for me is a real date in column A, a real time in column B and a string "GMT+1200" in column C

    You can convert using DATA -> Text to Column

    See the file (I extend the range to row 6300)
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: Condensing 1-minute forex data to 20-minute forex data

    thanks to all for your contributions

    shg - I looked at the reference and you macro is ideal

    I shall add a further case = 20 mins

    Many, many thanks

    Bob M

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Condensing 1-minute forex data to 20-minute forex data

    I added a couple of other window sizes, attached.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: Condensing 1-minute forex data to 20-minute forex data

    Are rgnInp and rgnOut named ranges ?

    Can I use my original form of Date/Time in text format ?

    Bob M

  15. #15
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Condensing 1-minute forex data to 20-minute forex data

    Are rgnInp and rgnOut named ranges?
    Yes.

    Can I use my original form of Date/Time in text format?
    No. I explained how to convert them to Excel dates in post #10.

  16. #16
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: Condensing 1-minute forex data to 20-minute forex data

    When I do the first part of the Find and Replace I end up with a column of 1's ??

    Bob M

  17. #17
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Condensing 1-minute forex data to 20-minute forex data

    Initially

    A
    1
    Local time
    2
    01.07.2010 00:00:00.000 GMT+1200
    3
    01.07.2010 00:01:00.000 GMT+1200
    4
    01.07.2010 00:02:00.000 GMT+1200
    5
    01.07.2010 00:03:00.000 GMT+1200


    Replace .000* with nothing

    A
    1
    Local time
    2
    01.07.2010 00:00:00
    3
    01.07.2010 00:01:00
    4
    01.07.2010 00:02:00
    5
    01.07.2010 00:03:00


    Replace . with /

    A
    1
    Local time
    2
    1/7/2010 0:00
    3
    1/7/2010 0:01
    4
    1/7/2010 0:02
    5
    1/7/2010 0:03


    EDIT: I see in the prior post I said replace .0* with nothing.

+ 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. FOREX Data Spread Sheet
    By James Bowler in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2016, 02:04 PM
  2. [SOLVED] Record on/off time for minute by minute data set
    By bdenzer in forum Excel General
    Replies: 5
    Last Post: 07-30-2015, 07:41 PM
  3. Getting Live MT4 forex data
    By mrprofit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2014, 07:18 AM
  4. Correspond Dates to Minute by Minute Data
    By chubby127 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-22-2013, 05:50 AM
  5. Live Forex Data - Not possible?
    By heatwave in forum Excel General
    Replies: 0
    Last Post: 07-25-2011, 02:56 AM
  6. rolling minute data into 30 minute averages
    By grc1980 in forum Excel General
    Replies: 0
    Last Post: 08-30-2006, 03:58 PM
  7. Savs All Forex Data
    By YJL in forum Excel General
    Replies: 2
    Last Post: 12-05-2005, 07: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