Excel: Calculating adding to Current position

[BasicBobby]

Im trying to create an excel sheet for an equation that calculates the number of shares i am able to add to my current position.
So, If i have 288 shares at a $6.94 avg, with a stop loss of 6.59, this would mean i am risking $100 on the trade. But, if i want to add at $7.10, with a new stop at $6.85, how many shares can i add, if i want to keep the same $100 risk, but now moving my stop to $6.85. Is it possible to create a table in excel to where i can input numbers and automatically calculate?

 

[halcyonguy]

Im trying to create an excel sheet for an equation that calculates the number of shares i am able to add to my current position.
So, If i have 288 shares at a $6.94 avg, with a stop loss of 6.59, this would mean i am risking $100 on the trade. But, if i want to add at $7.10, with a new stop at $6.85, how many shares can i add, if i want to keep the same $100 risk, but now moving my stop to $6.85. Is it possible to create a table in excel to where i can input numbers and automatically calculate?

my answer in this post was wrong.
see post #8 below for correct answer
thanks Jonessw1

 

[halcyonguy]

i'm doubting my math, i think i left out something.....
experimenting with a tos study

 

[BasicBobby]

Appreciate the help, halcyonguy.

 

[Jonessw1]

i don't know if i will get around to posting an excel file, so here are some notes to help.

risk = ( buy - stop) * shares
risk = (6.94 - 6.59) * 288 = $100.80

but , what if i buy more...
how many shares to add, to maintain a set risk amount , $100 ??


buy at 7.10
qty is unknown
stop at 6.85


risk = ( buy - stop ) * shares
risk = ( 7.10 - 6.85 ) * shares = 100
risk = 0.25 * shares = 100

IF I am understanding the question correctly, I think you have forgotten to subtract the effect of moving the stop on the initial shares from the initial risk if ALL shares now have a stop of $6.85.

288 Shares @ $6.94
New Stop of $6.85
$0.09 Risk Per Share
$25.92 of Total Risk at New Stop
$100.80 - $25.92 = $74.88 of Original Risk Remaining for new shares

New Share Price = $7.10
Stop of $6.85
$0.25 Risk Per Share
$74.88 of Original Risk Remaining / $0.25 Risk Per Share = 300 New Shares Allowable

Original 288 @ $6.94
New 300 @ $7.10
588 Shares @ $7.02 Average

 

[BasicBobby]

thanks Jonessw1

 

[halcyonguy]

IF I am understanding the question correctly, I think you have forgotten to subtract the effect of moving the stop on the initial shares from the initial risk if ALL shares now have a stop of $6.85.

288 Shares @ $6.94
New Stop of $6.85
$0.09 Risk Per Share
$25.92 of Total Risk at New Stop
$100.80 - $25.92 = $74.88 of Original Risk Remaining for new shares

New Share Price = $7.10
Stop of $6.85
$0.25 Risk Per Share
$74.88 of Original Risk Remaining / $0.25 Risk Per Share = 300 New Shares Allowable

Original 288 @ $6.94
New 300 @ $7.10
588 Shares @ $7.02 Average

thank you @Jonessw1
that makes sense
i have your ideas added to my spreadsheet.
will incorporate into a study

i would just change,
if starting with a desired risk amount, to round down for the share quantity.
$100 desired risk / ( 6.94-6.59) = 285 shares

then on risk2 , to start with the desired risk of 100 , not what was calculated for buy1

 

[halcyonguy]

IF I am understanding the question correctly, I think you have forgotten to subtract the effect of moving the stop on the initial shares from the initial risk if ALL shares now have a stop of $6.85.

288 Shares @ $6.94
New Stop of $6.85
$0.09 Risk Per Share
$25.92 of Total Risk at New Stop
$100.80 - $25.92 = $74.88 of Original Risk Remaining for new shares

New Share Price = $7.10
Stop of $6.85
$0.25 Risk Per Share
$74.88 of Original Risk Remaining / $0.25 Risk Per Share = 300 New Shares Allowable

Original 288 @ $6.94
New 300 @ $7.10
588 Shares @ $7.02 Average

determine quantity of shares for trade 1, based on buy price, stop, and max risk.
determine quantity of shares for trade 2, based on a higher stop, and the available risk dollars.

enter desired risk amount (default $100)

trade 1,
enter a buy price and stop price
calculate quantity of shares to buy


trade 2,
figure in a 2nd trade, and see how it affects share quantity.
buy price, trade 2
..can enter another buy price,
..or choose 0 to have it use the current price.

stop, trade 2
..can enter another stop price,
..or choose 0 to have it use the trade 1 spread.


draw lines at buy1 and buy2 prices
draw bubbles with stats
display labels with stats


i laid out the formulas in excel, then created them in thinkscript.

i used symbol, ACET for testing, as it was close in price to the prices in request.

# calc_future_shares_same_risk_01

#https://usethinkscript.com/threads/excel-calculating-adding-to-current-position.14311/
#Excel: Calculating adding to Current position.

#------------------------

