Using YASAI for Monte Carlo

ok im trying to figure out how to do monte carlo analysis...

lets say i have the following:

Jan 2011
5 pts
-5
5
-5
5
5
Total: 10 pts

Feb 2011
5 pts
-5
5
5
Total: 10 pts

March 2011

5
-5
-5
-5
-5
Total: -15 pts

how would i use YASAI to run monte carlo on this?

 

I would do this as follows (there may be other ways to do it, too)…

Your example has 15 trades;
8 winners (each @ +5);
7 losers (each @ -5)
-> Prob of winning = 8/15
[BTW, Feb total is +15 pts, not +10 pts]

Your worksheet needs 4 columns

Column A
Cell A1: “Trade”
Cell A2: 1
Cell A3: 2
Cell A4: 3
.
.
Cell A16: 15

Column B
Cell B1: “Won/Lost”
Cell B2: =genBinomial(1,8/15)
Cell B3: =genBinomial(1,8/15)
.
.
Cell B16: =genBinomial(1,8/15)

Column C
Cell C1: “PnL”
Cell C2: =if(B2=1,5,-5)
Cell C3: =if(B3=1,5,-5)
.
.
Cell C16: =if(B16=1,5,-5)

Column D
Cell D1: “Total PnL”
Cell D2: =simOutput(SUM(C2:C16))


Now run the sim, and your results will be on a new tab.

 

Thank you very very much!

 

Your worksheet needs 6 columns

Column A
Cell A1: =“Historical Trade”
Cell A2: =1
Cell A3: =2
.
.
Cell A8: =7

Column B
Cell B1: =“Historical PnL”
Cell B2: =-5
Cell B3: =-5
Cell B4: =2
.
.
Cell B8: =-5

Column C
Cell C1: =“Historical Frequency”
Cell C2: =1/7
Cell C3: =1/7
.
.
Cell C8: =1/7


Column D
Cell D1: =“Simulated Trade”
Cell D2: =1
Cell D3: =2
Cell D4: =3
.
.
Cell D8: =8


Column E
Cell E1: =“Simulated PnL”
Cell E2: =genTable(B$2:B$8,C$2:C$8)
Cell E3: =genTable(B$2:B$8,C$2:C$8)
.
.
Cell E8: = genTable(B$2:B$8,C$2:C$8)

Column F
Cell F1: =“Total Simulated PnL”
Cell F2: =simOutput(SUM(E2:E8))


Now run the sim, and your results will be on a new tab.

 

Thanks very much again. I did try to do it first but nothing came up because well it was totally wrong.

 

It's working for me. Not sure what you are doing differently...
a) NB - content of cell D8 should be 7 (not 8, as I wrote), but that shouldn't make a difference to your simulation
b) check again Columns B, C, E & F against what I wrote; the problem will lie somewhere there, I suspect

Good luck! Hope you can sort it out!

 

same result...i swear i checked it...

 

Before trying to adapt the spreadsheet to your specific case, why not start with a new, clean spreadsheet and attempt to duplicate the scenario I have outlined above? Once you have convinced yourself that it works, then try to adapt the working spreadsheet to your specific case.

Also, the Yasai download site has detailed instructions on how Yasai works, in case you're still stuck...

 

got it to work finally! 1 line of error in 500+ lines of excel. sorry for wasting your time

thanks again for your help.