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!
0
humble_t (1)
7/17/2006 7:58:02 AM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
580 Views

Similar Articles

[PageSpeed] 39

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 <189D3C5C-515F-4114-9932-D889EC9956B7@microsoft.com>, 
humble_t@discussions.microsoft.com 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!
> 
0
7/17/2006 1:41:53 PM
Reply:

Similar Artilces:

Creating PivotTable in Excel2007
How do you create a Pivot Table in Compatibility Mode in Excel 2007. The PivotTable need to be refreshed in Excel 2003. ...

SBS Exchange 2003: Create new user in AD but no exchange mailbox #3
I'm using SBS 2003. Exchange server is SP2. When I create a new user using Active Directory, it prompts me about creating a mail box. It says it's creating a mailbox, then it's done. But no mailbox appears. In fact, if I use the Exchange tasks wizard, I can create, delete, and recreate the mailbox--all say successful without an error message. And yet, no mailbox. This is even after I reboot the PC and sent email to the address. Any ideas what's going on? mike.aes@gmail.com wrote: > I'm using SBS 2003. Exchange server is SP2. > > When I create a new user ...

Excel 2007 Chart in VB6 Program
I have another thread in the VB group and was told to post to these two groups so sorryfor the multi-post. Ill post there again just in case folks want to follow the thread. So I've had a Visual Basic 6 app (as an EXE) that has worked great for the last 9 years or so. Up until Excel 2003 I have had to make minimial changes for it to work properly. The app has an OLE control which acts as a container for many different external sources .In this control I embed an Excel workbook and display a chart sheet. I can interact with the workbook through the normal means of Excel programming objects...

how do I recover charts within an excel workbook?
The workbook had many charts that were previously visible. Now, upon opening the file, the charts are no longer visible. How to I reactivate them to appear? thanks ...

If my GP Dynamics support CPA's new cheque standard
Hi; I am using GP Dynamics 8.0 in Canada, we have Payable Management module installed. How can I know if my current version support the CPA's new cheque standard? The CPA's new cheque standard will be launched in July 2007. -- Kane Kane, Support for the new cheque format has nothing to do with the Payables module. Rather, it is a function of modifications or changes to the report that produces your cheque. Lyle On Fri, 22 Sep 2006 10:06:01 -0700, Kane <Kane@discussions.microsoft.com> wrote: >Hi; > >I am using GP Dynamics 8.0 in Canada, we have Payable Managem...

Problem with Asset Allocator pie charts
Everything "seems" to be working fine with Money 2005 Premium (after restarting the program multiple times). I'm having a problem with many of the pie charts. They do not show up at all. I upgraded from Money 2004. Any ideas? I'm running Windows XP Professional if that's of any help. Thanks, Patrick I've seen them going on a new file on Win XP Pro - do you get them on a new file, or do they not appear on both a new and old one? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;...

Draw line in a chart
I have the following problem. I have a chart (date on x axis) on which I draw a line. How can I obtain the ending points of the line in order to compare, mathematically, their positions to corresponding values of chart at the same date? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements I'm sure your post makes perfect sense to you and might to other readers, but I am los...

