Your browser does not support JavaScript!
Investor Hub AAII Platinum A+ Investor Dividend Investing Shadow Stocks Stock Investor Pro Stock Superstars Report VMQ Stocks AAII Store →
AAII: The American Association of Individual Investors

STOCK INVESTOR PRO > April 2016

Introducing Stock Investor Pro 4.5

Introducing Stock Investor Pro 4.5

This month’s release is the culmination of over a year’s worth of work by AAII staff and two months of beta testing by Stock Investor Pro subscribers. We are proud to introduce Stock Investor Pro 4.5. This update deals strictly with data; no enhancements or changes to functionality or usability have been made to the program at this time. However, these new changes should improve the quality and reliability of the data, and they set the stage for future improvements to Stock Investor Pro.

With version 4.5, we have migrated to a new SQL-based database from Thomson Reuters. This improves the timeliness of the data and gives us access to deeper data. The migration process has also provided us with an opportunity to “audit” our data handling and calculations and make adjustments and improvements where needed. In addition, we were able to address some data timing and display issues to better align data across all financial statements as well as match data across similar period lengths. Over the last 18 months or so, the Stock Investor Pro development team has been migrating to the new database and has tested it extensively in-house; we have also conducted a two-month beta test among current Stock Investor Pro subscribers.

This issue of Stock Investor News will walk you through some of the key data enhancements and changes that we have made. Please note that there is no video tutorial this month; we will resume those next month.

 

 


Table of Contents

Period Alignment of Financial Statement Data

The interim (quarterly) financial statement data in Stock Investor 4.5 is now linked so that all of the periodic data is for the same period-end date. In the past, the data process took the latest data available for the income statement, cash flow statement and balance sheet and made that Q1, and then worked backward from there for quarters two to seven (Q2 to Q7). However, especially in instances where the Q1 data was being taken from earnings announcements and not 10-Q reports, there may have been a delay in the cash flow and balance sheet data, since companies release limited financial data in their earnings statements. So, for example, Q1 data for the income statement may have been for the period ended June 30, but the Q1 cash flow data may have been for the period ended March 31.

With the adjustments we have now made to the data, if a company’s latest interim income statement data is for the period ended June 30, but there is no cash flow and/or balance sheet data yet for that same interim period, Q1 data for the cash flow statements and/or balance sheets are nulled. This alignment, based on period-end date, extends across all interim (quarterly) periods in Stock Investor Pro 4.5. This allows us to offer the latest data that is available for companies, but also properly aligns the periods for meaningful analysis.

As a result, however, you may see a temporary increase in the number of nulls (NAs) for certain 12m and Q1 data fields, especially during earnings season. If the latest quarterly data comes only from an earnings release, with no accompanying balance sheet or cash flow statement data, the data for these statements for that period is null (NA). This renders screens involving these quarterly fields ineffective for affected companies. For example, quarter-to-quarter comparisons will have NA on one side or the other, and tests involving Q2, Q4, Q6 fields will compare to NA.

Once a company files its latest interim financial statements (10-Q, etc.) this missing data will be populated.

Interim (Quarterly) Cash Flow Statement Period Adjustments

The raw interim (quarterly) cash flow data we receive from Thomson Reuters is a fiscal-year-to-date aggregate. So, Q3 cash flow data, in its raw form, covers three quarters of data; Q2 covers two quarters; and Q1 covers one quarter. In Stock Investor Pro 4.0, we deducted the previous quarter’s interim cash flow data from the current period to, in theory, arrive at data that covers the correct time period. In other words, deducting Q2 data, which covers two quarters, from Q3 data, which covers three quarters, leaves us with one quarter’s worth of data.

However, this adjustment did not take into account periods that were impacted by restatements or reclassifications. So Q3 may cover nine months, but because of a restatement or reclassification, Q2 covers 12 months. Therefore, the adjustments done in version 4.0 generated data covering a period that is not the same length as the quarterly income statement data.

In an attempt to overcome this, we have implemented new logic in version 4.5 for the adjustment of interim cash flow data. It starts by comparing the period lengths of each interim income statement period and interim cash flow period. If the period lengths match, no adjustment is made to the interim cash flow data for that period. This may mean that the data presented still covers a non-traditional quarterly period length, but ensures that the interim income statement and cash flow data cover the same amount of time.

If the period lengths for the interim cash flow and income statement data for a given period do not match, we attempt to make an adjustment to that period’s interim cash flow data. For example, if the period length for the income statement is three months and the period length of the cash flow data is nine months, we take the difference in period lengths (9 – 3 = 6) and look back at previous interim cash flow periods until we find a period length that matches six months. We then deduct that period’s data from the current period to arrive at cash flow data that covers the same period of time as the income statement data for that three-month period. If we do not find a prior interim cash flow period with the required period length, the cash flow data for that period is null.

The figures below illustrate the change in logic.

