#### dcifrths

Hey guys,

i'm trying to learn VBA and just trading strategies in general. my background isn't strong in computing and i haven't written these types of things before. i do have some matlab experience but not enough

so basically, i want to re-create an LTCM type trade. i want to execute a strategy that is betting on convergence for the LIBOR/Tbill spreads when the spread is 1 Zaway from the long term mean.

for instance, i have daily data from 1/2/1987 until 12/24/2007.

the mean spread over the period is 47.5bps and the stdev is 32.9bps.

if the spread goes above, say, 1 Z below or 1 Z above, i want to go short it (bet the spread will increase) and long it (bet the spread will decrease) respectively. i want to stay short/long until the spread returns to 0.5 Zs at which point i want to exit the trade and wait until the next time the spread moves away from its long term mean by 1 Z (we can hpefully replace 1 with "zl" and "zs" to represent the Zscores away from the mean for which i want to long/short respective).

so i want to compute returns for the strategy where those rules are met.

the data is laid out simply:

so in this example, i would be long the spread from where N0=32 and simply hold that trade until N1=44. at which point i would calculate the annualized returns = - [Product(1+Return(32:44))^(250/(N1-N0+1)) - 1] (this is negative since the returns assume i'm net short the spread...it would be simply =[product...] if the spread was at -1Z) ... assuming 250 trading day count convention. then, i would wait until the next point that Z <>=1 at which point i'd bet on convergence again according to those above steps.

EDIT: i just noticed that the above example is different than what i actually did. the above example takes return N for ZScore N when in fact i would initiate the position at Zscore N and thus get the returns from N+1 (which is how i actually calculated it)...

I just want to get to learn how to write a VBA code to create this trading strategy. ideally, one input box would just be the Z score to short and another would be the Z score to long (so they wouldn't have to be symmetric).

any thoughts/help would be greatly appreciated. this isn't a homework assignment or anything, i just want to do this to help me learn how to construct this type of test so i can do many many more on my own in VBA.

hopefully, we can all learn through this process.

EXCEL SUMMARY: ...

here is a picture to help visualize the strategy:

every time the red line crosses +1 or -1, the strategy goes into effect and stays in effect until the red line crosses +.5 or -.5.

so i just tested this without VBA and it is working perfectly but it takes way to many steps and is nowhere near effecient. it has nothing but nested IF(AND(OR(...)..)) type statements and then one final step to make sure the returns all face the correct way (i.e. if the spread is -1Z away it gives a positive return if it goes from -1.1Zs away to -0.90Zs away but gives a negative return if it goes from 1Z away to 1.5Zs away)

the strategy looks absolutely amazing on paper (and i thought of it mostly by myself yay!!! )

it gives an annualized total return (not counting financing costs, opporutnity costs etc.) of 234.28% with a st.dev of 15.99 %. if we assume a 10% financing cost and a 6% opportunity cost (from avg 3mo tbill annualized returns), then we get about a 218.28% excess return on about a 15.99% vol (different when you take out the tbill returns but whatever) for an Information Ratio of 13.65

so i want to be able to refine this strategy, but to do that, i think it is WAAAAYYY more efficient to do it in VBA.

thanks,
Barron

EDIT: obviously this is a very very simple algorithm..ideally the trading strategy would use the Zscore to compute a signal that would take X % of the maximum position based upon the Zscore relative to the max (and this wouldn't be a simple linear function either). it would be an S curve where the signal approaches -100% or +100% far quicker at the beginning than it does at the end.....buuuuut, i have to start somewhere, right?

#### Andy Nguyen

Post some excel sheet with data on it, all the excel formula. I'm sure we can do it in vba easily once we understand what you want/need

I may take a shot this weekend. No promise. I had a quick read of your post and didn't get every little details yet.

#### dcifrths

Post some excel sheet with data on it, all the excel formula. I'm sure we can do it in vba easily once we understand what you want/need

I may take a shot this weekend. No promise. I had a quick read of your post and didn't get every little details yet.

you got it...is there a way i can make the spreadsheet public? maybe i could email it to you and you post it like you did the CourseInDerivativeSecurities.xls??

that way you can visually see what is going on (i always annotate anything i build as a model so it should be very clear what i'm doing) as well as have the formulas onhand...

i would wager that the sheet can be vastly improved upon since there are correction columns and blah blah blah ... too much going on. bottom line i think it can be simplified. but, it gets the job done.

so can you let me know how to either make it available ? or would you rather i just go through and paste the formulas in a step by step format here (Which i don't think is best b/c there is a middle man there..why not look to see what i did and how i thought about it)

thanks a ton,
Barron

#### Andy Nguyen

It will have an option to upload files. Look for the paperclip icon.

#### dcifrths

It will have an option to upload files. Look for the paperclip icon.

thanks a ton andy.

i really appreciate how helpful and informed you guys are. i'm a big NEWB when it comes to any kind of programming (Except elementary functional matlab...moving formulas in my head to code or adjusting data etc.).

there is an analogous post on 2p2 (where i normally am) about the functionality of this type of trade (since it has to be in the spot market, otherwise futures prices would have adjusted in some way for the expectation of spreads decreasing from spot levels). personally, if i were to do it myself, it would be impossible to get what the trading strategy above is telling me to do.

thread: algorithmic LIBOR/TBILL trading strategy - Two Plus Two Forums ... in that thread i go into a bit more of the returns and things i'd like to do (signal construction) and limitations etc...

so we just have to imagine that we are the institution w/ the ability to reverse repo, and do whatever necessary to get short spot LIBOR.

anyways, i'll post the final spreadsheet after i double check it and remove the hardcoded 1Z trade initiator and .5Z trade exit points and do whatever other things i think would make it easiest to move directly to VBA.

again, thanks and i'll post the sheet when i get it set.

Barron

#### alain

##### Older and Wiser
let me get to the weekend and I'll try to take a swing at it as well

#### dcifrths

Andy et.al.,

please find attached here my excel sheet.

i've altered the strategy a bit such that it takes into account the asymmetric properties of the spread (can blow out far more than it can compress, thus the trigger should be farther away in Z terms and closer in Z terms respectively).

Thanks a ton and please let me know your thoughts. the strategy looks, from my perspective, to be a very powerful one with a (kind of not really) IR of >2.0

Barron

#### Attachments

4.3 MB · Views: 153

#### Andy Nguyen

Ok, I got the excel sheet and see what all the columns, charts are. What do you want to use VBA for ?

One quick comment is that in your formula, chart, you should use dynamic range instead of fixed because your data may grow or shrink in the future. Dynamic range involves some countA, count formulas.

#### dcifrths

Ok, I got the excel sheet and see what all the columns, charts are. What do you want to use VBA for ?

One quick comment is that in your formula, chart, you should use dynamic range instead of fixed because your data may grow or shrink in the future. Dynamic range involves some countA, count formulas.

well i'm thinking long term here.

i'd like to be able to write (or just use) code that can do the following:

use the selected cells (i.e. select cell as range) and out put in successive columns returns, cumulative returns, and ideally some distributional analysis of the returns (for some reason that i don't understand, the formula "countif" doesn't work with cell inputs. i.e. countif(A1:A10,"<$C$10") doesn't work. it has to be hard-coded in like "countif(A1:A10,"<.5")" which to me is ridiculous

basically, i want to be able to analyze trading strategies simply, quickly, and easily using code so that in the future i can learn how to adapt the code to different functions using what i learn here and via the books i ordered along w/ good old fashioned trial and error.

thanks a ton,
Barron

PS- can you give me an example of how to use dynamic ranges. that would be awesome b/c all of my formulas are hardcoded like the ones you see.

for example, imagine i have data in cells A5:A100 but i want to add data daily to cell A101, A102 etc.

in A3 i have a formula that reads : "=Average(A5:A100)"

how could i use the dynamic ranges you mentioned (countA)

or, if it would be easier, could you simply change the formulas in the spreadsheet and repost it so that i can see how teh formula would work in my =Product(1+....)^(250/count(if (isnumber)...)) -1 ?

again, thanks for all your hlep and time

#### Andy Nguyen

I was using this for chart my graph dynamically. It has good examples
Dynamic Charts

#### dcifrths

I was using this for chart my graph dynamically. It has good examples
Dynamic Charts

hey andy,

i just tried to learn this dynamic charting thing and it must be that i'm not that great at this type of thing in excel b/c i can't understand the example.

Dynamic Charts

has the "Basic Range" sheet which is the example of precisely what i want to do.

what i can't understand is where the reference to "YValues" in this formula =OFFSET('Basic Range'!YValues,0,-1) is ? there is no cell named "YValues" in 'Basic Range' sheet, nor is any group of cells named YValues. further, when i hit F2 on the formula box in the spreadsheet, it highlights the entire B column and a square selection is on the first data point under "Price."

it doesn't tell me how it did that and there is no instructions or references for me to work off of. i tried copying the formula to a new sheet but when i hit "enter" it opens a "Update Values: Sheet 2" file selection box (like i'd open a file) and i have no idea why it does that (my worksheet is named "Sheet 2")

this is fairly frustrating since i can't seem to get to ground 0 with this forumla.

Barron

#### Andy Nguyen

XValues and YValues are named ranges. To view it, click on Insert -> Name -> Define
You will see which column it refers to.
Look at how the YValues is defined. It works on column B, starting on cell B5 and down. The number of cell is calculated using CountA function.
XValues is just the same cells as in column B but now in column A, using Offset function

#### Yossarian

VBA code

You need to read John Walkenbach's book on VBA programming. Every question you asked here is dealt with in his book.

Y

Replies
2
Views
1K
Replies
2
Views
1K
Replies
8
Views
720
Replies
4
Views
2K
Replies
2
Views
1K