Hi,
I tried to embed a pic of the spreadsheet, but it does not appear to work, the url is shown below.
Basically...I'm want to track my Forex paper trading.
Cells B3:M3 are the headers for the things I want to trade.
Each row below that (5 downwards) will represent a buy or sell traded.
My setup is that I enter a date in column A then;
- if I'm buying I enter a positive number in cells B:M on the same row; or
- if I'm selling I enter a negative number in the cells B:M on the same row.
these number represents the number of units I buy/sell. A running total of this units are kept in column N (eventually a running total will be created for each)
Also, when I buy, it records when I bought in column O - this repeats while the outstanding number of units is > 0
Finally, I specify whether the buy is a "long" L or "short" S purchase.
My problem is...
When I sell I need the spreadsheet to...
Check the buy date (column O), scan column A for the buy date; then
Check the same column the sell was made in the matching date.
When column A and B:M contain the buy date and a positive buy number in the same column as the sell number - get the value in column P and put it into column R on the sell row.
What I know;
MAX($B9:$M9)
- will tell me what the highest number is between B and M (only a buy or sell for 1 type of trade occurs per line)
=MATCH(MAX($B9:$M9),$B9:$M9,0)
- will tell me which column the trade occurs in.
=MATCH(O9,A:A,1)
- will match the purchase date and tell me a row (however I do not know how to match multiple similar dates).
I'm thinking a SHIFT+CTRL+ENTER formula where column A and B:M match, if they do, get value from column P.
But I'm getting a headache, any ideas?
Regards
Michael
I tried to embed a pic of the spreadsheet, but it does not appear to work, the url is shown below.
Basically...I'm want to track my Forex paper trading.
Cells B3:M3 are the headers for the things I want to trade.
Each row below that (5 downwards) will represent a buy or sell traded.
My setup is that I enter a date in column A then;
- if I'm buying I enter a positive number in cells B:M on the same row; or
- if I'm selling I enter a negative number in the cells B:M on the same row.
these number represents the number of units I buy/sell. A running total of this units are kept in column N (eventually a running total will be created for each)
Also, when I buy, it records when I bought in column O - this repeats while the outstanding number of units is > 0
Finally, I specify whether the buy is a "long" L or "short" S purchase.
My problem is...
When I sell I need the spreadsheet to...
Check the buy date (column O), scan column A for the buy date; then
Check the same column the sell was made in the matching date.
When column A and B:M contain the buy date and a positive buy number in the same column as the sell number - get the value in column P and put it into column R on the sell row.
What I know;
MAX($B9:$M9)
- will tell me what the highest number is between B and M (only a buy or sell for 1 type of trade occurs per line)
=MATCH(MAX($B9:$M9),$B9:$M9,0)
- will tell me which column the trade occurs in.
=MATCH(O9,A:A,1)
- will match the purchase date and tell me a row (however I do not know how to match multiple similar dates).
I'm thinking a SHIFT+CTRL+ENTER formula where column A and B:M match, if they do, get value from column P.
But I'm getting a headache, any ideas?
Regards
Michael