Automated Stock Analysis Spreadsheet Changelog
- New
- On the COMPARISON tab, users now have the option to visualize stock ticker prices across 40 different countries. This will function the same for stock tickers on U.S. exchanges, but for non-US stocks, users now have the option to (1) select a country, then (2) select the appropriate exchange name that the company is trading in. If these two are correct, then you should be able to see the stock on the comparison chart.
- Updates
- "Save PDF" macro now temporarily turns formula calculations to "manual," to ensure that data/charts do not refresh or change when you're saving the sheet.
- Fixed
- On the OVERVIEW tab, the "#VALUE!" error would show sometimes for some non-US dividend-paying companies. This has been fixed, and the message now instead eithers tells you (1) the last dividend paid and the payment date, or (2) if the company has never been a dividend-paying company.
- New
- Workbook Navigation Panel: Found on the left-most and/or upper-most grouped row/column on each spreadsheet (depending on the sheet). There's now a universal navigation panel that (1) provides you with a quick overview of the inputted stock ticker, (2) shows what macros are available for the sheet you're currently on, and (3) navigation buttons to all 22 spreadsheet tabs.
- NOTE: This is hidden by default on all tabs besides the GUIDE and OVERVIEW tab. Just use the [+] and [-] grouping buttons to show/hide this panel.
- VALUATION: Not necessarily a "new" tab, as most of the info on this tab was pulled from the old DASHBOARD tab. However, it now features a weighted average option (if you want to assign different weights to valuation model outputs), an updated format/structure, and the addition of the savings rate as an input. Input your weights from M22:M31, then select "Weighted Avg" in cell H34 to use the new weighted average valuation feature.
- MANAGEMENT: This is a non-automated tab. It's a simple tab where you can input common management team and board member information from online sources for a company. Useful if you want to do a deeper-dive on a company.
- OWNERSHIP: This is a non-automated tab. It's a tab where you can input insider transactions, insider trading volume over the TTM, ownership breakdown between institutions and shareholders, and the top shareholders of the company. There are two visuals that auto-populate based on the data inputted. Like the MANAGEMENT tab, this is useful if you want to do a deeper-dive on a company.
- COMPARISON: This is a new tab where you can compare the stock ticker inputted in the OVERVIEW tab to 4 other stock tickers. You can see the differences between 60 different metrics/ratios/KPIs, as well as stock price performances. I also have a threshold-based color coding option, similar to what the KFR tab offers.
- SCREENER: This is a new basic stock screener tab. It utilizes 35 different metrics/ratios/KPIs, and references data in the SCREENER DATA tab. You can input any operator (<=, <, =, >, >=), and any value within the defined range (which is based on the stock's values in the SCREENER DATA tab). Then, click on the "Start Screener" macro button and you'll get all the applicable stocks.
- SCREENER DATA: This is a new tab that utilizes Wisesheets' Wise Screener functionality to populate all the financial data in one click. By default, I've loaded in 1000 stocks from the iShares Russell 1000 ETF. You can easily update this entire dataset using Wisesheets' Wise Screener. See their documentation if you're unfamiliar on how it works.
- Range Selection: In terms of ranges to select for the Wise Screen, select the input cells on the sheet. This is just H5:H1004 for the ticker range, and I4:AR4 for the parameter range.
- Updates
- COVER: Now has four links to all relevant pages for this spreadsheet model.
- GUIDE: Made significant updates to this sheet. Now explains (1) the requirements needed to use the workbook, (2) updated spreadsheet quick debugging tips, (3) updated spreadsheet best practices, (4) updated spreadsheet tab descriptions, (5) new section on what each macro does, (6) updated workbook cell formatting rules, and (7) an updated Excel shortcut library (now with 430+ custom Excel shortcuts!).
- OVERVIEW (previously called the DASHBOARD tab): Significant updates were made to this sheet. (1) Updated formatting and structure, (2) Can choose between =STOCKHISTORY or =WISESHEETS for stock price data/chart visualization, (3) radar chart moved to the top, no longer associated with the valuations section, (4) NEW dynamic chart visual to let you compare range of buy prices to historical and current stock prices, (5) NEW dividend info section and chart, (6) More space to see KFS and KFR ratio labels, as well as new visuals (7) Financial statements reduced to 5Y + TTM, instead of 10Y only. This keeps it as more of an overview, and lets you focus more on recent trends. (8) New chart visuals for the balance sheet that let you see (a) YoY assets and liabilities/equities change (shows % change and size, represented by length of bar chart), and (b) what consists of company's assets, liabilities, and equity over the TTM (via a treemap chart). These charts provide a better picture of YoY changes and the company's current net worth snapshot.
- KFS: Column I now has short names for each of the financial statement and supplementary data line items.
- DDMs: Removed the NCGFSM from this sheet. This was effectively the TwoSDDM, but let you manually enter in dividends over the next 1-5 years. Very uncommon model with little to no use, so I removed it, along with two data tables and other conditional formatting rules which will help speed up the spreadsheet (primarily when data tables recalculate/save). Also updated the dividend chart so it matches what's on the OVERVIEW tab, updated spacing, and removed default inputs across the models.
- BACKEND: A few tweaks were done here to make the model more functional and quick. (1) 52-week high/low for current company and all comparable companies (for COMPS model) all load within one Wisesheets API call, vs. 11 different data ranges. (2) All dividend data is now loaded on the BACKEND tab (vs. on the KFS tab). (3) All company profile data and named ranges are loaded in one Wisesheets API call now vs. 10+ separate locations. (4) Dropdowns on OVERVIEW tab are more defined vs. showing all the ratios in the spreadsheet. The data validation ranges for these sit in the BACKEND tab, and have much more simpler formulas (now using the relatively new =VSTACK formula).
- Other minor tweaks to spacing and formats were done across all of the models as well.
- Fixed
- "Auto Fit Text" Macro: No longer need to unprotect the sheet to run this macro, it's done automatically, and now gives you a status message telling you if it succeeded or not.
- On the IS, BS, CFS, KFS, and KFR tabs, the date range above the "min, median, mean, max" labels will automatically adjust (e.g., if company only has 4 years of financial history vs. 10, it will show 2019 -- 2022, not 2013 - 2022).
- #CALC! error would sometimes occur due to usage of =TODAY() function in combination with the calendar and fiscal year Wisesheets functions. Although refreshing the data would fix this, this has been fixed and no longer occurs.
- If your chosen fiscal year is different than the company's last actual fiscal year, then this should now be reflected accurately across the workbook (stock prices were not adjusting previously to show the chosen fiscal year, so ratios based on the company's stock price were slightly off).
- New
- No new significant updates.
- Updates
- Primary update for V1.5 is relying more on Wisesheets data than on =SUM formulas to solve for bottom-line numbers on the 3 financial statements (i.e., net income, total assets, cash flow from operations, etc). This has been reflected on the IS, BS, and CFS tabs, as well as the financials on the KFS and DASHBOARD tabs. This was done to improve the accuracy of the spreadsheet (e.g., revenues minus costs did not always equal net income reported on the company's 10-K, although this was rare and varied depending on the company). I've also used more arrays vs. line-by-line financials to load financial data (via Wisesheets) with fewer formulas/requests. More specifics are below:
- IS Tab: Reduced Wisesheets API requests by 2 (from 13 to 11). This sheet is more accurate as well now, as you're no longer relying on sum formulas to solve for profitability numbers (e.g., EBITDA, EBIT, Net Income, etc.).
- BS Tab: Reduced Wisesheets API requests by 17 (from 28 to 11). Again, this sheet is more accurate as well now for total balance numbers (e.g., Total Current Assets, Total Assets, Total Long-Term Liabilities, etc.).
- CFS Tab: Reduced Wisesheets API requests by 9 (from 20 to 11). Again, this sheet is more accurate as well now for total cash flow numbers (e.g., Net Operating Cash Flow, Net Investing Cash Flow, Net Financing Cash Flow, etc.).
- KFS Tab: IS, BS, and CFS section's formulas all reference their respective sheets now, instead of relying on sum formulas. Formatting has been reflected accordingly.
- DASHBOARD Tab: The IS, BS, and CFS section's formulas all reference the KFS tab now, instead of relying on sum formulas. Formatting has been reflected accordingly.
- Fixed
- There were some "#VALUE!" errors that were present on the KFS tab, for companies with less than 10 years of historical financial data. This did not cause any computation errors, it was more of a visual error that is now hidden.
- "Cash Flow Statement: Change in Cash & Cash Balances" chart on the DASHBOARD tab no longer shows zero-dollar values (as "0"), instead it just remains hidden/blank.
- Updated stock prices not displaying to show as "N/A" (previously was just outputting an error). This will only show, for reference, if a stock hadn't been publicly traded for the last 10 years.
- Reduced the number of visual errors on the DDMs tab, particularly for companies with little or no dividend history
- New
- Added the 'Exchange' name, now found on the DASHBOARD tab, on cell J39.
- Added the following Excel shortcuts (can be accessed by typing "=" into any cell): Company_Volume, Company_AvgVolume, Company_Exchange, Company_ExchangeShortName, Company_Currency, Company_Country, GICS_Sector, GICS_Industry, ISIN, CIK, CUSIP
- Stock prices and stock chart (on the DASHBOARD tab) now function for non-US stock tickers supported by Wisesheets. Note that the security comparison chart only supports stock tickers traded on U.S stock exchanges, so it will remain blank/gray if you use a non-US stock ticker.
- For easier accessibility, added the Spreadsheet Quick Debugging and Spreadsheet Best Practices sections from the documentation page to the GUIDE tab.
- Updates
- 'Stock Price (Last Trading Day of the Year)' on the KFS tab now uses =WISEPRICE instead of =STOCKHISTORY. This will ensure all price ratios function correctly (e.g., for the price-to-earnings ratio). The functionality on how this all works is on the BACKEND tab, if you're curious.
- Updated Excel shortcut guide. Now there are 407 custom excel shortcuts (e.g., if you want to know a company's last twelve months accounts payable balance, you'd select any cell and type '=TTM_AccountsPayable').
- Because of how Wiesheets dates are outputted from their API, the stock price chart had a small visual tweak (with the dates no longer being in "MMM YY" format).
- Reduced API calls from Wishesheets and sped up spreadsheet by doing the following:
- Updated 'Dividend Per Share' on the KFS tab to be one array function, vs. 10 individual formulas.
- Updated 'Exchange,' 'Exchange Short Name,' 'Currency,' and 'Country' on the DASHBOARD tab to only be one array function call, vs. 4 individual formulas.
- Updated 'ISIN,' 'CIK,' and 'CUSIP' on the DASHBOARD tab to only be one array function call, vs. 3 individual formulas.
- Updated 'Sector' and 'Industry' on the DASHBOARD tab to only be one array function call, vs. 2 individual formulas.
- Fixed
- Percent growth formula for revenue on the DASHBOARD tab was missing, now fixed.
- DCF discount rate was accidently unlinked, it has now been re-linked.
- New
- Updates
- Made IS, BS, and CFS data from Wisesheets load as ranges (aka arrays) for every line item, significantly reducing API requests and load time.
- Reduced API request for date function array to be only one request.
- Renamed "Refresh Data" macro button added in V1.2 to "Refresh Dates," as all it does is refresh the date API call from Wisesheets, so you know the most recent fiscal year for the company. Also made slight adjustment to macro code.
- Fixed
- Basic EPS and Diluted EPS were rounding with the unit selector on the IS tab, which should not be the case (as these are per-share values). This has been fixed.
- New
- Added a "Refresh Data" macro button, found on the DASHBOARD tab. This will (1) unprotect the BACKEND tab (if protected), (2) refresh the WISESHEET date values (which drives the correct analysis year date, amongst other dates), then protect the BACKEND tab (which is its default state). This should speed up the spreadsheet in the case of '#BUSY!' and/or '#CALC!' errors
- Updates
- Significantly reduced '#Value!' and '#N/A!' errors across workbook, which was more common in the case of companies with limited data.
- Fixed
- Comparable financials were not rounding correctly if you used billions or thousands, with the unit selector on the DASHBOARD tab.
- New
- Added first fiscal year calculation in BACKEND and DASHBOARD tabs.
- You're now able to change the unit number format for companies on the DASHBOARD, as you can now select between billions, millions, or thousands. This will then be reflected throughout the entire workbook.
- Added the following line items to the balance sheet to make it more thorough, from the Wisesheets API: Long Term Investments, Deferred Tax Assets, Other Assets, and Other Liabilities. Updated the DASHBOARD and KFS financial statements to reflect this change as well.
- Added =TTM and =LFY shortcuts for LongTermInvestments, OtherAssets, DeferredTaxAssets, and OtherLiabilities. These shortcut names can be found in the "GUIDE" tab.
- Updates
- Made slight formatting and error-catching updates on IS, BS, and CFS tabs. Now works better for companies with less than 10 years of publicly available financial data.
- Linked IS, BS, and CFS line item names to KFS. This reduces chances of errors.
- Reduced sheet references on the KFS tab. Items that can be calculated manually without having to reference the IS, BS, or CFS tabs are done so (e.g., gross profit), and are reflected via the workbook's formatting guide.
- Small header item that tells you what data is being represented as (e.g., "USD in Millions, except per-share data") is now dynamic, based on currency of inputted stock ticker and user-selected unit selector on DASHBOARD tab.
- Fixed
- Link on COVER is now clickable.
- Input cells in column B in IS, BS, and CFS tabs are now editable (although it's recommended that you do not edit them).