How To Allocating Rent Between Two Categories?
Original Question: [The part I need help with is that the amount in column G has to be allocated to the following accounts in this order Gas, Hydro, Promo FC CAM Mkt CAM Base % Rent I'm just working on allocating the last two, so I'm assuming that there is rent remaining to be allocated from column G after allocating to Gas, Hydro, Promo, FC CAM and Mkt CAM. The remaining balance has to be allocated first to base rent than to % rent on a monthly basis. If some rent is allocated to % rent, and the next months sales are so low that the base rent is fully filled we need to first reduc...

how to chart who was working when
Hi there, I'm looking for some help in producing a chart. My organization has hired about 100 folks since 1991, each on contracts of varying lengths of time. Some peoples' contracts have ended, and some have been renewed. I set up some worksheets inputting their names, start and end dates with the intent of producing a graph that will tell me exactly how many people were working for us at one time, based upon 6 month intervals. For example: John Doe 7/29/91 - 3/31/05 Jane Doe 6/21/94 - 2/20/96 Jake Doe 8/2/01 - 4/18/03 I would like a chart that can tell me how many people w...

How do you replace old data with new data without creating a new .
I have existing pivot tables and I want to replace the data source worksheet with new data and the pivot tables update with the new information. I have replace the data source but the pivot tables didn't update. Hit the "Refresh Data" in the pivot table toolbar (looks like an exclamation point) -- Regards, Dave "Dena" wrote: > I have existing pivot tables and I want to replace the data source worksheet > with new data and the pivot tables update with the new information. I have > replace the data source but the pivot tables didn't update. So...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

Could Microsoft create a MSPOS user manual?
My customers are asking for one. Guess we were spoiled by RMS Store Operations having one available after Microsoft updated the SMS Commerce manuals. Makes me expect one for MSPOS even though most software no longer has printed manuals. -- Jeff Faul Merchants Solutions ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and t...

Viewing xml created in .Net in a ASP form VB6
I have been trying to create an xml document in VB.Net for displaying on an existing page written in ASP. im having a type mismatch error in my code. When i view my XML i have the following included in the <binary> node. <Binary xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">DATA HERE </Binary My issue is how do i set the datatype of the the node using dotnet so that i can replicate the same node structure and use my existing ASP page. thanks in advance Colin Graham ...

email charts
I have an excel workbook file containing many department sales charts that recieve data from a group of central data entry spreadsheets. Each department chart resides on its own sheet. Is there a way to email individual chart sheets (or snapshots of the chart sheet) to the respective departments without sending the entire parent file to all recipients? Thanks, Mnaut You could export the chart as a gif file, then email that file to the department. The following code creates a gif file from the active chart: '============================ Sub ExportChartGIF() ActiveChart.Export Filenam...

see chart from pushing button
how would I see a chart from pushing a button on the spreedsheet ? ...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

Switching x-axis & y-axis on line chart
I am working with a large data spreadsheet with year, number and currency data. My problem is when I extract data to create a standard line chart, the chart is defaulting to a specific x-axis and y-axis format with data related to each axis. For visualization purposes, I would prefer to have the data that is currently presented on the x-axis to instead be presented on the y-axis. And likewise, I would like the y-axis data be presented in the x-axis. However, I have been unable to figure out how to do this and the MS Excel Help menu does not seem to address this. Can anyone help? If ...

pie-charting non-numeric data
I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value...

Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the columns by right clicking and choosing the desired options. In the tab that opens there is a slider which is supposed tho set the level of transparency of the column (selceted area). But so far i couldn't find a way how to use this slider. I know that there is an alternative way to get transparent bars by creating a rectangular object formating it and the use copy -> paste. But i wonder what is the slider for if you can't use it? Does anybody know have an idea? Cheers, Thomas ...

Creating a form or template
We scanned in an image of a post it note that has graphics on it. It is a jpg file. Can we somehow make this jpg file a form or template in Outlook? Ideally, we want to pull up a form that looks like the image of a post it note with the graphics in tact and be able to compose a message on the email post it note. How do you accomplish this? Thanks! ...

How to record sell of asset & see true cost?
I had bought a car 4 years ago on loan and some downpayment. And recently I sold it. I had the following accounts setup in Money 2004 Delux: 1. Car Asset with only Opening Balance of $30,000 2. Car Loan account which tracked Principal and Interest amounts (the loan is fully paid) 3. Auto category as Expense category with sub categories such as Purchase Cost (downpayment), Fuel costs, Insurance, etc. All car expenses were tracked under one of these sub categories. I would like to record the sell of the car. Where would I record it and how would I categorize so that I can get...

Insufficient Permissions to Create Tasks
Hi, Exchange 5.5, Windows 2000, Outlook 2000. I have a user who is trying to create tasks and he gets a message that says that he does not have permission to do this. He can edit tasks and delete tasks, but not create them. We've run the repair utility, reinstalled, thought that it had to do with the PALM software and removed that, and there has been no change. Any help would be much appreciated! Thanks! ...

Fonts do not print in colur used when document created.
When I create a document in either Word ot Publisher the text is not always printed in the colour selected. This problem seems particular to Red & Blue. can any one help please. Peeter Have you tried some maintenance on your printer? Are you saving as a PDF? This problem has been reported using Microsoft's save as add-in. -- Mary Sauer http://msauer.mvps.org/ "Peter Piper" <PeterPiper@discussions.microsoft.com> wrote in message news:76737C66-2B07-42DF-BFC5-828A14CE135F@microsoft.com... > When I create a document in either Word ot Publisher t...

Stack Bar Charts
I have yearly information that I would like to graph in a stacked bar chart. I have years 2005 to present Two companies I am comparing with two variables for each company each year. I would like to have the stack bar chart by year reflect the stack of Co A vs Co B on variable 1 + 2. Any help would be appreciated. Thanks. You want a clustered-stacked chart: http://peltiertech.com/WordPress/clustered-stacked-column-charts/ http://peltiertech.com/WordPress/clustered-stacked-bar-charts/ - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Sean wrote: > ...

personal income and expenditure chart
can anybody give me a personal income and expediture chart please Thanks Mike mikeyboy87 wrote: > can anybody give me a personal income and expediture chart please > Thanks Mike You can do this in Excel but why re-invent the wheel? Quicken or Money have far more tools and options. If money is an issue try checking ebay. I purchased Quicken 2004 for about $10 including shipping. It isn't the latest and greatest but it does what I need it to do. gls858 ...