Hi !
Would appreciate some guidance on the following please:
I am trying to create an efficient and effective spreadsheet to help someone in manage their stock portfolio. I am trying to make it as simple and easy-to-use as possible.
Ideally, I would like to have a form or button based system that first asks 1- whether the user has bought or sold a stock
2- From a drop down menu select which account the buying or selling was in (there are about 8 accounts with coded names like H694JKD8 that can be troubling to keep on typing)
3. Select the date of activity (from a drop down calendar)
4. Enter the stock ticker, number of shares, and cost.
This is essentially what it will do but there is more that takes place beneath all this.
In an Excel spreadsheet, the tracking/portfolio headings of: "Account", "Date","Buy or Sell", "Cost", "Buy Commission", "Sell Commission", "Average cost", "Profit or Loss" etc. should be filled with the corresponding information that was input.
The part I especially am having trouble wrapping my head around is:
1- dealing with the initial buy or sell (determines the next fields to be filled)
2- how to calculate average cost because if 100 shares are bought today and 50 are sold tomorrow and another 25 are bought (all at different prices) how will this be calculated?
3- can all this even be done in excel?
Phew, that was a mouthful, lol. Hope this made sense and I'd happy to provide any further clarification if it wasn't clear.
Any guidance or tips would be most welcome.
Thanks a ton !!
-BeHappy
Would appreciate some guidance on the following please:
I am trying to create an efficient and effective spreadsheet to help someone in manage their stock portfolio. I am trying to make it as simple and easy-to-use as possible.
Ideally, I would like to have a form or button based system that first asks 1- whether the user has bought or sold a stock
2- From a drop down menu select which account the buying or selling was in (there are about 8 accounts with coded names like H694JKD8 that can be troubling to keep on typing)
3. Select the date of activity (from a drop down calendar)
4. Enter the stock ticker, number of shares, and cost.
This is essentially what it will do but there is more that takes place beneath all this.
In an Excel spreadsheet, the tracking/portfolio headings of: "Account", "Date","Buy or Sell", "Cost", "Buy Commission", "Sell Commission", "Average cost", "Profit or Loss" etc. should be filled with the corresponding information that was input.
The part I especially am having trouble wrapping my head around is:
1- dealing with the initial buy or sell (determines the next fields to be filled)
2- how to calculate average cost because if 100 shares are bought today and 50 are sold tomorrow and another 25 are bought (all at different prices) how will this be calculated?
3- can all this even be done in excel?
Phew, that was a mouthful, lol. Hope this made sense and I'd happy to provide any further clarification if it wasn't clear.
Any guidance or tips would be most welcome.
Thanks a ton !!
-BeHappy
