How do i calculate this stocks trading excel?

**nederay** · 04-30-2013, 02:12 AM

Hi,

I would have a sell order match with a buy order, calculate the profit and loss for that particular trade
problems comes when there are multiple buy order and not enough sell order to square off the trade

need to write a vba/marco to have the sell order check for the earlier buy order and square off accordingly.
How should i write the formula ?

attached excel spreadsheet
spreadsheet has more details

Thank you for your time

**dilipandey** · 04-30-2013, 06:19 AM

Hi nederay,

Try using the below formula:-

Formula:

Please Login or Register  to view this content.

Example.xlsx

Regards,
DILIPandey
<click on below * if this helps>

**nederay** · 05-03-2013, 07:50 AM

Hi DILIPandey,

Thank you for your time and help.

however if i remove the earlier buy trade,the one with 11 April the formula would not be able to count the profit.
and if i also need to take hold of the remaining holding, how do i calculate ?

thank you

**dilipandey** · 05-04-2013, 03:25 AM

Formula in D33 is considering only rows 21 to 26 and 11 april's data is row no.12 so no effect of that.

Regarding holding calculation, I need to see what result you are looking for and I'll try to obtain the same using formula. thanks.

Regards,
DILIPandey
<click on below * if this helps>