For stock trade profile, calculate the new positions and add new stock to the new row

Hi, sunnyray.

To get straight to the point, what you want to do is to add the new trade position to the "Existing Position", right?

Thank you for your reply, Jujin!

For stocks like BABA/ AAPL I have existing position, I would like to recalculate the new average price and number of shares.
FOr sotcks like AMZN I have no existing position AMZN, I would like to create a new row for the new stock like AMZN.

On my example workbook, spreadsheet "New Trade" means the trades I would like to add to the spreadsheet "Existing Position", and the spreadsheet "Existing P + New Trade" mean the final result I need. I wish to add a button to get the new result automatically.

I am not certain, but the logic could be
1.) look up the the Stock Code (Cell A2 on spreadsheet "New Trade") on spreadsheet "Existing position"
2.) If yes (Cell A2= BABA, which exists on the spreadsheet" Existing Position", then it will recalculate the new average price and number of shares.
3.)If no (Cell A7 = AMZN, then I would like to add a new row under the last row of the spreadsheet "Existing Position.

Your help will be really appreciated!

I think I can figure out what you want, but I'd like to ask you some more questions.

In "New Trade" sheet, do you enter the data manually and click the button to do the job (recalculating etc)?

If Stock Code is not found in "New Trade", Stock Code and Company Name will be copied from "New Trade" to "Existing Position", but how about Average Price or Long Positions? Where do they come from? Can they also be copied from "New Trade"?

Thank you for your help! Please see the colored reply below.

Originally Posted by yujin

I think I can figure out what you want, but I'd like to ask you some more questions.

In "New Trade" sheet, do you enter the data manually and click the button to do the job (recalculating etc)?
Yes, I would enter the data manually , and a macro the do the job.

If Stock Code is not found in "New Trade", Stock Code and Company Name will be copied from "New Trade" to "Existing Position", but how about Average Price or Long Positions? Where do they come from? Can they also be copied from "New Trade"?
For the stock code cannot be found in "New Trade", they will be just copied from the "New Trade"by the macro.

The last thing I want to know is how to recalculate the average prices in "Existing Position".
Will you show me the formula to calculate them?

Originally Posted by yujin

The last thing I want to know is how to recalculate the average prices in "Existing Position".
Will you show me the formula to calculate them?

It will be like
1.) 'New Trade!' Cell(Ai) lookup with 'Existing Position!' Cell(Ai) [ for example, BABA = 'New Trade!' Cell(A2) = 'Existing Position!' Cell(A2)

2.) [('New Trade!' Cell(C2) * 'New Trade!' Cell(E2)) +('Existing Position!' Cell(C2)*'Existing Position!' Cell(D2)]/('New Trade!' Cell(E2)+'Existing Position!' Cell(D2))---------->[(174*(-25))+(150*500)]/(500+(-25))

3.) then loop it for every row on "New Trade"

P.S. when the number of shares are in the column "Short Position" which should be negative, and I can just fix it by adding one more column with formula [=-(cell), so basically ignore the negative sign.

Thanks a lot Yujin.
I just cant find something similar online to be an example to amend, and I don't know how to build a macro like this.

So, "Average Price" and "Long/Short Positions" in "Existing Position" are fixed values.
And values in "Existing P + New Trade" are to be recalculated.

Is my understanding correct?

Originally Posted by yujin

So, "Average Price" and "Long/Short Positions" in "Existing Position" are fixed values.
And values in "Existing P + New Trade" are to be recalculated.

Is my understanding correct?

The most ideal way is that Both "Average Price" and "Long/Short Positions" can be updated on Existing Position". But I am not sure if it will get error easily. Otherwise, I can just create another spreadsheet for it and make them be fixed value in "Existing Position"

Sorry to take some time to understand what you want.
But I've got to get it straight to write the code.

C2 cell in "Existing P + New Trade" sheet has the formula below.
=(((174*-25)+(169*50)+(172*-70)+(150*500))/(500-20-70+50))

Bold red numbers (500 and 150) are from C2 or D2 cell in "Existing Position" if I understand correctly.

But C2 and D2 in "Existing Position" has no formula but fixed value.
That's why I considered these values would be fixed.

Originally Posted by yujin

Sorry to take some time to understand what you want.
But I've got to get it straight to write the code.
Instead, I really appreciate your help Yujin.

C2 cell in "Existing P + New Trade" sheet has the formula below.
=(((174*-25)+(169*50)+(172*-70)+(150*500))/(500-20-70+50))

Bold red numbers (500 and 150) are from C2 or D2 cell in "Existing Position" if I understand correctly.

But C2 and D2 in "Existing Position" has no formula but fixed value.
That's why I considered these values would be fixed.

The spreadsheet "Existing P + New Trade" actually is the completed version of "Existing Position" after running macro.
"Existing Postion" is the raw/"pre edit" version. just like before and after.

Here's the code I've written. Maybe it's a little slow, I guess.
Try it and tell me if there's something wrong.

Please Login or Register  to view this content.

Really appreciate your help Jujin! It works very great, and now I have one more template to study! You are amazing man!!

You are welcome.
But the code might be insufficient for practical use, I guess.
Please feel free to ask if you need a hand.

Sure! I am still studying the code you provided. its really well organized!