~~ Offline ~~ theme Menu

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