Results 1 to 9 of 9

Thread: Formula for maintaining a Share Portfolio

  1. #1

    Formula for maintaining a Share Portfolio



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    723
    Articles
    0
    Quote Originally Posted by phits002 View Post
    My question is; is there a formula that essentially says: if there is a sale, deduct it from the lowest cost base parcel?
    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.

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

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    723
    Articles
    0
    Quote Originally Posted by phits002 View Post
    can you provide any guidance on how this operates?? Thanks
    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
    Last edited by Hercules1946; 2017-01-11 at 02:08 PM.

  5. #5
    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

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    723
    Articles
    0
    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.

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    723
    Articles
    0
    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/

  8. #8

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    723
    Articles
    0
    Quote Originally Posted by daliltv View Post
    Thanks
    Hello - I notice that you are a different user. Did you reply to the wrong post by mistake?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •