Help With FIFO U P/L & R P/L on Multiple Stocks

Hello Gents.,

With all credits due to Excel Forum I managed to create a simple Stock Portfolio FIFO Template. It took me nearly a month of research to put something together but never got the FIFO P/L Calculations right. Until I read a post from Forum Expert kvsrinivasamurthy which I implemented it to my Sample.
https://www.excelforum.com/excel-for...-of-stock.html

I have entered simulated trades on Thinkorswimm platform to verify my results and they matched perfectly.

Having said this, I have one small problems that I would like to resolve if possible.

I am unable to input multiple Stock Symbols, I get bad results for obvious reasons. -See Table 1
Currently I have to have Separate Table Per Symbol to get it right. -See Table 2 and 3

It would be so much better if I could input all trades in one Table and get the right Results
In Column P - Q - R. of Table 1.

Column Y calculations are generated by the kvsrinivasamurthy's UDF Code.

I have Highlighted in Table 1 the desired results that were copied from Table2 and Table 3.

Hope it can be done, I personally have hit a wall.

Thank you for your Help
Cheers
ONP

hello KVS
Thank you for your reply.

I have attempted to explain the manual calculations as you requested. Its in the Sample, I hope the explanation makes sense, because often I even dont understand what I write.

I have also inserted an array formulas in the blue highlighted cells in column P that will produce the requested results if copied down. That formula was written By Forum Guru Bo_Ry. I have no Idea how to read that formula, but it may help you understand the calculating methods he has aproached. Althou Bo_Ry formula works, it slows down my excel program drastically when calculating a large Data Base.

The New improved Code you have provided Helps allot Thank you.

Thank you for your time and Help
Cheers
ONP

Hello KVS,
Just wanted to let you and everyone know that I managed to solve the FIFO problem thanks to you.
Every trade now calculates with FIFO method including the PL.
Thank you Again for your Help

I have attached a copy of the final FIFO working Portfolio Tracker.
Hope many will benefit from this as I know many are looking for such.

I will Mark this thread Solved
Cheers
ONP

Hi Celsius, Got your message.
Basically you want to log in short and long positions in the template.
Give me a little time and ill see what i can do. Maybe someone else in the forum resolves the issue before me

FYI the new template i have is a built up version of what was uploaded here. Its rather complex. It logs in Puts Calls Long and short positions. Of course all of the formulas ware the results of Excel Forum Help. I was tired of manually inputting data so now I just copy and paste the downloaded transactions from my brokers platform (TOS - from TD Ameritrade) and the template calculates everything i need. I have been working on it over 3 months and still making small improvements. Thus far all the excel calculations match the Brokers Platform to the penny.

Cheers
ONP

Originally Posted by onp

Basically you want to log in short and long positions in the template. P

Yes, you are right, onp.

When you are margin trading, you can start selling -shares, cryptos or whatever- first or reached to certain point, being short but I see when the Cumulative Shares starts or reaches to the point that turns to negative, it doesn't work.

Thanks for your fast answer and feedback, mate.

Hi ONP,

Did you have any luck make thet sheet work for short positions?

Regards
Bhav

Hi All. I just want to thank ONP for this sensational spreadsheet. It's exactly what I was looking for.
Very much appreciated!!!
Thank you for sharing if for free with the rest of the world.

Hello - what is the FIFO code formula in column P?

Superb worksheet. very helpful. Is it possible to bring the holding period of each shares in the excel