Money management - any Excell wizzards on the board?

Discussion in 'Risk Management' started by neo_hr, Oct 4, 2001.

  1. neo_hr


    Dear Traders!

    I am just learning about the moneymanagement and have a following idea :

    Since the stock direction on the next day can be up or down (50/50) chance, similar to a coin flip, I d like to have a program (or, I was thinking maybe a spreadsheet) that would let me input initial nr. (or acct balance), risk (percentage) Id put on any one position, the number of flips and finally the portion I loose on a losing trade(1x) with the portion i win on a winning trade (2x or even 3X).

    If anyone knows how to do it, has something finished, knows ofa website Id be more than thankful.

    Good tradimg to you all!


    UPDATE : OK, heres the link to the article I read and why I actually started this thread so you can get a broader picture
  2. sallyboy

    sallyboy Guest


    Obviously you can pursue trading as you see fit, but I just had to comment that I don't think the direction of a stock the next day is simply 50/50. There is a chance to go in either direction (depending on the stock, where it is, where it's been, etc.), but could be 60/40, 70/30, 80/20, etc. on any given day. Placing bets & risking capital when you have no clue which way a stock is likely to go the following day is akin to gambling. While some compare trading to gambling, it is not. With trading you have to manage money, manage trades, & go where the risk/reward ratio is in your favor. (And for those that might bring up the argument that there are professional gamblers, I would agree. But there is a big difference between a truly professional gambler & a non-pro.)

    In sum, I don't think there is any magic formula in trading that will guarantee positive results.

  3. neo_hr


    Dear Sallyboy and the others,

    I think we have a misunderstandinghere. I KNOW all about trade management, rules to stick to, position sizing and everything we use every day as traders whatever the style!!! :)

    BUT! One thing I think everyone has to agree, YOU SIMPLY CANNOT FORECAST what the next bar is going to be (daily, 3 min or tick) it can be a white candle as much as it can be a black one.

    Right now Im starting to develop my money management scheme as I have seen how much it can mean in trading, and learning about risk management etc. so this with the spreadsheet has just caught my mind, thought someone would know how to make it.

    PLUS, the reason I want it is to see what result (acct balance) comes out after a # of flips (trades) with this-or-this portion being risked and I would like the ability to change it.
    Basically I would like a program that would allow me to say, "OK, I have 10k capital, will risk 3% on every trade, make 3$ for every winning one, loose 1$ for loosing, -WHAT WILL MY BALANCE LOOK LIKE AFTER 100 randomized trades?".

    Thats it folks! Happy trading


  4. to run a Monte Carlo simulation on the securities you intend to trade. You'll need to know the mean, sd and moments of the distributions of the securities. Plug them in and you can run 100 or 100,000 passes through the simulator. You'd need to make a few more assumptions (long or short) and be sure to model log return and not price, but it could be done. Excel's random number generator is ok, but you'd be better off substituting a real quasi-random number generator, imo.

  5. neo_hr



    I dont want to do theese complicated simulations and that stuff, I just need "something" that will generate 100, 1000 or 10000 for that matter random yes or no's; black or whites; heads or tails and by the way calculate what I have won and what lost. I dont want to apply it to the market, I just would like to see what my retrurn would be if I risk certain amount of $ per trade, win another amount per trade and if the chances of winning are close to 50/50.

    In other words, just to see how the final acct balance acts accordingly to me changing risked percentage.

    Thanks for all the effort you put in!

  6. Are you referring to an expected value equation?

    i.e. pwin =.50
    plose =.50
    avgwin =$3.00
    avgloss =$1.00
    numtrd = 100

    exp value = (pwin*numtrd*avgwin) - (ploss*numtrd*avgloss)


    Don't forget to subtract commissions.

    If this ain't it, there's no way to calculate returns for a system without knowing market metrics and running a simulation, period

  7. mgregor


    One thing to keep in mind is that you might get a losing or winning streak. So although, statistically you might be right or wrong 50% of the time, you could still get a streak of 20-30 or more losers or winners in a row.

    Depending on the size of your capital, this means that you could have a positive expectancy system and still go bust! This is probably the number one reason that most traders fail... they're undercapitalized.

    Finally, you're not really going to end up with a 3:1 reward/risk ratio by winning $3 and losing $1 on each trade. This is due to slippage and commissions.
  8. Actually if you have 50% probability of winning each trade, the probability of having a streak of 20 loses is 1:1,048,576 and a streak of 30 loses is 1:1,073,741,84. That's one in more than one billion, and with a $1 stop loss you'd still lose only $30 x average number of shares, which wouldn't be a lot if each trade is 3% of 10k ($300, if the average price is only $20, that would be 30x20 or a $600 loss.) Traders mostly get wiped out because they do not have a tight enough stop (they hold on to losers) or their system stinks. The possibility of a losing streak is part of the risk, but so are stops and percentage of capital committed to each trade.


    If the chart was a result of coin flips, you would not have 50% of winning with a $1 stop and $3 on trades won. The chance of winning would be 1:3. But of course the stock charts are nothing like a chart that would be a result of coin flips, and in fact if you had no entry criteria you still could probably beat the market by a significant percentage by setting appropriate stop losses. That's why the kind of simulation you are talking about makes absolutely no sense whatsoever. Like I and others already told you, you should set your stop loss based on charts only. Same goes for entry criteria. As far as how much of your equity you should commit to a single trade, that should be based on risk of having a losing streak or something going wrong (such as the stock being halted, losing your internet connection, etc., etc.) And btw, that should be a lot more than %3 of 10k.

  9. Neo, I am not exactly sure of what you are trying to do, but you may find something to suit you in the book "Mathematics of Money Management" by Ralph Vince.

    In particular it deals with the concept of optimum F which is the optimum fraction of your account to put into a sequential series of trades with defined risk and reward characteristics. The optimum fraction will grow your account at the fastest possible rate while reducing your risk of going broke to an acceptable low level. I know there is a spreadsheet treatment for this described there. You may have to generate a series of random trades to do the analysis. One consequence is that, if you want to grow your account at a fast rate, you have to be prepared to accept large drawdowns.

    Even if this is not what you are looking for there is lots of food for thought in the book on the subject of risk analysis.
  10. mgregor



    The point I was trying to make is that even a 50% probability of winning would only an accurate percentage over a very large sampling of trades (10,000 or more).

    So if you were to only make 20-30 trades, you may very well end up with more losers or winners than 50% on each side. You might get lucky and win more, but you can also be unlucky and lose several times in a row. Maybe even 10-20 times.

    The fact is, no one can really pin point the exact probability of a system because of all the variables associated with the market. You might have a "system" that produces 10 winning trades and ten losers, but that certainly does NOT mean you have a 50% probability of winning. Not unless you have a very large number of trades to base your observations on.
    #10     Oct 6, 2001