Can anyone help me think about how to do some Excel testing?

S

SoyUnGanador

I often try and test strategies in Excel, using data off finance.yahoo.com. A couple things I'm trying to do, but I really need to simplify:

Let's say I want to have a buy signal if a stock has increased by more than [X%] in the last [Y] days. I can do that, but the only way I know how is to in the spreadsheet for each day to compare that day's price to the price [Y] days earlier in the column. That works, but if I start with a 30 day period, I cannot easily switch it to a 60 or 90 day period for example, I have to go into the spreadsheet and change it manually so each day it compares prices to the day 60 or 90 days previously. Its not that hard, but it does take time. What I want to be able to do is just have one cell that I have the lookback period, and I could just type "60" or "90" over the "30" I have in that cell and it would automatically adjust it in the rest of the spreadsheet.

How could I do something like that?

In other words, what I am trying to do is effectively say, in each "test" cell to see whether the increased price is triggered, is say, "compare the price in that cell over there for today with the price in that cell X days ago, where X is obtained from that other cell over there". Then I could just change X and it would all carry over and work out easily. Thus, if we were on row 100, and X is 30, I would want it to back out 30 from that row 100 and look at that cell, but I don't know how to do that.


Kind of a similar question on the flip side:

If the buy is triggered per the above, I want to test holding onto that position for Z number of days, and I want to fill in Z in a cell and it automatically carry through the entire spreadsheet.


Thanks for any help on this!!!
 
Another way to say at least one thing I am trying to do:

To reference a number in a cell, you would generally simply say something like: =B63

But I want to change the "63" reference to a different number, which number I can change in another cell. So let's say I plugged "30" in cell Z1. Using the above methodology, I would think I would put in something like =B(63-Z1) and have it spit out the value in cell B33. But that does not work.
 
ondafringe said:
Read up on the functions: INDIRECT() and ADDRESS()
More...

If you haven't used those two functions before, they can be a little perplexing.

So... assuming I understand (?) your needs correctly, this is how I would do it.

The formula goes in the yellow cell. That block of cells can be moved -- as a group -- anywhere on the sheet, but the group must stay together and in the same order, or you will have to update the formula. You can put the data anywhere you want, just update the reference row/column accordingly.

I'll let you type the formula in yourself, that way you will get a feel for how parenthetical nesting works.

Note: I use Libre Calc, not Excel, but this should work in Excel.

LookBack.PNG
 
Last edited:
SoyUnGanador said:
I often try and test strategies in Excel, using data off finance.yahoo.com. A couple things I'm trying to do, but I really need to simplify:

Let's say I want to have a buy signal if a stock has increased by more than [X%] in the last [Y] days. I can do that, but the only way I know how is to in the spreadsheet for each day to compare that day's price to the price [Y] days earlier in the column. That works, but if I start with a 30 day period, I cannot easily switch it to a 60 or 90 day period for example, I have to go into the spreadsheet and change it manually so each day it compares prices to the day 60 or 90 days previously. Its not that hard, but it does take time. What I want to be able to do is just have one cell that I have the lookback period, and I could just type "60" or "90" over the "30" I have in that cell and it would automatically adjust it in the rest of the spreadsheet.

How could I do something like that?

In other words, what I am trying to do is effectively say, in each "test" cell to see whether the increased price is triggered, is say, "compare the price in that cell over there for today with the price in that cell X days ago, where X is obtained from that other cell over there". Then I could just change X and it would all carry over and work out easily. Thus, if we were on row 100, and X is 30, I would want it to back out 30 from that row 100 and look at that cell, but I don't know how to do that.


Kind of a similar question on the flip side:

If the buy is triggered per the above, I want to test holding onto that position for Z number of days, and I want to fill in Z in a cell and it automatically carry through the entire spreadsheet.


Thanks for any help on this!!!
More...
It sounds to me like you are talking about Monte Carlo simulation. If so, check out these videos:


 
You can use 3 columns, for each Y and use the one you need?

And if you have spare time, indeed learn some python. Library yfinance let's you download complete ticker/prices lists with one click. Once set up, it's really convenient :)
 
You must log in or register to reply here.
Back
Top