+ Reply to Thread
Results 1 to 2 of 2

Creating a dynamic asset allocation chart

  1. #1
    humble_t
    Guest

    Creating a dynamic asset allocation chart

    I was inspired by a recent E-Trade commercial to build a financial portfolio
    pie chart using Excel. The chart I have in mind would show the percent
    breakdown of asset allocation (e.g., cash, large cap stocks, small cap stock,
    etc). This is the data table format I had in mind:
    Financial Institute (column 1), Allocation (column 2), and Amount (column 3)
    e.g.,
    BofA Cash $1600
    Fidelity Large Cap $1000
    Fidelity Cash $101

    As I update my allocation, say, move some cash to small cap stocks, the
    chart would automatically display my new portfolio breakdown.

    The problem I'm running into is this: when I try to create such a pie chart,
    each row (series) is created as its own slice (e.g., BofA cash, Fidelity
    Large Cap, Fidelity cash) rather than grouping a single "cash" category. Is
    it possible for Excel to dynamically look for cells with the same content
    (say, "Cash") and add the corresponding amount to form a single slice?

    Thanks in advance!

  2. #2
    Tushar Mehta
    Guest

    Re: Creating a dynamic asset allocation chart

    Use your data as the source for a PivotTable. The PT will have the
    'allocation' as the row field and SUM('amount') as the data field.

    Alternatively, you can "roll your own." Suppose your data are in A2:Cn and
    row 1 is a header row. Then, create a named formula (Insert | Name >
    Define...)

    DataRng =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,3)

    The use of the above name means the solution will adjust itself as you add
    more rows.

    In some empty range, enter the category names (the column 2 names).
    Cash
    Large Cap
    etc.

    Then in an adjacent cell, array enter the formula
    =SUM(IF(INDEX(DataRng,0,2)=F2,INDEX(DataRng,0,3)))

    Copy this cell as far down the column as you have categories in the previous
    column.

    [To array enter a formula, do not complete data entry with the ENTER key.
    Instead, use the CTRL+SHIFT+ENTER combination. If done correctly, XL will
    display the formula within curly brackets { and }]

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <[email protected]>,
    [email protected] says...
    > I was inspired by a recent E-Trade commercial to build a financial portfolio
    > pie chart using Excel. The chart I have in mind would show the percent
    > breakdown of asset allocation (e.g., cash, large cap stocks, small cap stock,
    > etc). This is the data table format I had in mind:
    > Financial Institute (column 1), Allocation (column 2), and Amount (column 3)
    > e.g.,
    > BofA Cash $1600
    > Fidelity Large Cap $1000
    > Fidelity Cash $101
    >
    > As I update my allocation, say, move some cash to small cap stocks, the
    > chart would automatically display my new portfolio breakdown.
    >
    > The problem I'm running into is this: when I try to create such a pie chart,
    > each row (series) is created as its own slice (e.g., BofA cash, Fidelity
    > Large Cap, Fidelity cash) rather than grouping a single "cash" category. Is
    > it possible for Excel to dynamically look for cells with the same content
    > (say, "Cash") and add the corresponding amount to form a single slice?
    >
    > Thanks in advance!
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1