The figure above is taken from Stock Investor Pro 4.0, with data as of January 8, 2016. It is the quarterly (interim) cash flow data for Facebook (FB). We see that the period ended September 30, 2014, is reclassified, so that period covers nine months. However, looking at the time series in the figure, we can see that the nine-month period has data comparable to the three-month periods in the series.

Now looking at the figure below, we see the impact of the new logic. Again, we have the quarterly cash flow data for Facebook, but this time from SI Pro version 4.5. It is apparent that the cash flow data for the period ended September 30, 2014, now covers a nine-month period (as does the income statement data, not shown).

Null Handling

There are times when there are “holes” in the data we receive from Thomson Reuters. This may be due to a company not reporting specific line items or data not being available in the report being used, such as an earnings release versus an SEC report (10-Q report, etc.).

In Stock Investor Pro 4.5, we have worked to improve the way in which we handle missing data. With some database services, missing data may simply be converted to a zero. While this is an easy solution, it may lead to the end user drawing incorrect conclusions about the company’s financial state or it may lead to incorrect screening results.

Stock Investor 4.5 now only deliberately converts missing data to zeros in those cases where the company has not reported a line item across its entire time series. For example, if a company has never reported long-term debt on its balance sheet or dividends on its income statement, null (NA) values for these fields are converted to zeros.

Otherwise, if there is a missing piece of data for a given data field within a time series, this null value remains a null (NA).

By handling missing data in this way, sector and industry medians are truer comparative benchmarks than those that we would get if missing (NA) data were simply converted to zeros. This makes for more meaningful analysis and screening results.

Formula Changes for Existing Data Fields

As we migrated to the new Thomson Reuters database, some fields were not presented to us in the same manner as they were in the database used in version 4.0. In other cases, fields were not available at all. This meant, in some cases, that we had to start calculating the data ourselves. In other instances, our data investigations led us to use different component data fields for “aggregate” fields. The changes are highlighted below. You can also find these definitions in the Stock Investor Pro Help System.

Balance Sheet

  • Long-term investments (LTINV_Q1-Q8, Y1-Y7)
    • Long-term investments are those investments that will be held for one year or longer. For banks, long-term investments is the sum of long-term investments in affiliate companies and other long-term investments. For non-banks, long-term investments also include long-term notes receivable. We are now using the fields designated by Thomson Reuters as being long-term investment fields and are now excluding total investment securities, loans held for sale, other earning assets, customer acceptances, and net loans.
  • Other current assets (OCA_Q1-Q8, Y1-Y7)
    • Other current assets can include restricted cash, deferred income tax, unbilled utility revenue (utilities only), deferred gas cost (utilities only), current assets from discontinued operations, and other current assets. Other current assets can also include prepaid assets such as prepaid fees, commissions, taxes, insurance expenses, prepayments to reinsurers, rents, or other expenses. This field now excludes deferred policy acquisition costs for insurance firms, which Thomson Reuters does not designate as a current asset.
  • Other long-term assets (OLTA_Q1-Q8, Y1-Y7)
    • Other long-term assets is now the sum of deferred charges, overfunded pension benefit, deferred income tax (long term asset), discontinued operations (long-term asset), long-term restricted cash, and other long-term assets. The field now excludes items not specifically designated as long-term in nature, including securities/indebtedness of related party (insurance firms), accrued investment income (insurance firms), reinsurance assets (insurance firms), separate asset accounts (insurance only), interest receivable (bank firms), other real estate owned (bank firms), and other assets.
  • Other long-term liabilities (OLTL_Q1-Q8, Y1-Y7)
    • Other long-term liabilities represents non-interest-bearing long-term liabilities, including long-term deferred income tax, deferred investment tax credits (utilities), reserves, underfunded pension benefits, liabilities subject to compromise, negative goodwill, and financial derivatives for non-financial companies. This data field now excludes minority interest (a separate field in Stock Investor) and liabilities from discontinued operations.

