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

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

Luca -

☕ 2 min. read

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)
AvgLoss(object[,], dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
AvgWin(object[,], dou­ble, dou­ble, ob­ject, dou­ble, dou­ble)
Correlat(object[,], ob­ject[,])
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[,])
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)
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)


kick it on DotNetKicks.com






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

Charlie Calvert's Community Bl


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

Work at home.


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

0 Webmentions

These are webmentions via the IndieWeb and webmention.io.