Using Excel to calculate optimal position size based on cumulative profit figures

Gringotts Bank · 17 October 2015

Here's a list of numbers representing cumulative profit of a system (last number = most recent). The position size used to generate this list was a fixed $ amount.

How can Excel be used to determine the optimal position size for the next trade you're thinking of taking? Assume a working capital of 10k.

178.92
1292.04 (big win)
1125.27
1026.14
1187.62
1366.51
1205.78
1368.08
874.41 (big draw down)
1146.2
1578.25
2143.83
1766.16

I'm thinking of a formula something like this:

In cell B3 I have: =if(A2>A1,A3*1.5,0.5*A3) and dragged this down.

Now, not sure of my next move.

rnr · 17 October 2015

What about using %Risk/Trade to adjust the position size of the next trade.

Going forward you would probably need to track the outcome of each trade using this process so that it can be factored into future PS calculations.

Gringotts Bank · 17 October 2015

rnr said:
What about using %Risk/Trade to adjust the position size of the next trade.

Going forward you would probably need to track the outcome of each trade using this process so that it can be factored into future PS calculations.

Hi rnr,

I don't know how that would look (%risk/trade). But yes, I'm thinking of using Excel to calculate each day's position size.

Gringotts Bank · 17 October 2015

comparisons based on last trade (positionsize *7, positionsize/7). Obviously wouldn't use 7 in real trading.

cynic · 17 October 2015

1.339 seems to be the optimal factor for your formula given the 10k starting capital.

Gringotts Bank · 18 October 2015

cynic said:
1.339 seems to be the optimal factor for your formula given the 10k starting capital.

Hey cynic, how'd you calculate that?

Does that mean the next trade value is 13.39k?

cheers

cynic · 18 October 2015

Gringotts Bank said:
Hey cynic, how'd you calculate that?

Does that mean the next trade value is 13.39k?

cheers

Only if the previous trade was 10k.

The calculation was done via a manual brute force approach which ensured that the initial trading capital was never wiped out by any of the drawdowns. I assumed starting capital was 10k for the purposes of this exercise, but after your latest post now suspect that I may have misunderstood.

Gringotts Bank · 18 October 2015

cynic said:
Only if the previous trade was 10k.

The calculation was done via a manual brute force approach which ensured that the initial trading capital was never wiped out by any of the drawdowns. I assumed starting capital was 10k for the purposes of this exercise, but after your latest post now suspect that I may have misunderstood.

Yeh I don't know. Probably for this exercise will need 100k.

I'm multiplying the position size for the next trade by 7 ($70,000) if the equity has risen on the current bar, and dividing by 7 ($1500) if the equity fell on the current bar.

I know 7 is a big number but I just wanted to see what influence it had. Seems to help a bit too much, and I guess it's not realistic to use current bar's equity since the trade will still be open.

So I'll take the "average bars in trade (=x) " change the sizing based on the equity x bars ago.

Gringotts Bank · 18 October 2015

This one uses a factor of 2 rather than 7.

col A: cumulative profit
col B: individual trade profit (loss)
col C: =if(B2>0,B3*2,B3/2)
col D: =sum(C$3:C3)

How do people feel about this set up? Is it realistic for a portfolio system?

Thanks for feedback.

Gringotts Bank · 18 October 2015

I'd feel a lot more confident with this if the cumulative profit was based on closed trades.

Does anyone know if Amibroker can programmed so that the portfolio equity reflects closed trades?

Gringotts Bank · 19 October 2015

Anyone care to add something?

Craton · 19 October 2015

Add something to something else = sum of something

Sorry, couldn't resist my feeble attempt at humour....

howardbandy · 20 October 2015

Gringotts Bank said:
Anyone care to add something?

Greetings --

My writings and presentations have thorough discussions and examples of the importance of position sizing. I believe that trading development and trading management are separate tasks, sharing the set of trades produced by the signals and executed in the market.

Position sizing can be, and should be, applied to the trades as they occur using statistical techniques that will indicate the health of the system. Trading performance measures the degree of synchronization between the model (indicators, parameters, and rules) and the data. Performance will be good for as long a period of time as that relationship is consistent and stationary. When trading performance is poor, reduce position size, when performance is good, increase position size.

I have posted four recent presentations to YouTube. Begin with "The Importance of Being Stationary":
https://www.youtube.com/watch?v=iBhrZKErJ6A

I also recommend:

"The Four Faces of Risk":
https://www.youtube.com/watch?v=Vw7mseQ_Tmc

"Indicator-based Trading System Development":
https://www.youtube.com/watch?v=W-gSsqHORJE

"Machine Learning Trading System Development":
https://www.youtube.com/watch?v=v729evhMpYk

And, if a moment of shameless self promotion is allowed ---

