Scan multiple columns for last match to column entry, then perform calculations

[Rick R]

First time I have been in a forum, not very experienced with excel, please bare with me.

I am trying to create a stock trading journal with calculations in it.


Currently I am using

=IF(H35="Stock",Q35/V35,P35*V35/V35) in column W and column AA

to give me the cost basis of a stock purchase designated by the word ""stock"", otherwise if a trade and not a purchase the formula gives the per share cost or profit earned by the trade .

Currently the spreadsheet is doing the calculation correctly in the green columns W & AA, but I need the formula to also show the sum of the initial cost basis at purchase and then with the increasing or decreasing cost basis of each following trade. I have manually showed the results I am looking for in yellow columns AF and AI I have been manually entering the original cost basis in same named column... not sure if that column could be deleted or needed to work out calculation or renamed and used to keep track of what I want manually.


If possible I would also like to the spreadsheet to do this automatically each time I enter a trade

when I enter a new trade the spreadsheet will look back to match up the entered stock symbol (column E) with the last trade for each lot in the trade (maybe by matching the stock symbol, matching purchase date and lot number) and then keep a running total, (as talked about earlier) adjusting the last matching lot number's cost basis by the net premium per share and putting that/those total(s) in the most recent row (Stocks Current Cost Basis Lot) or the column next to it if another column is needed to do the calculation... row by row trade by trade.

I have about 950 rows of trades and I would like to apply it from the bottom up

Can this be done?
Help if only pointing a direction I should research would be appreciated...

Please ask me any question if I was not exact or clear.

Thanks,

Rick

[FDibbins]

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[Rick R]

Thanks, file added...

[FDibbins]

Did you include a few sample answers in there?

[Rick R]

Sample answers? Not sure what you mean, sorry my first experience with a forum.
I did include an example of a formula I am using but it does not do what I want.

Part of the problem is I don't even know what type of formula to use, if then, lookup, match, vba, or what.
It's not that I understand those, but if I knew I could google it and try to figure out.

Was hoping someone could give me the formula or at least tell me what to look for...

If I need to put something more then I already have please let me know.
I see there is a paid service, not having any experience here I don't know what to expect as far as help.
Let me know if I would be better off seeking help there.

Thanks,

R-

[oeldere]

@Rick R

Where did you add the formula's that don't work.

What would you like to achieve

Add manualy the excpected result in your file and refer to the related cells with e.g. a color.

The sheet is clear to you, since you work with it, but it is totaly new to forummember.

By adding the result, forummember who are not in the same business, can also help you.

[Rick R]

Thanks for advice, will update