O'Reilly Network    
 Published on O'Reilly Network (http://www.oreillynet.com/)
 See this if you're having trouble printing code examples


O'Reilly Book Excerpts: Online Investing Hacks

Online Investing Hacks, Part 2

by Bonnie Biafore

Editor's note: In her book, Online Investing Hacks, Bonnie Biafore presents 100 tips and hacks designed to help you reach your financial investment goals. In these two sample hacks from the book, Bonnie covers ways to manipulate stock screens to your advantage, and how to avoid manual data entry when evaluating financial ratio trends in the statements you download.

Moderate Hack #6

Weed Out Results

Related Reading

Online Investing Hacks
100 Industrial-Strength Tips & Tools
By Bonnie Biafore

Because stock screens blindly follow the criteria tests and cut-off values you specify, they can omit companies that almost meet your criteria. By using looser screening criteria, you'll end up with more results, but you can use Excel or online features to compare financial measures and then decide for yourself which companies to keep. Downloading screen results to a spreadsheet gives you the most flexibility, because you can bring all of Excel's features into play to evaluate your contestants. However, if you use Quicken.com's stock screens, the Compare Stocks feature displays the financial measures for all the screen results in a table on the Web.

If you download results data to a spreadsheet, be sure to display all the columns of data you want to evaluate before you create the spreadsheet. Whether you use the MSN Money Deluxe Screener [Hack #5], the Reuters Investor Power Screener, or another stock screen that downloads results, add, remove, or rearrange columns in the results on the web page. For example, using the MSN Money Deluxe Screener, you choose View→Column Set Displayed→Customize Column Set on the screener's menu bar. Then, you can select columns and click Add, Remove, Move Up, or Move Down to modify the columns in your results.

Tinkering with Results in a Spreadsheet

With the data for the companies that pass your criteria stored in a spreadsheet, you can sort and filter based on any of the data in the spreadsheet with much more flexibility than that provided by online tools. Because you're playing directly on your computer, you don't have to worry about dropped Internet connections or slow web site response. If you use the MSN Money Deluxe Screener, choose File→Export→Results To Excel and then click OK. The spreadsheet opens in Excel with a default filename, so you'll have to choose File→Save As in Excel to save it to the folder you want on your computer.

NOTE: The exported results file opens in Excel as a comma-delimited file. Before you save the file, be sure to change the file type to an Excel workbook.

Sorting

With the online screening tools, you can typically sort by only one column at a time. Although you can sort using up to three columns in Excel, it's not much help, because sorting by multiple columns implies duplicate entries in the first and second sort column. Because these spreadsheets contain financial measures to at least one significant digit, you won't see much duplication, but the capability is there if you need it. To sort the results in Excel, follow these steps:

  1. Select a cell in the spreadsheet. If you know which column you want to sort by, select the label for that column.
  2. Choose Data→Sort.
  3. In the Sort By box in the Sort dialog box, select the label for the column by which you want to sort in the drop-down list. For example, as a fundamental investor, you might sort by EPS growth. If you selected the label cell before you opened the dialog box, Excel selects that column automatically as the sort field.
  4. Because higher growth rates are more desirable, select the Descending option, so the results appear from highest to lowest growth.
  5. Click OK.

Filtering

Excel enables you to filter the rows that appear in your spreadsheet. If you run a stock screen that produces several dozen companies, you can turn Excel filters on and off quickly, or filter by more than one column at a time. For example, you can apply a filter to find the companies with great growth rates, then filter for companies with strength (low debt, good inventory turnover ratios, and more). Filtering rows is faster and more flexible than changing criteria in a stock screen, so it's a great way to see which companies keep showing up as you look for desirable characteristics.

Let's filter a results screen to see how it can help evaluate a number of potential investments. Follow these steps to define and refine filters:

  1. Select a cell on the spreadsheet—any cell will do.
  2. Choose Data→Filter→AutoFilter. Down arrows appear in each column heading in the spreadsheet.
  3. To define a filter for a column, click its Down arrow. For example, to filter for companies with reasonable levels of debt, click the Down arrow in the Debt to Equity Ratio column. The AutoFilter drop-down list includes all the values that appear in cells in the column as well as other filter options.
  4. To specify a filter test, choose (Custom...) from the drop-down list. The Custom AutoFilter dialog box appears.
  5. Choose the comparison operator from the drop-down list in the first box. For example, to show companies with debt to equity ratios less than 30 percent, choose "is less than or equal to." In the box on the right, type .3 (for 30 percent) for the value for the test.
  6. Click OK. Only rows with companies that meet the debt to equity ratio filter criteria appear, as shown in Figure 1-7.


    Figure 1-7. Display only the rows that meet filter criteria you define in Excel.

  7. To apply another filter, click the Down arrow in another column. For example, these companies have grown quickly over the past several years, so let's filter out the lower revenue growth rates. Click the Down arrow for 5-Year Revenue Growth, and choose (Custom...) on the drop-down list. Choose "is greater than or equal to," and type 20 in the value box. The rows that appear in the spreadsheet show the companies that meet the filters set for both columns.

    TIP: You can define a filter based on a range of values by defining the first test in the Custom AutoFilter dialog box to look for values greater than one value and the second test to find values less than another value.

  8. To turn a column filter off, click the Down arrow in that column, and then choose (All) from the drop-down list.

Beginner Hack 18

Download Financial Statements

Download financial statements to study financial ratios without tedious manual data entry.

To puzzle out how a company has performed in the past and where it might be heading, financial ratios can't be beat. The problem has always been data entry. Evaluating trends in financial measures requires at least three years of values, but who wants to manually transcribe three years of income statements, balance sheets, and cash flow statements into a spreadsheet? Sure, financial data providers offer data files of financial information—with a price tag attached [Hack #20]. And, you can always use Excel web queries to grab data from web pages [Hack #7]. However, EdgarScan (http://edgarscan.pwcglobal.com) provides the key information from financial statements, extracted from the filings that the company submits to the SEC, already stored in Excel spreadsheets that you can download at no charge.

The Global Technology Centre at Price Waterhouse Coopers developed EdgarScan, and it's a mother lode of data for investors who dig deep into financial ratios. EdgarScan spreadsheet files contain up to thirteen years of financial statements (both annual and quarterly) for companies. If a company has been in business less than thirteen years, it provides the data that is available.

NOTE: Financial statements can be quite complex. EdgarScan aggregates some numbers into higher-level categories. If EdgarScan data doesn't contain the measures you need, use other sources for data [Hacks Section 3.8#19 and Section 3.9#20].

Financial Statements from EdgarScan

EdgarScan scans the filings in the SEC EDGAR database for financial data and stores them in standard categories, so you can locate and compare numbers. Although you can view this data on the EdgarScan web site, downloading it as an Excel spreadsheet means you can slice and dice ratios for one company, or compare values between competitors. EdgarScan spreadsheets include columns for quarterly (10-Q) and annual (10-K) filing for the last thirteen years; samples are shown in Figures Figure 3-1, Figure 3-2, and Figure 3-3.

To download an EdgarScan spreadsheet, follow these steps:

  1. Navigate to http://edgarscan.pwcglobal.com/servlets/edgarscan to access the EdgarScan web page.
  2. Type the name or ticker symbol in the box and click Search.

    TIP: To limit the companies or filings that EdgarScan returns, click the Advanced link next to the Search button. You can search by company name, ticker symbol, industry, and SIC code, and limit the filings to a specific type of SEC filing, filings submitted during a range of dates, or filings that contain keywords or phrases.

  3. If EdgarScan finds more than one company that matches the name or ticker you typed, it displays links for each result. Click the link for the company that you want.

    NOTE: A company page includes three links that help you research a company. Click (business) to read the description of the business from the most recent 10-K. To jump to the company's summary page at Yahoo! Finance, click the ticker symbol link, such as (HD). The third link takes you to a web page that lists all the companies in the same industry, as defined by the Standard Industry Classification (SIC) code.

  4. To download an EdgarScan spreadsheet, scroll below the company summary table and click Excel Spreadsheet. In the File Download dialog box, click Save. In the Save As dialog box, navigate to the folder in which you want to store the file, type a name for the file in the File Name box, and click Save.

Links to recent filings, 10-Ks, and 10-Qs display the SEC filing on the screen. Click links to jump to the sections you want to read. If you want to work offline, you can download filings as text files, HTML files, or rich text files. If you can't call to mind what an SB-2MEF filing is (or any of the other SEC form identifiers), click the glossary of form types link at the bottom of the page.

You can also download SEC filings from the EdgarScan web site into Excel using web queries [Hack #7].

EdgarScan includes a feature called the Benchmarking Assistant, which displays a graph of values for one financial measure at a time. You can choose from several categories of measures and almost 100 financial measures in all. In general, a linear graph of values isn't useful, because you want to see the growth year over year [Hack #26]. However, you can check trends in key financial ratios by displaying a graph of a measure in the Ratios category in the Benchmarking Assistant, demonstrated in Figure 3-6 below the chart.


Figure 3-6. The EdgarScan Benchmarking Assistant graphs financial ratios.

Copyright © 2009 O'Reilly Media, Inc.