Using YASAI for Monte Carlo

[feng456]

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?

 

[abattia]

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.

 

[feng456]

Thank you very very much!

 

[abattia]

feng456 wrote on 12-29-11 11:25 PM:
hey thanks for your help with YASAI. I am wondering how I would do my PnL if it's not just +5 / -5 (like with only 2 possibilities) but the target always changed depending on the trade?

so if i had

-5
-5
2
6
9
1
-5

how would i do the PnL for it for column C per your example?

thanks

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.

 

[feng456]

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

 

[abattia]

feng456 wrote on 12-31-11 12:56 AM:
i just used the formulas you gave me and it didnt work. there was no distribution and no chart at all. i checked to make sure i followed your instructions exact

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!

 

[feng456]

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

 

[abattia]

feng456 wrote on 01-02-12 08:17 PM:
im still having problems. i did it EXACTLY the way you told me. ive now checked it 4 or 5 times. the only difference is my dataset has 536 samples rather than the 7 or 8 i gave you...but im still not getting any distribution and no charts

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...

 

[feng456]

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

thanks again for your help.