Income Statement

  • Adjustments to income (ADJUST_Q1-Q8, 12M, Y1-Y7)
    • Adjustments to income now include pro forma adjustments, which represent an adjustment used to convert historical earnings to pro forma net income when a company goes public.
  • Cost of goods sold (CGS_Q1-Q8, 12M, Y1-Y7)
    • For industrial firms (those that are not utilities, banks or insurance companies), cost of goods sold now includes excise tax payments, which represent government-levied taxes on the manufacture, sale, or consumption of goods and services, which are included in the sale price, collected by the seller and returned to the government. These taxes can be imposed at any trade level and can either be a specific tax or a value-added tax (a percentage of value). Examples of industries that typically report significant excise taxes are tobacco, liquor, gasoline, tires and communications. This item is classified as a variable direct cost of goods and services sold.
  • Interest expense (INT_Q1-Q8, 12M, Y1-Y7)
    • Interest expense represents the total operating interest expense for financial institutions (banks) as well as the financial services subsidiaries of non-banking firms. For banks, interest expense includes interest in deposits, interest on other borrowings and federal funds purchased/securities sold under repurchase agreement. For non-banks to be able to report operating interest expense, their subsidiaries must primarily be engaged in financial services other than insurance operations: Although the parent company may be engaged in industrial, utility, or insurance businesses, the respective subsidiaries must be engaged in financial services in their day-to-day operations, including the provision of loans to customers. In cases where the ordinary expenses of a company are not delineated between operating and non-operating and all of the ordinary expenses are grouped together prior to pretax income, interest expense is classified as operating interest expense, which may have a similar nature to non-operating interest expense for analysis purposes. For non-banking firms, interest expense may include amortization of discounts/premiums or debt issuance expenses, capitalized from issuance of debt.
  • Unusual income (UNINC_Q1-Q8, 12M, Y1-Y7)
    • Unusual income (expense) represents unusual/non-recurring/one-off items reported above net income before taxes. The item is used for all unusual gains or losses classified as operating—i.e., for those items that are reported in the operating section. Unusual income (expense) also includes non-recurring losses or gains reported within revenues, or within the non-operating section. The only non-recurring item reported above income taxes that is not classified as unusual income (expense) is the gain or loss on the sale of fixed assets and group companies, reported in the non-operating section of the income statement. Unusual income (expense) is calculated as the sum of purchased R&D written-off, restructuring charge, litigation, impairment – assets held for use, impairment – assets held for sale, gain (loss) on sale of fixed assets, and other unusual income (expense).

Price and Share Statistics

  • Price Change 4 week (prchg_04w)
    • In SI Pro 4.5, Price Change 4-week is calculated by taking the price change from the last 19 market days.
  • Price Change 13 week (prchg_13w)
    • In SI Pro 4.5, Price Change 13-week is calculated by taking the price change from the last 63 market days.
  • Price Change 26 week (prchg_26w)
    • In SI Pro 4.5, Price Change 26-week is calculated by taking the price change from the last 126 market days.
  • Price Change  52 week (prchg_52w)
    • In SI Pro 4.5, Price Change 52-week is calculated by taking the price change from the last 252 market days.
  • Relative Strength 4 week (rs_04w)
    • In SI Pro 4.5, Relative Strength 4-week is calculated by taking the relative strength from the last 19 market days.
  • Relative Strength 13 week (rs_13w)
    • In SI Pro 4.5, Relative Strength 13-week is calculated by taking the relative strength from the last 63 market days.
  • Relative Strength 26 week (rs_26w)
    • In SI Pro 4.5, Relative Strength 26-week is calculated by taking the relative strength from the last 126 market days.
  • Relative Strength 52 week (rs_52w)
    • In SI Pro 4.5, Relative Strength 52-week is calculated by taking the relative strength from the last 252 market days.
  • Volume--Average Monthly 3m (AVM_03M)
    • In SI Pro 4.5, Volume--Average Monthly 3m is calculated by taking the sum of the volume from the last 63 market days, dividing by 3, and then dividing by 1,000 to convert to shares in thousands.

New Data Fields

The new Thomson Reuters database also gives us access to a deeper collection of data fields, which we plan on tapping into. For version 4.5, we added a new data field (and we expect to add more in the near future):

Price and Share Statistics

Shares Diluted Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8
Data Table Name: SHR_DQ1, DQ2, DQ3, DQ4, DQ5, DQ6, DQ7, DQ8
Data Category: Price and Share Statistics
Field Type: Shares in millions (0.0 to 999999.9)
Percent Rank: No
Industry/Sector Median: No

The diluted weighted average number of shares of common stock for each of the last eight fiscal quarters. This is used as the denominator for computation of diluted EPS items. Diluted EPS may be different from Basic EPS when a company reports convertible preferred stock or convertible debt. Diluted weighted average shares reflects the company’s total weighted average shares outstanding during the period, which includes the conversion of stock options, convertible preferred stock and debt.

Shares Diluted Y1, Y2, Y3, Y4, Y5, Y6, Y7
Data Table Name: SHR_DY1, DY2, DY3, DY4, DY5, DY6, DY7
Data Category: Price and Share Statistics
Field Type: Shares in millions (0.0 to 999999.9)
Percent Rank: No
Industry/Sector Median: No

The diluted weighted average number of shares of common stock for each of the last seven fiscal years. This is used as the denominator for computation of diluted EPS items. Diluted EPS may be different from Basic EPS when a company reports convertible preferred stock or convertible debt. Diluted weighted average shares reflects the company’s total weighted average shares outstanding during the period, which includes the conversion of stock options, convertible preferred stock and debt.

If you have any questions or comments about these changes and enhancements, please contact AAII Tech Support at techsupport@aaii.com.