Online Investing Hacks

Hacks #7-13

Microsoft Excel is a versatile tool in your financial analysis arsenal. It can calculate, compute, and tally any financial measure you want to evaluate. In addition, Excel can digest data—downloading data from the Web to feed your analyses; validating the values you enter to ensure that they are appropriate; sorting, filtering, and highlighting financial values to hide poor results or emphasize the good; and categorizing or aggregating values to show portfolio composition. Excel can produce charts that make it easy to compare financial results or visualize trends. And if you manage to come up with requirements that Excel’s built-in features can’t satisfy, you can always write some code in Visual Basic for Applications (VBA).

Throughout this book, you’ll find hacks that take advantage of Excel features and functions. This chapter explains how to use them. Excel includes functions that calculate commonly used financial measures, such as the internal rate of return of an investment or the monthly payment for a loan. As it turns out, investing uses several statistical functions to evaluate growth rates and trends, and others to assess risk. When you want to find data in your spreadsheets or process that data in some way, you can use additional functions in the Date & Time, Text, Logical, and Lookup & Reference categories. To reduce time, frustration, and errors as you build your own financial spreadsheets, examine the list of functions that Excel provides before concatenating ...