+ Reply to Thread
Results 1 to 12 of 12

macro to do some backtesting?

  1. #1
    Forum Contributor
    Join Date
    05-09-2009
    Location
    Chicago, US
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    188

    macro to do some backtesting?

    Hi guys. I have finally finished writing a code that downloads information on historical stock prices...now I have another problem, here it is:

    I have a few columns, A is date, B is open price, C is close price, D is high price...depending on the stock I pull, the number of entries in columns varies (its always very large, >5000)

    Here is what I want to do. I want to put columns in arrays, and i am guessing since the number of entries varies, i need to do a dynamic array. so i'll have three arrays for each column (open, close, high)

    this is what i am trying to do, i want the code to do the following:

    if open price is greater than close price, then i want the high price (from the same date/row) to be multiplied by a 100 and i want that result to show up in the same row in column E.

    basically, i am just comparing data in each row and if desired IF statement is satisfied (B>C) then i want E = (D*100) ...i can do this for one row, but i need some kind of a loop that will run down all the rows and do this automatically. i want to write VBA code for this, as everything i have so far is writen from scratch and works very well. im trying to avoid recording macros and do worksheet functions.


    can anyone help me with this?

    here is what i have so far, which is probably very wrong..im not even sure if i created arrays correctly..

    Please Login or Register  to view this content.

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: macro to do some backtesting?

    Hi, Here's some code. I don't see why you need an array. I tested this code with +7K lines and it took About 3 Secs.
    You could easily alter it if reguired, But this seems to do the Job.
    The Range is Dynamic for Column "B"
    Please Login or Register  to view this content.
    Regards Mick

  3. #3
    Forum Contributor
    Join Date
    05-09-2009
    Location
    Chicago, US
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    188

    Re: macro to do some backtesting?

    Mick thank you for the code, it does work, but i would like to put everything into an array because i would like to avoid using offset() function. i have more than those columns, i just said three columns to simplify things. i would like to create more complex calculations and it would be a lot easier (and impossible to do with offset() functions) if i could just do "if openprice > closeprice then ...whatever"

    i would like to, if possible, put columns in a variable and then perform all kinds of things with them.

    is that possible? or am i limited to using the offset(0,1) etc stuff?

  4. #4
    Forum Contributor
    Join Date
    05-09-2009
    Location
    Chicago, US
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    188

    Re: macro to do some backtesting?

    i actually have all of the following:

    a column = date, b = open, c = high, d = low, e = close, f = volume. i need each one of those, except the date column, as a variable so i can easily refer to each one and perform calculations.

  5. #5
    Forum Contributor
    Join Date
    05-09-2009
    Location
    Chicago, US
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    188

    Re: macro to do some backtesting?

    why does in VB change Range to range for me? Then it tells me an error because R isn't capitalized?

    When I open a new VB project, if I enter Range, it leaves it like that after hitting enter..It is just in current project and every "Range" is in small caps..

  6. #6
    Forum Contributor
    Join Date
    05-09-2009
    Location
    Chicago, US
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    188

    Re: macro to do some backtesting?

    here is the code i have, it works, but i want to get rid of all "offset()" stuff...

    Sub getrange()

    Dim openprice As Range
    Dim closeprice As Range
    Dim cell As Range

    Set openprice = Range("B:B")
    Set closeprice = Range("E:E")

    For Each cell In openprice
    If cell > cell.Offset(0, 3) Then cell.Offset(0, 7) = "buy"
    Next cell

    End Sub

    for instance, Offset(0,3) is "closeprice" range ...but If i put that in the code, it gives me an error....

  7. #7
    Forum Contributor
    Join Date
    05-09-2009
    Location
    Chicago, US
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    188

    Re: macro to do some backtesting?

    and this code takes about 4 minutes to run..? why is that?

    also, why wont it let me do cell.Offset(-1,3) ...how else do i select a one row below and 3 columns to the right?

  8. #8
    Forum Contributor
    Join Date
    05-09-2009
    Location
    Chicago, US
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    188

    Re: macro to do some backtesting?

    anyone? please. i am stuck and cant continue writing the code

  9. #9
    Forum Contributor trucker10's Avatar
    Join Date
    07-22-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003 / 2007 / 2010 prof +
    Posts
    149

    Re: macro to do some backtesting?

    Quote Originally Posted by losmi8 View Post
    anyone? please. i am stuck and cant continue writing the code
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    05-09-2009
    Location
    Chicago, US
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    188

    Re: macro to do some backtesting?

    trucker, thank you for that code, it runs a lot faster! i still thought excel would be able to do this in a second, but it does take a couple of seconds. not a big deal.

    i know there is a code to put before the code runs to speed it up, i'll just have to look for it

    thanks again!
    Last edited by losmi8; 12-27-2009 at 05:21 AM.

  11. #11
    Forum Contributor
    Join Date
    05-09-2009
    Location
    Chicago, US
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    188

    Re: macro to do some backtesting?

    sorry, found the fix. thank you again
    Last edited by losmi8; 12-27-2009 at 05:47 AM.

  12. #12
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Office 2003 / Office 2007
    Posts
    114

    Re: macro to do some backtesting?

    If possible, I'd simply determine the calculation you want to use, then use VBA to enter that calculation into the range / process the data:

    Please Login or Register  to view this content.
    In the interest of speed, I'd start with a simple formula to rule out things you DONT want to see (e.g. result of formula = DELETE). Put that in a column, create an autofilter for "DELETE", then select / delete those cells.

    After doing this, then paste in your more complicated formulas using the same method shown here. Doing this should not only be fast, it should cut down on the overall amount of calculation you need to perform.

+ 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