First time I have been in a forum and not very experienced with excel, sorry in advance of any errors I make.
Goggle has got me this far but I am stuck. I am trying to create a stock trading journal that has some calculations in it. I am sure it's no work of art but again, not even basic user here.
I would Like to have excel scan the same column, backwards in time for the first exact match of a stock symbol (Whatever symbol I last entered) and confirm its a match by a date in a second column, same row. If its a match then perform a calculation in another column.
See link for picture of my spreadsheet, I removed many of the non important columns.
Downloading from DiskStation
Currently I am using =IF(D6="Stock",H6/J6,G6*J6/E6) (in the stocks current cost basis columns (K) to give me the cost basis if its a purchase designated by the word stock, otherwise give me the per share cost or profit earned by the trade.
There can be multiple columns based on there being more then one lot of stocks currently open.
What I would really like to have happen is to have it do what it's doing now only to match up the trades and keep a running total of the cost basis increasing or decreasing in column (K) depending on the lot... look back to the date each stock was purchased and then match up the trades based on the stock symbol and purchase date... then reduce the last cost basis by the net premium per share (Column G) and put that total in the (Stocks Current Cost Basis Lot(K) or the column next to it (L) if another column is needed to do the calculation... row by row/ trade by trade.
Can this be done? How?
Please ask me any question if I was not exact or clear.
Thanks,
Rick
Goggle has got me this far but I am stuck. I am trying to create a stock trading journal that has some calculations in it. I am sure it's no work of art but again, not even basic user here.
I would Like to have excel scan the same column, backwards in time for the first exact match of a stock symbol (Whatever symbol I last entered) and confirm its a match by a date in a second column, same row. If its a match then perform a calculation in another column.
See link for picture of my spreadsheet, I removed many of the non important columns.
Downloading from DiskStation
Currently I am using =IF(D6="Stock",H6/J6,G6*J6/E6) (in the stocks current cost basis columns (K) to give me the cost basis if its a purchase designated by the word stock, otherwise give me the per share cost or profit earned by the trade.
There can be multiple columns based on there being more then one lot of stocks currently open.
What I would really like to have happen is to have it do what it's doing now only to match up the trades and keep a running total of the cost basis increasing or decreasing in column (K) depending on the lot... look back to the date each stock was purchased and then match up the trades based on the stock symbol and purchase date... then reduce the last cost basis by the net premium per share (Column G) and put that total in the (Stocks Current Cost Basis Lot(K) or the column next to it (L) if another column is needed to do the calculation... row by row/ trade by trade.
Can this be done? How?
Please ask me any question if I was not exact or clear.
Thanks,
Rick