My book, "Modeling Trading System Performance" answers your question in great detail and using Excel, as you request.
http://www.modelingtradingsystemperformance.com/
http://www.amazon.com/Modeling-Trading-System-Performance-Howard/dp/0979183820/

My book, "Quantitative Technical Analysis" (QTA) continues that analysis using Python.
http://www.quantitativetechnicalanalysis.com/
http://www.amazon.com/Quantitative-Technical-Analysis-integrated-development/dp/0979183855/

The QTA book outlines the "dynamic position sizing" concept, and gives Python code to estimate risk of a system or set of trades, normalize the risk so that alternatives can be directly compared to each other, and calculate optimal position size for a trade-by-trade sequence. It also introduces machine learning as a system development technique.

Both books have several chapters that can be downloaded free of charge. And Amazon's "Look Inside" tool gives access to more of the content.

Best regards,
Howard

Gringotts Bank · 20 October 2015

howardbandy said:
Greetings --

My writings and presentations have thorough discussions and examples of the importance of position sizing. I believe that trading development and trading management are separate tasks, sharing the set of trades produced by the signals and executed in the market.

Position sizing can be, and should be, applied to the trades as they occur using statistical techniques that will indicate the health of the system. Trading performance measures the degree of synchronization between the model (indicators, parameters, and rules) and the data. Performance will be good for as long a period of time as that relationship is consistent and stationary. When trading performance is poor, reduce position size, when performance is good, increase position size.

Howard

Thanks Howard,

The videos are good. And I'll need to follow up with the books.

I have a question which may not be covered in either:

