Formula for maintaining a Share Portfolio

Hi all

OK, so here is my scenario:

I have a holding of shares in a share portfolio, broken down by individual parcels per holding (for cost base purposes). For example, I have ANZ shares with multiple parcels (due to various purchase dates) along with various different cost bases. I then need to maintain a running balance for this holding, taking in to account disposals, and where there is a disposal, take it from the lowest cost base with a remaining balance.


My question is; is there a formula that essentially says: if there is a sale, deduct it from the lowest cost base parcel?

Currently, my formula is =IF(AND(MATCH("Sale",$C:$C,0)>0,G8>0,(INDEX($G:$G,MATCH("Sale",$C:$C,0))>G8),K8=MIN(K:K)),G8,0)

However, the above formula only takes the lowest cost base, and doesn't take the remaining balance of the sale into account. So the formula I need, needs to be able to apply the whole sale balance to the lowest available cost bases.

It may not be possible, and I may not have explained myself too well, but any help would be greatly appreciated.

I have attached an extract of the file so you can see what I'm trying to do.

Please let me know if I can offer any further clarification to the ^.

Cheers
Tim

phits002 said:

My question is; is there a formula that essentially says: if there is a sale, deduct it from the lowest cost base parcel?

Click to expand...

Hello
I'm not saying it can't be done with a formula solution, but personally I would go for a VBA solution for this type of problem because the user involvement will be minimal once it has been coded.

Thanks, Hercules1946. I have not worked with a VBA before, can you provide any guidance on how this operates?? Thanks

phits002 said:

can you provide any guidance on how this operates?? Thanks

Click to expand...

Hi
VBA can provide more flexibility when you reach the boundaries of a formula based solution. A formula is designed to return a result to one cell, and your scenario suggests that you will need to update multiple cells in one operation, which VBA can do. How does it operate ?
1) You set the code in motion
2) Input new data (purchases and sales)
3) The code recalculates and updates your portfolio schedule.

The above is very broad, because the possibilities are considerable based on how far you want to go. About 3 years ago, I responded to someone with a simple formula question, and ended up developing a package to interact with an on line Wall Street stockbroker over the next 18 months

Thanks again for your reply.

Forgive my ignorance, but the VBA still operates on excel formula, does it not? So I would still need to come up with a formula, and then apply it across as a macro (also forgive my description!)?

Would love to learn more about macros and VBAs, can you point me towards any training that can assist in this regard?

Cheers

Im a bit short of time at the moment, so Ill get back to you in more detail later.
1, VBA can certainly use Excel formulae if it needs to, but it also has many of its own functions beyond that.
2. Your key starting point would be to specify/define what your new process needs to do, so that you, or someone assisting you can write the VBA
code to deliver that.
3. VBA code and macro are to some extent interchangeable terms, and certainly a macro is constructed using VBA code. In my days of mainframe programing, a macro was
a routine written using a high level language (meaning small numbers of very powerful commands that did an enormous amount of processing). With Excel you can record macros
to cover what you are doing, and Excel will build the VBA code to automate it, which is an excellant way to learn how to use it.

Hello
Regarding your point about training take a look at the article about on line training written by our site administrator, Ken Puls under the Resources tab above.
There are also some good books around on the basics of VBA if youi browse around on Amazon. Also check out the link below:


http://www.homeandlearn.org/

Thanks

daliltv said:

Thanks

Click to expand...

Hello - I notice that you are a different user. Did you reply to the wrong post by mistake?