I like to write code and I still man­age to do it even now that it is not my pri­mary job. I never post the things that I write be­cause I don’t want to main­tain them. Lately Charlie convinced me that I don’t have to do that. I can just throw the code out, with­out much prepa­ra­tion or im­plicit con­tract of per­pet­ual main­te­nance.

This one is an Excel add-in that adds func­tions to Excel to an­a­lyze your trad­ing and man­age your port­fo­lio. Notice that I’m not a pro­fes­sional trader or sta­tis­ti­cian, so the whole thing could be wrong, buggy or con­cep­tu­ally ab­surd. Probably it is all of the above.

I used the ex­tremely good ExcelDna to write the add-in. You need to down­load it and fol­low the in­struc­tions in HowToInstall.txt on the at­tached zip file to use it. I based the for­mu­las mainly on the work of Ralph Vince. Please buy and read his books on money man­age­ment as they are won­der­ful.

On the sta­tis­tics side: I made up the Downside cor­re­la­tion co­ef­fi­cient, I have no idea if it is sta­tis­ti­cally sound.
On the tech­ni­cal side:

1. I haven’t op­ti­mized the al­gos at all. I.E. I’m sure there is a way to cal­cu­late the stan­dard de­vi­a­tion with­out nav­i­gat­ing the ar­ray twice, but I did­n’t bother to look. They end up be­ing fast enough any­how (apart from the Monte Carlo re­lated func­tions that I should in­ves­ti­gate).
2. I haven’t or­ga­nized the code cor­rectly. It needs to be rewrit­ten now that I par­tially know what I’m do­ing.
3. I haven’t used LINQ. It will be a lot of fun for me to rewrite the code to be more OO and to use LINQ in the process.

I plan to im­ple­ment OptimalF for a nor­mal dis­tri­b­u­tion and Efficient Frontier cal­cu­la­tions in the fu­ture. I have no idea when.

I also have some other pro­jects that might be in­ter­est­ing to share (I.E. a black­jack sim­u­la­tor that you can use to try the re­sult of dif­fer­ent strate­gies in­spired by Blackjack Attack).

This is the list of Excel func­tions that you get with this add-in. They ends up in a Trading cat­e­gory in the list of func­tions on the Insert Function Excel di­a­log:

AnnualizeRet(double, dou­ble)
AnnualizeStdevp(double, dou­ble)
AnnualStdevpParam(double, dou­ble)
ArithMean(object[,], dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
AutoCorrel(object[,])
AvgLoss(object[,], dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
AvgWin(object[,], dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
Chebyshev(double)
Correlat(object[,], ob­ject[,])
Describe(object)
DownCorrelat(object[,], ob­ject[,], dou­ble)
DownStdevp(object[,], dou­ble)
EGM(double, dou­ble)
EGM2(double, dou­ble)
GM(object[,], dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
Kelly(double, dou­ble) TradingLibrary.Losses(object[,])
MaxDD(object[,])
MaxDDFromEq(object[,])
MaxLoss(object[,])
MaxNegRun(object[,])
MaxWin(object[,])
MCMaxDD(object[,], dou­ble)
MCOptimalF(object[,], dou­ble, dou­ble, dou­ble)
OptimalF(object[,], dou­ble, dou­ble)
OptimalFArray(object[,], dou­ble, dou­ble, dou­ble)
OptimalPos(double, dou­ble)
PortAnnualGM(double, dou­ble, dou­ble, dou­ble, dou­ble, dou­ble, dou­ble, dou­ble)
PortAnnualTaxGM(double, dou­ble, dou­ble, dou­ble, dou­ble, dou­ble, dou­ble)
PortAnnualTaxGMGivenTrades(object[,], dou­ble, dou­ble, dou­ble, dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
PortStdevp(double, dou­ble, dou­ble)
PRR(object[,], dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
PRRParam(double, dou­ble, dou­ble, dou­ble)
RealReturn(double, dou­ble, dou­ble, dou­ble)
RunTest(object[,])
SharpeRatio(double, dou­ble, dou­ble)
SortinoRatio(double, dou­ble, dou­ble)
Stdevp2(object[,], dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
TWR(object[,], dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
Wins(object[,])

TradingLibrary.zip