Pyro's Excel Goodies

[Pyrotex]

Originally Posted by LaurieAG

...problems with extremely large amounts of data would cause definite problems. in Excel.

Yes, of course. As a rule of thumb, I have serious second thoughts about using Excel for a data set that exceeds 1,000 rows, or that requires more than 100 columns for analysis, categorization, merging, etc.

Excel WILL handle far more data than that, BUT... the probability of making an error and the difficulty of finding your errors becomes just too too huge. It also makes Excel uncomfortably slow.

For a data set between 1,000 and 100,000 rows (records), I prefer to have the data in mySQL or some other speedy database, and write a PERL script to do the analysis.

For over 100,000 records, you can't beat SAS.

[Pyrotex]

Here are valuable resources for you spreadsheet geek wannabees (SGW) out there.

How I got so good at Excel was that as I picked up tricks and techniques, I kept a "cheat sheet" for myself. This eventually grew and evolved into an ~80 page manual for Achieving Excel Mastery. Here and now, I give away free copies of this manual, divided into two parts for your convenience.

If you want to know how to use Equations and built-in Formulas, then check out part 1: Basics of Excel Equations and Formulas.

[Pyrotex]

And here is the second part:

Basics of Excel Macros (VBA).

[Grains]

First of all I want to say that I have reviewed your excel work and it is amazing. I have been working with vba/excel for the past 2 years and I am still scratching the surface.

I was looking at your document for investment/retirement planning and had some input/challenge.

Is it possible to have one page where the user inputs data such as current age, retirement age, investments in qualified accounts (IRA, SEP, etc), investments in non-qualified accounts (regular account, etc), inflation, prediction of return on investments, prediction of return on investments during retirement, and annual income or annual income percentage needed in retirement.

The reason I ask is when you have qualified accounts your return will differ as if you had those assets in non-qualified accounts. And inflation will obviously have an effect on income needed today (user input) and in 20 years what the inflation income adjusted income they will need in 20 years. for example if today i need 100k in todays income in 20 years that might be like needing 200k.

I have tried for a while to encompass the effects of non-qualified vs qualified accounts as well as inflation and other measures to create a retirement plan in excel.

Have any ideas???

[Grains]

the other thing is a chart showing when i start taking income/withdrawals from my retirement account how will those adjust year to year as inflation increases the amount i need

[Pyrotex]

Originally Posted by Grains

...Is it possible to have one page where the user inputs data such as current age, retirement age, investments in qualified accounts (IRA, SEP, etc), investments in non-qualified accounts (regular account, etc), inflation, prediction of return on investments, prediction of return on investments during retirement, and annual income or annual income percentage needed in retirement...

When asked any question about Excel which begins, "Is it possible to..." -- my standard answer is "Absolutely Yes".

My invest/retire spreadsheet basically calculates just one "account", which grows at one rate, and which is calculated in one column.

On a separate page (call it "Accounts"), why don't you devote one (or more) columns for calculating each of the types of accounts you mentioned. You could have your IRA calculated month by month in column B, your SEP in column D, your savings in column F, your Swiss bank account in column H, ... etc.

Then on my page where all the calculations take place, replace the equation in the investment column with the new equation:
=Accounts!B7 + Accounts!D7 + Accounts!F7 + Accounts!H7 + ... etc.

So, where *I* calculate just ONE account, make it where it adds up ALL your accounts.

You can easily create a new page, (call it "Inputs") where you define each of the variables the user should enter, such as current value of IRA, predicted return of IRA, current value of SEP, predicted return of SEP, ... etc.

Name these input cells. Give them names like: IRAstart, IRArate, SEPstart, SEPrate, ... etc.

Then on the Accounts page (where you calculate all your accounts), wherever you need one of your inputs in an equation, just use the name instead of a cell address.

[alexander]

You are the person i can ask this, i know you'd know a hell of a lot better then me. Can you write a macro for excel that will collapse certain fields to be invisible, and then next time toggle them back, or at least one macro to collapse the fields, and another to uncollapse them?

here's the problem, our av team has a form that they use universally to price things, what you do is put in say the name of the product, their cost, mark up, and it will calculate a few other values from it. They also use some checking systems etc for the final form, to them they want to see everything, but when they output the form to pdf, they want to hide a few fields that customers don't need to see. now they have to do them one by one, but i was hoping you'd have some time to work some magic, and perhaps propose some sort of a really cool solution (such as some of the crazy things you've done here )

I have faith in your superior excel knowledge

And thanks in advance for the help, or even just looking into this and telling me that it cant be done.

[Pyrotex]

Originally Posted by alexander

You are the person i can ask this, i know you'd know a hell of a lot better then me. ...And thanks in advance for the help, or even just looking into this and telling me that it cant be done.

Alex,
my standard answer is "Absolutely Yes", it can be done.
Please read the PM I sent with questions that need answers before I can dazzle you with my Excel Wizardry.
Pyro

[Grains]

pyro...ive been working with some vba macros and wanted to see if you could help me with a few scripts...

1) If I have a word document with a whole bunch of names and addresses and want to pull it over to excel is there a way to write a macro that will pull all the data over on to the sheet. I know there is and I know how to do that part here is where it gets a little tricker. Each word document might have different amounts of pages and addresses . I have written a macro for one word file but I try it in a different word file it dosn't work because different amounts of data. Any suggestions?

2) When the data comes over from word it is very discombobulated. For example like this

A1

Sam Nobody
113 Lake View Dr
Atlanta, GA 30328

So I wrote a macro that does this to that data

A1 A2 A3
Sam Nobody 113 lake view.. Atlanta, GA 30328

Reason I am doing this is so we can do mailing labels. Problem is I have to run that macro for each individual person...Not hard just time consuming and I know there should be a way to automate it. Its just the problem is that when the data comes over from word it also brings about random spaces of rows between each entry...so one entry to the next might be 2 rows where the next one could be 5.....does that make since...

Any help would be appreciated..

[Pyrotex]

Originally Posted by Grains

...1) If I have a word document with a whole bunch of names and addresses and want to pull it over to excel
...2) When the data comes over from word it is very discombobulated. ...

Hello Grains,
There are 4 ways to get the data to Excel:
1. Easiest--just copy the data from Word and paste it into Excel.
2. Easy--go to Excel and "import" the data from the Word doct by using the menu command (from Excel).
3. Hard--write an Excel macro to transfer the data from the Word doct.
4. Hardest---write a macro in Word to transfer the data.

All of these techniques depend on the data being well formatted in Word.
You need to make sure that every name/address has exactly the same number of lines; every name/address is separated by the same number of blank lines; every line of the name/addresses end in a full paragraph character (The Enter Key) and not a "new-line" character. So your best bet is to neaten up the Word file FIRST.

Then, technique 1 or 2 works like a snap without having to write any macros.