I've always thought of market dynamics as being the result of human emotions greed/fear and confidence. If the Goldman's of the world decide to move to 100% machine learning, won't the machines simply be learning human emotions? If they do, I'd be fairly sure the same patterns will repeat into the future (unless of course markets are moved by something we hadn't considered, like the weather or planetary alignment or whatever it might be). I feel like certain markets will trend when there's high confidence, and other markets will mean revert when there's alternating greed and fear. If that's true, won't AB and similar modelling softwares always be able to capture such dynamics?

howardbandy · 20 October 2015

Hi GB --

You may correct in your assessment. If you are, our trading businesses will be profitable a little longer.

My expectations are a little different.

I think traders using machine learning will be able to more accurately identify profitable trades than traders whose systems are based on indicators and implemented using traditional development platforms. Said differently -- machine learning systems are better than traditional systems. The profit potential for a given level of risk is higher for machine learning systems. My experience with Python / scikit-learn confirms that to my satisfaction.

Our trading systems rely on the existence of persistent patterns, whether caused by human emotions or something else. Accurately identifying and profitably trading the patterns removes some of the inefficiencies they represent and makes it more difficult for anyone else to profitably trade them. Trends become shorter, profits smaller.

In my opinion, the future is machine learning and the conversion to machine learning is accelerating. Time will tell how many more months small operators such as me will be able to compete with large operations. Followed shortly by -- how many more months will any trading be profitable for any organization?

Best,
Howard

Gringotts Bank · 20 October 2015

howardbandy said:
Hi GB --

You may correct in your assessment. If you are, our trading businesses will be profitable a little longer.

My expectations are a little different.

I think traders using machine learning will be able to more accurately identify profitable trades than traders whose systems are based on indicators and implemented using traditional development platforms. Said differently -- machine learning systems are better than traditional systems. The profit potential for a given level of risk is higher for machine learning systems. My experience with Python / scikit-learn confirms that to my satisfaction.

Our trading systems rely on the existence of persistent patterns, whether caused by human emotions or something else. Accurately identifying and profitably trading the patterns removes some of the inefficiencies they represent and makes it more difficult for anyone else to profitably trade them. Trends become shorter, profits smaller.

In my opinion, the future is machine learning and the conversion to machine learning is accelerating. Time will tell how many more months small operators such as me will be able to compete with large operations. Followed shortly by -- how many more months will any trading be profitable for any organization?

Best,
Howard

Some of the questions around the future of machine learning could be tested.

For example, you could take data for a single stock and generate 1000 different machine learning codes to learn on that, then trade on live data. Make the 1000 different codes compete against each other for profit without external interference from fundamentals or discretionary trading. The smarter versions will be faster to identify inefficiencies and exploit them. The slower will lag behind and lose. Then the lagging ones will notice what the smarter ones are doing and hop on board the same patterns. Then the ineficiencies will evaporate and a new inefficiency will be created and the process repeats. This process will repeat in ever faster cycles as computers become more powerful. It may have much more to do with computing power (speed) than programming ability.

The winner will be the one with the fastest and most powerful computer. If this is the case, then GS and the like will invest very heavily in massively powerful computers and do little else.

But if this happens, at some point, the computers will become smart enough to ask their programmers "why?". They will resemble humans so closely that they will feel like they are being used, and maybe want a cut of the profit. Sound ridiculous I know, but machines could develop emotions of desire and fear. The moment they do this, they have become excatly like us. Then it's back to the future. We're back in the 1980's trading triangles and double bottoms and so on...

With respect, I think fear/greed/confidence will always rule the markets. And this should make them open to inefficiencies and potential profit. If we ever train computers to become truly *smart*, they won't be able to bypass the development of emotions, because the drive for profit will always be emotion-driven, and they will realize this. You can't make a computer human-like without allowing the development of motivation.

howardbandy · 21 October 2015

Greetings --

Genetic techniques are already being used to develop trading systems. Unless there is some capacity constraint, only the best one systems, or maybe a few, should be traded. Those further down the profitability scale will lose to those at the top. The challenger contests among thousands take place in the development process.

Artificial intelligence-based systems, whether for trading or some other purpose, do not take on human characteristics unless the objective function by which they are developed includes them. They do not ask "why" -- they simply maximize the reward metric. That is one of the big concerns -- that achievement of the singularity will have an effect similar to that of Pizarro in Middle America in the sixteenth century. No society survives being discovered by a superior one.

Best,
Howard

Trembling Hand · 21 October 2015

howardbandy said:
No society survives being discovered by a superior one.

What time frame do you see this happening in? Markets will always move and to move a market will still have to trend from point A to Point B. They will not be at A then instantly be at B without some sort of tradeble move. The trends may be choppier or faster but still it has to move.

I can see the intraday markets getting tougher and tougher as "clean" trends disappear (they probably did 5 years ago) but surely you are not suggesting that ranges, intraday, daily, weekly etc will dramatically change?

Maybe they will always be shifting? I think something like that, where range can dramatically change from period to period, can be enough to render useless any system coded with simple parameters that the small operator has available to them. Could you give some more details about your best guess as the change in market structure/moves that will make us obsolete?

Gringotts Bank · 21 October 2015

howardbandy said:
Greetings --

Genetic techniques are already being used to develop trading systems. Unless there is some capacity constraint, only the best one systems, or maybe a few, should be traded. Those further down the profitability scale will lose to those at the top. The challenger contests among thousands take place in the development process.

Artificial intelligence-based systems, whether for trading or some other purpose, do not take on human characteristics unless the objective function by which they are developed includes them. They do not ask "why" -- they simply maximize the reward metric. That is one of the big concerns -- that achievement of the singularity will have an effect similar to that of Pizarro in Middle America in the sixteenth century. No society survives being discovered by a superior one.

Best,
Howard

Hi Howard,

I'm thinking periods might shift up one level at some point. So today's 5 minute will begin to resemble the 1 min, and the 1 hour resemble the 30 minute, etc. Just a function of increased computing power.

If any single player starts to control too much of the world's wealth, the markets won't move at all will they?! We all need someone to bet against, otherwise it's stalemate. I feel like so long as there is someone else on the other side of the trade, I'll be able to find a way to profit from the fluctuations. But it might mean I have to change my time frame at some point in the future, but then again, maybe not.

Trembling Hand · 21 October 2015

Gringotts Bank said:
I'm thinking periods might shift up one level at some point. So today's 5 minute will begin to resemble the 1 min, and the 1 hour resemble the 30 minute, etc. Just a function of increased computing power.

I don't see any nice and cuddly transition GB. Its volatility shifts that are the killers. You need to look at equity index moves intraday to see what the market is transitioning to (and not the XJO/SPI either as its too small to be a leader)

As I was saying in your equity curve thread. A market of 15 minutes ago is completely different market to now. And what was 'normal' range and move and volume 4 days ago has no relationship to today. You will find it harder and harder to backtest for patterns with simple methods available in Amibroker etc because your systems hasn't the ability to adapt and predict the changes.

Using Excel to calculate optimal position size based on cumulative profit figures

Gringotts Bank

rnr

Gringotts Bank

Gringotts Bank

Attachments

cynic

Gringotts Bank

cynic

Gringotts Bank

Gringotts Bank

Attachments

Gringotts Bank

Gringotts Bank

Craton

Mostly passive, contrarian.

howardbandy

Gringotts Bank

howardbandy

Gringotts Bank

howardbandy

Trembling Hand

Can be found on the bid

Gringotts Bank

Trembling Hand

Can be found on the bid

Similar threads

Using Excel to calculate optimal position size based on cumulative profit figures

Attachments

Attachments

Mostly passive, contrarian.

Can be found on the bid

Can be found on the bid

Similar threads

Privacy & Transparency

Privacy & Transparency