A trading/portfolio management Excel Add-in based on the books by Ralph Vince

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 optimized the algos at all. I.E. I’m sure there is a way to calculate the standard deviation without navigating the array twice, but I didn’t bother to look. They end up being fast enough anyhow (apart from the Monte Carlo related functions that I should investigate).
2. I haven’t organized the code correctly. It needs to be rewritten now that I partially know what I’m doing.
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

Tags

• CSHARP
• FINANCIAL

3 Comments

Comments

DotNetKicks.com

2007-01-05T02:00:23Z

You’ve been kicked (a good thing) - Trackback from DotNetKicks.com

Charlie Calvert's Community Bl

2007-01-08T03:51:07Z

Welcome to the XVII Community Convergence. This post is ded­i­cated to Steve Teixeira’s Granddad, who died

Work at home.

2008-08-18T02:48:41Z

Work at home jobs. Home based work. Work from home. Work at home. Companies that hire em­ploy­ees to work from home.