Making a Matrix with Rounded Numbers of Contracts to Buy

Malthus101

New Member
Joined
Jan 23, 2017
Messages
46
Office Version
  1. 2016
Platform
  1. Windows
Howdy!

So... trading of futures contracts - /MES in this case (micro S&P futures)

Tick value is $1.25 - 1 contract is $5 (4 ticks per contract)

Max risk is dynamic value but lets call it $50.

So I need to create an "at a glance" matrix that will tell me how many contracts to buy when the stop/risk, is a certain number of ticks.

So for example, if the stop/risk was 40 ticks away ($1.25 x 40 = $50) then I could buy 1 contract safely.

If the stop/risk was 80 ticks away ($1.25 x 80 = $100) then I could not buy any contracts safely, so could not enter the trade.

If the stop/risk was 20 ticks away ($1.25 x 20 = $25) then I could buy 2 contracts safely and still only lose $50 if stopped out.


So... I already have one sheet programmed to make calculations for correct position size, that I must manually enter the data for each trade on.

However, this is time consuming in a fast moving environment.

I want to create another sheet, that references the first sheet, and presents the information as a simple matrix.

Increments of 5 would work best I feel.

10 ticks, 15 ticks, 20 ticks etc. This would be column A.

Column B would be for 0 contracts

Column C would be for 1 contracts.

Column D would be for 2 contracts.

Continue this to a maximum of 10 contracts.

The matrix would simply be colored cells so I can see what the max number of contacts I could buy for the number of ticks/risk for the trade.


So I need a formula in each cell that calculates number of ticks x the tick value = "number". If that number is 50, it will give me a green box under "1 contract"

If the number is say 75 (too much risk) it would turn the cell red in Column C (0 contracts allowed)

If the number were say 25, it would turn the 25 row and column D (2 contracts) cell green.


To make matters even more complicated, I am OK with it rounding the numbers up (absorb a little more risk) but I would like it to be within say a 20% range of the risk amount ($50)

So if the potential loss was $60, I am OK with that. But if it was $65, that's now too much.


Being not only bad at Excel but also terrible at math, I hope I have explained this clearly enough!

Thanks!
 

Attachments

  • matrix example.JPG
    matrix example.JPG
    112.7 KB · Views: 25

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top