Microsoft Excel Q&A
- Thread starter Uncle Festivus
- Start date
- Joined
- 9 June 2011
- Posts
- 1,926
- Reactions
- 480
If i have an excel spreadsheet of all trades performed based on a set of rules, is there a function I can use to allow me to only be 'in' 20 trades at a time?
I have the entry and exit date of each trade.
Effectively it needs to be something along the lines of 'If I have 20 open trades then the next trade must be ignored' or 'only enter trade if 19 trades or less are open'
I have the entry and exit date of each trade.
Effectively it needs to be something along the lines of 'If I have 20 open trades then the next trade must be ignored' or 'only enter trade if 19 trades or less are open'
- Joined
- 9 June 2011
- Posts
- 1,926
- Reactions
- 480
- Joined
- 1 May 2007
- Posts
- 2,904
- Reactions
- 52
Insert new column K
=COUNTIF($J$7:J7,">"&I7)
and drag down
This will count how many exit dates are above the current entry date (ie how many stocks not exited) = open positions.
You can then edit entries/exits accordingly
- Joined
- 9 June 2011
- Posts
- 1,926
- Reactions
- 480
Outstanding.
pixel
DIY Trader
- Joined
- 3 February 2010
- Posts
- 5,359
- Reactions
- 344
I find array formulas even easier. No need for a new column, only one cell.
Type =SUM(IF($J$7:$J$9999>$I$7:$I$9999,1,0))
(if your table extends beyond row 9999, replace 9999 by a sufficiently large number.)
then, instead of hitting Enter, hold down Ctrl and Shift keys before hitting Enter.
That will turn the formula into an array formula, indicated by { } around it; the result will show the number of open trades in whichever cell you entered it.
Make sure, every time you edit an array formula, to confirm it by Ctrl-Shift-Enter; else you'll get an error.
I'm usually not too bad on excel but I'm stumped on this seemingly basic one. Trying to get a 3rd variable for a column chart (My PnL) showing whether I traded MHI or HSI that day, but it's giving me some kinky outcomes thus far. Anyone got some ideas? Or a template for a chart of PnL, date and product?
- Joined
- 1 May 2007
- Posts
- 2,904
- Reactions
- 52
Not exactly sure what you're asking, are you trading HSI and MHI?
I assume you copy paste data from IB?
skc
Goldmember
- Joined
- 12 August 2008
- Posts
- 8,277
- Reactions
- 329
Organise your data in 3 columns. Date, PnL of MHI and PnL of HSI.
Then plot column chart using the MHI and HSI as two data series, and date as column labels.
You can stacked the columns or have them side by side.
Yep, MHI mostly recently for a couple reasons.
And yep copy paste data from IB.
SKC, I appreciate that, have got that one on a diff spreadsheet but I'd really like to keep the one PnL chart for overall performance. For example, a label near the date saying whether it was MHI or HSI traded that day. If that makes sense?
So a cumulative PnL of my entire account labelling each day traded as either MHI or HSI and still showing returns on the Y axis and date on the x axis.
skc
Goldmember
- Joined
- 12 August 2008
- Posts
- 8,277
- Reactions
- 329
Do as I suggested. It will have both MHI and MSI data series on the same chart. It's the only way to do it. You can't have a different label for each column.
If you still don't understand throw your excel up here with enough example data and someone will be able to help you.
- Joined
- 12 January 2008
- Posts
- 7,169
- Reactions
- 17,744
- Joined
- 30 June 2007
- Posts
- 7,200
- Reactions
- 1,225
Hi,
I just want to calculate the difference (sell - buy) on a list of trades.
The list is of paired trades like so:
A B C D
1 Buy BHP $10000
2 Sell BHP $11000 D2 = C2-C1=$1000 profit**
3 Buy NAB $10000
4 Sell NAB $12000 D4 = .....?
How do I replicate this** function in D4 and every second line thereafter? I did it last year and can't recall the process.
Thanks.
EDIT: alternatively, if I drag the function down to include all cells, then I just need to delete or make every second cell=zero in that column. Any ideas on this please?
I just want to calculate the difference (sell - buy) on a list of trades.
The list is of paired trades like so:
A B C D
1 Buy BHP $10000
2 Sell BHP $11000 D2 = C2-C1=$1000 profit**
3 Buy NAB $10000
4 Sell NAB $12000 D4 = .....?
How do I replicate this** function in D4 and every second line thereafter? I did it last year and can't recall the process.
Thanks.
EDIT: alternatively, if I drag the function down to include all cells, then I just need to delete or make every second cell=zero in that column. Any ideas on this please?
skc
Goldmember
- Joined
- 12 August 2008
- Posts
- 8,277
- Reactions
- 329
Highlight cell D2 and D3, drag the lower right corner of the highlighted block down. It should fill automatically with one formula then one blank.
This is actually a really useful trick. Say if you have Trade ID# column that goes GB001. You can drag down the bottom right corner of this cell and it will fill down GB002, GB003 etc.
Country Lad
Off into the sunset
- Joined
- 11 July 2005
- Posts
- 1,555
- Reactions
- 1,491
The simplest way is to copy D2, cursor on D4, hold down Ctrl key, highlight the other cells you want the formula in, say D6, D8 etc, while holding down the Ctrl key then paste. There are other more complicated methods, this is the simplest.
Cheers
Country Lad
Cheers
Country Lad
- Joined
- 30 June 2007
- Posts
- 7,200
- Reactions
- 1,225
Thanks guys. Fantastic.
pixel
DIY Trader
- Joined
- 3 February 2010
- Posts
- 5,359
- Reactions
- 344
Another simple method is the use of a conditional formula, which you can then fill down.
instead of
=C2-C1
use
=if(A2="Buy","",C2-C1)
or
=if(A2="Sell",C2-C1,"")
This formula will only put the Profit into cell D2 if it's not a Buy (or definitely a Sell); otherwise, it will result in an empty cell. This formula can be filled down all the way from cell D2.
- Joined
- 30 June 2007
- Posts
- 7,200
- Reactions
- 1,225
Thanks again. Good to have alternative options.
pixel
DIY Trader
- Joined
- 3 February 2010
- Posts
- 5,359
- Reactions
- 344
I hope you noticed the correction:
I noticed only after posting that Buy or Sell was entered in column A
And if you're typing each transaction into columns A to C by hand, it may be better still to get a little fancier and just test for the first letter of column A.
=if(upper(left(a2,1))="S",C2-C1,"")
would only test the first letter (left(A2,1)); the "upper" around it would accept as a Sale anything that starts wit S or s.
