B
Bruce
Hi,
I’m doing some portfolio modelling in Excel and need some advice on some of
my formulas. I have a list of stock market trades and I what to model
different profit scenarios based on setting a limit on the number of trades
in the portfolio, and then calculating a running total of the accepted trades
based on this limit. I’ll first explain what I have, and secondly what I
would like to do.
1) What I have
My Worksheet is arranged as follows in the following columns.
A – Security (Stock name)
B – Cost ($ amount to buy)
C – Sell ($ amount sold)
D – Profit ($ profit i.e. Sell – Cost)
E – Entry Date (Date purchased)
F – Exit Date (Date Sold)
G – Duration (Trade Duration in days)
Data is sorted ascending based on the Exit Date which defines when the trade
is complete
Hope this gives a picture of my data
2) What I would like to do
a) The portfolio can only take 5 trades at a time based on combinations of
overlapping Entry date and Exit Dates. Once 5 trades are accepted at any
given date, all others are rejected until one or more are closed. I think I
some kind of counter that compares the Exit date of the current row to the
overlapping dates of the preceding….Any thoughts
b) I wish to sum the profit (D) for accepted trades in a) in col H. This
will give a running total / balance.
Appreciate any thoughts on this or other ways to approach. I have a few
ideas, just can’t seem to get it to work.
Regards,
Bruce
I’m doing some portfolio modelling in Excel and need some advice on some of
my formulas. I have a list of stock market trades and I what to model
different profit scenarios based on setting a limit on the number of trades
in the portfolio, and then calculating a running total of the accepted trades
based on this limit. I’ll first explain what I have, and secondly what I
would like to do.
1) What I have
My Worksheet is arranged as follows in the following columns.
A – Security (Stock name)
B – Cost ($ amount to buy)
C – Sell ($ amount sold)
D – Profit ($ profit i.e. Sell – Cost)
E – Entry Date (Date purchased)
F – Exit Date (Date Sold)
G – Duration (Trade Duration in days)
Data is sorted ascending based on the Exit Date which defines when the trade
is complete
Hope this gives a picture of my data
2) What I would like to do
a) The portfolio can only take 5 trades at a time based on combinations of
overlapping Entry date and Exit Dates. Once 5 trades are accepted at any
given date, all others are rejected until one or more are closed. I think I
some kind of counter that compares the Exit date of the current row to the
overlapping dates of the preceding….Any thoughts
b) I wish to sum the profit (D) for accepted trades in a) in col H. This
will give a running total / balance.
Appreciate any thoughts on this or other ways to approach. I have a few
ideas, just can’t seem to get it to work.
Regards,
Bruce