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

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 file 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 coefficient”, I have no idea if it is statistically sound.
On the technical 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 implement OptimalF for a normal distribution and Efficient 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)
AvgLoss(object[,], double, double, object, double, double)
AvgWin(object[,], double, double, object, double, double)
Correlat(object[,], 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[,])
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)
SharpeRatio(double, double, double)
SortinoRatio(double, double, double)
Stdevp2(object[,], double, double, object, double, double)
TWR(object[,], double, double, object, double, double)


kick it on DotNetKicks.com


Comments (3)

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

  2. Welcome to the XVII Community Convergence. This post is dedicated to Steve Teixeira’s Granddad, who died

  3. Work at home jobs. Home based work. Work from home. Work at home. Companies that hire employees to work from home.