macro to do some backtesting?

[losmi8]

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.

[MickG]

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

[losmi8]

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?

[losmi8]

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.

[losmi8]

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..

[losmi8]

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....

[losmi8]

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?

[losmi8]

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

[trucker10]

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

Please Login or Register  to view this content.

[losmi8]

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!

[losmi8]

sorry, found the fix. thank you again

[Marzuk]

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.