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

Luca Bolognese -I like to write code and I still manage to do it even now that it is not my primary job. I never post the things that I write because I don’t want to maintain them. Lately Charlie convinced me that I don’t have to do that. I can just throw the code out, without much preparation or implicit contract of perpetual maintenance.

This one is an Excel add-in that adds functions to Excel to analyze your trading and manage your portfolio. Notice that I’m not a professional trader or statistician, so the whole thing could be wrong, buggy or conceptually absurd. Probably it is all of the above.

I used the extremely good ExcelDna to write the add-in. You need to download it and follow the instructions in *HowToInstall.txt* on the attached zip ﬁle to use it. I based the formulas mainly on the work of Ralph Vince. Please buy and read his books on money management as they are wonderful.

On the statistics side: I made up the Downside correlation coefﬁcient, I have no idea if it is statistically sound.

On the technical side:

- 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).
- I haven’t organized the code correctly. It needs to be rewritten now that I partially know what I’m doing.
- 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 implement OptimalF for a normal distribution and Efﬁcient Frontier calculations in the future. I have no idea when.

I also have some other projects that might be interesting to share (I.E. a blackjack simulator that you can use to try the result of different strategies inspired by Blackjack Attack).

This is the list of Excel functions that you get with this add-in. They ends up in a Trading category in the list of functions on the Insert Function Excel dialog:

AnnualizeRet(double, double)

AnnualizeStdevp(double, double)

AnnualStdevpParam(double, double)

ArithMean(object[,], double, double, object, double, double)

AutoCorrel(object[,])

AvgLoss(object[,], double, double, object, double, double)

AvgWin(object[,], double, double, object, double, double)

Chebyshev(double)

Correlat(object[,], object[,])

Describe(object)

DownCorrelat(object[,], object[,], double)

DownStdevp(object[,], double)

EGM(double, double)

EGM2(double, double)

GM(object[,], double, double, object, double, double)

Kelly(double, double) TradingLibrary.Losses(object[,])

MaxDD(object[,])

MaxDDFromEq(object[,])

MaxLoss(object[,])

MaxNegRun(object[,])

MaxWin(object[,])

MCMaxDD(object[,], double)

MCOptimalF(object[,], double, double, double)

OptimalF(object[,], double, double)

OptimalFArray(object[,], double, double, double)

OptimalPos(double, double)

PortAnnualGM(double, double, double, double, double, double, double, double)

PortAnnualTaxGM(double, double, double, double, double, double, double)

PortAnnualTaxGMGivenTrades(object[,], double, double, double, double, double, object, double, double)

PortStdevp(double, double, double)

PRR(object[,], double, double, object, double, double)

PRRParam(double, double, double, double)

RealReturn(double, double, double, double)

RunTest(object[,])

SharpeRatio(double, double, double)

SortinoRatio(double, double, double)

Stdevp2(object[,], double, double, object, double, double)

TWR(object[,], double, double, object, double, double)

Wins(object[,])

## Tags

- CSHARP
- FINANCIAL

## 3 Comments

## Comments

## DotNetKicks.com

2007-01-05T02:00:23ZYou’ve been kicked (a good thing) - Trackback from DotNetKicks.com

## Charlie Calvert's Community Bl

2007-01-08T03:51:07ZWelcome to the XVII Community Convergence. This post is dedicated to Steve Teixeira’s Granddad, who died

## Work at home.

2008-08-18T02:48:41ZWork at home jobs. Home based work. Work from home. Work at home. Companies that hire employees to work from home.