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.
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.
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.
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:
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:
Figure 1-7. Display only the rows that meet filter criteria you define in Excel.
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.
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].
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:
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.
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.
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.