# determine a desired risk level, then calculate quantity of shares to buy
# figure in a 2nd buy, with a new stop
# calculate an adjusted risk for trade 1, and available risk dollars, to determine shares2

# test symbol - ACET

def na = double.nan;
def bn = barnumber();

def clsx = if isnan(close) then clsx[1] else close;

#-----------------------------
# trade 1

input desired_risk_dollars = 100;
input buy1 = 0.0;
input stop1 = 0.0;

#input buy1 = 6.94;
#input stop1 = 6.59;
def spread1 = (buy1 - stop1);

def shares1 = floor(desired_risk_dollars / (buy1 - stop1));
def risk1 = round(shares1 * (buy1 - stop1), 2);
def cost1 = round(buy1 * shares1, 2);

# --------------------------------
 # trade2

#input buy2 = 0.0;
#input stop2 = 0.0;
input buy2 = 12.9;
input stop2 = 12.4;

#hint buy2: Enter buy price for trade #2.\n or 0 to track with current price.
#hint stop2: Enter a stop price.\n or 0 for using the same spread as in trade 1.

def buy2x = if buy2 == 0 then clsx else buy2;
def stop2x = if stop2 == 0 then (buy2x - (buy1 - stop1)) else stop2;
def spread2 = buy2x - stop2x;

def spread1_adj = (buy1 - stop2x);
def risk1_adj = shares1 * (buy1 - stop2x);

def risk2 = (desired_risk_dollars - risk1_adj);
def shares2 = floor(risk2 / (buy2x - stop2x));
def cost2 = (buy2x * shares2);

#--------------------
# totals

def total_cost = (cost1 + cost2);
def total_shares = (shares1 + shares2);
def avg_share_cost = (total_cost / total_shares);

#--------------------------------------------

# define a set of bars, after last bar, for buy lines
def lineoff = 1;
def linelen = 20;
def linex = (!isnan(close[(lineoff + linelen)]) and isnan(close));
def buboff = 3;
input show_bubbles = yes;
def bubx = show_bubbles and buy1 > 0 and (!isnan(close[(buboff+1)]) and isnan(close[buboff]));

#--------------------------
# trade1 - buy line

plot zl0 = if linex then buy1 else na;
zl0.SetDefaultColor(Color.green);
#zl0.setlineweight(1);
#zl0.hidebubble();

addchartbubble(bubx, buy1,
 shares1 + " @ " + asdollars(buy1) + " / " + asdollars(stop1) + "\n" +
 "risk " + asdollars(risk1) + "     buy1\n" +
 "cost  " + asdollars(cost1)
 , color.green, no);

#--------------------------
# trade2 - buy line

#  buy more price , default is current price
plot zl1 = if linex then buy2x else na;
zl1.SetDefaultColor(Color.yellow);
#zl1.setlineweight(1);
#zl1.hidebubble();

addchartbubble(bubx, buy2x,
 shares2 + " @ " + asdollars(buy2x) + " / " + asdollars(stop2x) + "\n" +
 "risk " + asdollars(risk2)  + "     buy2\n" +
 "cost  " + asdollars(cost2)
 , color.yellow, no);

#-------------------------

addchartbubble(bubx, buy1,
"spread1_adj  " + spread1_adj + "\n" +
"risk1_adj  " + asdollars(risk1_adj)
 , color.yellow, yes);

addchartbubble(bubx, buy2x,
 "total cost " + asdollars(total_cost)  + "\n" +
 "total shares " + total_shares + "\n" +
 "avg share cost " + asdollars(avg_share_cost)
 , color.magenta, yes);

#-------------------------

input show_labels = yes;
def labels = show_labels and buy1 > 0;
addlabel(labels, " ", color.black);
addlabel(labels, "max risk $: " + desired_risk_dollars, color.yellow);

addlabel(labels, " ", color.black);
addlabel(labels, "Buy1:", color.green);
addlabel(labels, asdollars(buy1) + " / " + asdollars(stop1), color.green);
addlabel(labels, "shares1: " + shares1, color.green);
addlabel(labels, "risk1: " + asdollars(risk1), color.green);
addlabel(labels, "cost1: " + asdollars(cost1), color.green);

addlabel(labels, " ", color.black);
addlabel(labels, "Buy2:", color.yellow);
addlabel(labels, asdollars(buy2x) + " / " + asdollars(stop2x), color.yellow);
addlabel(labels, "shares1: " + shares2, color.yellow);
addlabel(labels, "risk1: " + asdollars(risk2), color.yellow);
addlabel(labels, "cost1: " + asdollars(cost2), color.yellow);


addlabel(labels, " ", color.black);
addlabel(labels, "total cost " + asdollars(total_cost), color.magenta);
addlabel(labels, "total shares " + total_shares, color.magenta);
addlabel(labels, "avg share cost " + asdollars(avg_share_cost), color.magenta);

addlabel(labels, " ", color.black);
#

trade 2 , specify buy price and stop price

trade 2 ,
buy price , follow current price (set to 0)
stop price , use trade 1 spread (set to 0)