Halebop
Board Regular
- Joined
- Nov 2, 2004
- Messages
- 83
Hi,
I'm overhauling a spreadsheet that has got unwieldy and I'm looking for some design advice before I begin. I've attached a sample worksheet below.
The spreadsheet records fundamental analysis data for public companies that I own or have my eye on. It then build charts from the data so I can review trends more easily. Originally I built a page of charts for each company reviewed but this has become unwieldy, particularly as I did this some time ago and none of the chart were dynamic - meaning I have to rebuild each one (about a dozen charts) for each company (now a few dozens) every time I add a new years data.
So here's what it does: One worksheet for each company. Each worksheet is named according to the companies stock ticker code and country of domicile thus: Code.Country or say IBM.US. Then the worksheet lists financial data and ratios in columns by year. This data then feeds the charts.
I want to feed data from the "company" worksheets into a single "chart" worksheet containing maybe a dozen different charts. Be able to select which company I am reviewing on the chart page with a drop down or similar device. Be able to add new yearly data to any company worksheets or add a new company on a new worksheet. All of this needs to update the charts dynamically. I was thinking pivot tables but I've often found them problematic to get working with complex charts.
Any design advice, tips or cool VBA really appreciated... (before I head off in a screwy direction).
I'm overhauling a spreadsheet that has got unwieldy and I'm looking for some design advice before I begin. I've attached a sample worksheet below.
The spreadsheet records fundamental analysis data for public companies that I own or have my eye on. It then build charts from the data so I can review trends more easily. Originally I built a page of charts for each company reviewed but this has become unwieldy, particularly as I did this some time ago and none of the chart were dynamic - meaning I have to rebuild each one (about a dozen charts) for each company (now a few dozens) every time I add a new years data.
So here's what it does: One worksheet for each company. Each worksheet is named according to the companies stock ticker code and country of domicile thus: Code.Country or say IBM.US. Then the worksheet lists financial data and ratios in columns by year. This data then feeds the charts.
I want to feed data from the "company" worksheets into a single "chart" worksheet containing maybe a dozen different charts. Be able to select which company I am reviewing on the chart page with a drop down or similar device. Be able to add new yearly data to any company worksheets or add a new company on a new worksheet. All of this needs to update the charts dynamically. I was thinking pivot tables but I've often found them problematic to get working with complex charts.
Any design advice, tips or cool VBA really appreciated... (before I head off in a screwy direction).
| Dynamic Charts.xls | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Year | 1999 | 2000 | 2001 | 2002 | 2003 | 2004 | ||
| 2 | Cash | 0 | 0 | 637 | 685 | 2294 | 14017 | ||
| 3 | Current Assets | 59082 | 62134 | 67537 | 73637 | 83161 | 83368 | ||
| 4 | Current Liabilities | -15336 | -17747 | -13035 | -13917 | -15530 | -35000 | ||
| 5 | Fixed Assets | 13957 | 18646 | 22728 | 21734 | 23487 | 22985 | ||
| 6 | Investments | 810 | 1115 | 0 | 0 | 0 | 0 | ||
| 7 | Intangibles | 572 | 500 | 429 | 357 | 286 | 178 | ||
| 8 | Long Term Liabilities | -800 | -9 | -978 | -1029 | -1179 | -1800 | ||
| 9 | Interest Bearing Debt | -21234 | -22052 | -27750 | -28139 | -42471 | -27737 | ||
| 10 | TOTAL | 37051 | 42587 | 49568 | 53328 | 50048 | 56011 | ||
| 11 | |||||||||
| 12 | Assets | 74421 | 82395 | 91331 | 96413 | 109228 | 120548 | ||
| 13 | Return on Assets | 18.21% | 18.83% | 16.57% | 19.34% | 15.23% | 18.30% | ||
| 14 | |||||||||
| 15 | Debt / Equity % | 57.31% | 51.78% | 55.98% | 52.77% | 84.86% | 49.52% | ||
| 16 | Current Ratio | 3.85:1 | 3.50:1 | 5.23:1 | 5.34:1 | 5.50:1 | 2.78:1 | ||
| 17 | Interest Cover | 9.85 | 10.67 | 8.06 | 10.26 | 7.21 | 8.76 | ||
MHI.NZ | |||||||||
