For the geek who's an investor, and the investor who's a geek, we present Online Investing Hacks, 100 industrial-strength, bleeding edge tips, tools and techniques for analyzing and managing online portfolios.

Individual investors have become more computer-literate and technology-dependent than ever before. Whether you're looking for suitable investments, studying alternatives, or managing your portfolios, you need data. The Internet can be a goldmine of financial data and research, but today's online investors also use spreadsheets, databases, and financial applications to select, study and manage investments. If your proficiency has grown to the point where you crave industrial strength tips and tools to turbo-charge your efforts, this is the book for you.

Online Investing Hacks covers:

Screening Investments
Collecting Data
Fundamental Analysis
Technical Analysis
Executing Trades
Investing in Mutual Funds
Portfolio Management
Updating Stock Data

Financial Planning
Project Management

Other books for managing online investing are either out of date, are for beginners and don't go beyond the most basic advice, or are so dull and boring they squeeze that last bit of fun out of the topic. Online Investing Hacks is for the customer who wants to know the latest techniques, to go beyond the basics, who gets jazzed by cool online tools and services, and who actually wants to have a bit of fun while trying to strike it rich (or at least not lose their shirt).

For the geek who's an investor, and the investor who's a geek, we present Online Investing Hacks, 100 industrial-strength, bleeding edge tips, tools and techniques for analyzing and managing online portfolios. Individual investors have become more computer-literate and technology-dependent than ever before. Whether you're looking for suitable investments, studying alternatives, or managing your portfolios, you need data. The Internet can be a goldmine of financial data and research, but today's online investors also use spreadsheets, databases, and financial applications to select, study and manage investments. If your proficiency has grown to the point where you crave industrial strength tips and tools to turbo-charge your efforts, this is the book for you. Online Investing Hacks covers: Screening Investments; Collecting Data; Fundamental Analysis; Technical Analysis; Executing Trades; Investing in Mutual Funds; Portfolio Management; Updating Stock Data; Financial Planning; Project Management Other books for managing online investing are either out of date, are for beginners and don't go beyond the most basic advice, or are so dull and boring they squeeze that last bit of fun out

Bonnie Biafore is an award-winning author of several books on investing, finance, project management, and software. She s a columnist for Better Investing magazine and the author of the NAIC Stock Selection Handbook. A PMI-certified project management professional with more than 20 years experience, she also manages information technology and software development projects in a variety of industries.

Hack #9 Make Nested Functions Work in Excel
Getting nested functions to work is easy when you trick Excel features into mimicking functions and subroutines.

You?re either a genius or a fool if you select an Excel spreadsheet cell, type an equals sign, pound out a multilevel nested function start to finish?and expect everything to work just the way you planned. It?s easier to write code in even the most cryptic of programming languages. There, you often have a development environment that provides tips for function parameters; you can code and separately test subroutines and objects, or you can construct code stubs to gradually build up a working program. At first glance, Microsoft Excel appears to offer no more than the Insert Function command, which shows you the parameters only for a top-level function. However, you can simulate subroutines and stubs in out-of-the-way worksheet cells, and check the accuracy of your formulas every step of the way.

Suppose you want to create a formula that indicates whether you should change your diversification in growth, value, and bond investments, and you want to perform the following actions:

? Calculate the formula only if the investment value is a valid number (not text or blank).
? Display a warning when the investment is a valid number and is more than 25 percent of your total portfolio.
? Use only half the investment value in the calculation if the investment is in a blend-style mutual fund, because the fund splits its investments between growth and value.
? Extract the second word in the fund-style cell to determine the style.

Using Named Cells
Instead of using cell references, such as B24, which can make formulas quite inscrutable, assign descriptive names to individual worksheet cells, ranges of cells, formulas, or constants. Names are like variables and make formulas easier to read, as illustrated in Example 2-6.

Example 2-6. Names make Excel formulas easy to decipher

= balance * interest_rate

Names are also more portable. You can reference a name in an Excel worksheet throughout the entire workbook, and move formulas to other cells or even to other worksheets within the workbook without changing the name references they contain. If you?re going to use a value more than once, it?s much easier to calculate that value in a named cell and refer to the name.

Creating Stubs
For logical functions, it?s easy to build a stub to check your logic before you plug in calculations. For example, the Excel IF function uses three parameters: the test condition, the result when the test is true, and the result whenmthe test is false, shown in Example 2-7.

Example 2-7. The Excel IF function with its three parameters

= IF(test_condition, result_if_true, result_if_false)

You can type in a test condition along with hardcoded strings or cell references to show the true and false results, as shown in Example 2-8.

Example 2-8. An example of the IF function

action = IF(ISNUMBER(investment),test_diversification,"Investment isn't a number.")

In this example, investment and test_diversification are name references to cells C2 and sheet2 A2, respectively.

Here?s how the parameters work:

Test condition
For the first parameter, specify a condition test that checks if the investment is a number: ISNUMBER(investment).

Result if test is true
If the test condition is true (the investment is a number), you want to test for diversification. For this parameter, you simply type the name of the cell that performs the diversification test.

Result if test is false
If the investment cell doesn?t contain a number, you want to display a warning. For this parameter, then, type the string: Investment isn't a number.

To test the formula, type the string Check diversification in the test_diversification cell. When you type a valid number in the investment cell, the action cell should display the string Check diversification. When you type a string or leave the investment cell blank, the action cell should display Investment isn't a number.

This formula is done! Because it works and refers to other cells, it won?t change, regardless of future calculations. For example, you can replace the text in the test_diversification cell with the formula that actually testsyour portfolio diversification. Example 2-9 shows another example of simplifying logical tests using strings and cell references.

Example 2-9. Named cells and text strings simplify the diversification test
= IF(InvestPercent > 0.25,"Check diversification","Leave it alone")

Creating Excel Subroutines
To create a true subroutine in Excel?one that you can call with parameters?you must use macros and Visual Basic for Applications (VBA). However, you can mimic the behavior of a subroutine using intermediate calculations in several cells, where each cell uses the results of other cells. By combining this divide-and-conquer approach with named cells, each intermediate calculation is easy to decipher and test.

You can nest functions only to seven levels in Excel. If you have a particularly deep nesting problem, you can sidestep this limitation by calculating portions of your problem in other cells.