tag:blogger.com,1999:blog-75196512426665058222024-03-05T08:09:26.778+00:00XBRL for AnalysisLooking at XBRL from an Analyst's perspectiveJim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.comBlogger49125tag:blogger.com,1999:blog-7519651242666505822.post-84696352105796792852020-11-06T14:35:00.003+00:002020-11-06T14:35:29.564+00:00Returning Tagged As Reported XBRL using an API<p>This is where you can see how a Company Reported their financial data but with tags to allow standardization, comparison and aggregation.</p><p>This is available to see in the Beryl Section Sheet. The API call brings back data as seperate records for each year but this is obviously more difficult to read and not how you would see it an report so the Power Query in the example sheet flips the years up onto the y axis of the Table as Columns. This can have the unwanted effect of inserting new columns into the sheet with each new call, as potentially each new column has different name depending on the dates of the years in question coming back from the filing.</p><p>To counteract that and to stop it getting messy basically!, we moved the Input Boxes from the top row to the first column. You can have the boxes wherever you want really. This sheet is an example, a starting point. As long as they build valid Queries, they can go wherever makes sense to you. You might want to create a separate opening sheet with all the parameters in for example.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn0giEHeH7GXNZYL9bRvfz0w8UPt8fuW0InofW7jo5EB0E09kQT4JfnyT092cIT7JOvI5TO-BrDbzxOm5HZFs8Kiu00kTdEeoAV2tL0vB3YnEu9hrkWCu9NBhN6ZIpSrBc1Fq6C9ua-ig/s246/APISection.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="123" data-original-width="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgn0giEHeH7GXNZYL9bRvfz0w8UPt8fuW0InofW7jo5EB0E09kQT4JfnyT092cIT7JOvI5TO-BrDbzxOm5HZFs8Kiu00kTdEeoAV2tL0vB3YnEu9hrkWCu9NBhN6ZIpSrBc1Fq6C9ua-ig/s0/APISection.PNG" /></a></div><br /><p>This Call uses the items/section Endpoint to return an As Reported Filing Section.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijyjMkbBzE9FKHzrfMa3D6VOdWHXGeyhLZK1eVn2Pe-iWm-VE8gFLfVMhWOiH4VDs-93CZKYZxGr5PMPOWuvNlyrcQiiHT5HoCSrUZVZTIrETz6KhEDmveFKREFqegod_e5YBUpqGPPLw/s822/APISectionResults.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="81" data-original-width="822" height="40" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijyjMkbBzE9FKHzrfMa3D6VOdWHXGeyhLZK1eVn2Pe-iWm-VE8gFLfVMhWOiH4VDs-93CZKYZxGr5PMPOWuvNlyrcQiiHT5HoCSrUZVZTIrETz6KhEDmveFKREFqegod_e5YBUpqGPPLw/w400-h40/APISectionResults.PNG" width="400" /></a></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">By choosing the Relative option "123", it will bring back all the primary periods for Report and so will the results will come back looking like the face of the Income Statement if you say choose that Section.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVjJlY2JQTayLeqnJVBaxCP7UrvZXJ48WqN9rZqipTOUz4DU1hLqeE2i2r7S_nIxqEvrIVNHEp_Xo-uE-B34-6n6YUXjnyjbBE4NtfQgqfW8ArWpAekAjZqFf_B4nbUIqvmgxfLzERcmk/s262/APISectionInput.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="253" data-original-width="262" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVjJlY2JQTayLeqnJVBaxCP7UrvZXJ48WqN9rZqipTOUz4DU1hLqeE2i2r7S_nIxqEvrIVNHEp_Xo-uE-B34-6n6YUXjnyjbBE4NtfQgqfW8ArWpAekAjZqFf_B4nbUIqvmgxfLzERcmk/s0/APISectionInput.PNG" /></a></div><br /><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">Which section gets returned is determined by which Section you choose. A drop down in the example Section sheet shows you the sections you can choose as denoted by their codes but you can actually view any section that has XBRL Values in the report by selecting it by number. Just visit the Filing Sections sheet see which Sections are available for a filing.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><br /><p><br /></p><p><br /></p>Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-82427012068592209442020-11-06T11:04:00.004+00:002020-11-06T11:05:46.670+00:00Using an API Call to get an Overview of the XBRL contained in FilingWe have a very simple call that enables you to quickly see which sections of data are available as XBRL in a filing. This is particularly helpful when used in conjunction with items/sections Endpoint. That Endpoint brings back all the XBRL values disclosed in a chosen section along with all the presentational data to display it as it was reported (e.g. show me in the Income Statement for 2019. What you are looking at here when you do that is all the same XBRL used by the SEC to construct their Interactive Data pages for a filing.<div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfpIxgQQzUhQZgGto5bgOuY17AYB1hKpmIF-ByNk7kzGGY6Jg0-pR14D5JdZm7GOqPNq1DzdcCQfkW6iv8I0QLDZGVepUrafpGxodFLO9LFQ1YF2qOT5GTimfqFiu8FuG-ioBPZXE-CRA/s509/APIFilingSectsBoxes.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="74" data-original-width="509" height="59" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfpIxgQQzUhQZgGto5bgOuY17AYB1hKpmIF-ByNk7kzGGY6Jg0-pR14D5JdZm7GOqPNq1DzdcCQfkW6iv8I0QLDZGVepUrafpGxodFLO9LFQ1YF2qOT5GTimfqFiu8FuG-ioBPZXE-CRA/w400-h59/APIFilingSectsBoxes.PNG" width="400" /></a></div><br /><div>Use the Endpoint sections as shown, choose a company in the Co Box and select a Date for the filing. You can also choose further along the row in the Annual? and Quarter Boxes whether you want to see the Annual Filing (e.g. 10-K or 20-F - just select "yr", otherwise "q") or choose which quarter you wish to see the filing for. By default it will bring back the annual filing.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7T1GgYCkX3vn4VUHt1EdcMhkin9FHONj0_N_hvkapro6yiXS7hlWVWp5pKhuT7Qszy_1rsWXlAJt5xC48SlUToY-hK_LIev3WiJZjUKPPuZCvsCkt7OaDvkFbm2M3VaiGaWOyGGlSl5s/s676/APIFilingSectsResults.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="96" data-original-width="676" height="56" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7T1GgYCkX3vn4VUHt1EdcMhkin9FHONj0_N_hvkapro6yiXS7hlWVWp5pKhuT7Qszy_1rsWXlAJt5xC48SlUToY-hK_LIev3WiJZjUKPPuZCvsCkt7OaDvkFbm2M3VaiGaWOyGGlSl5s/w400-h56/APIFilingSectsResults.PNG" width="400" /></a></div><div><br /></div>The results, as shown above, give you a list of all the Sections by name and by Tag. Unlike (in theory!), XBRL Values, Sections returned from the SEC data are not tagged, very bizarrely, with a set of standard tags. So we have stepped in to supply our own Standard Section Tags and to make things even easier, especially when summoning a section, we've turned these into a series of short codes. You can see these displayed in the Code column when you a return list of Sections. Each Section is also numbered according to its display order (No). This is useful when summoning a more obscure Section that does not have a Code. Line_Total next to it will give you a handle on whether a Section is likely to contain any useful XBRL (Total lines of XBRL contained in a Section). You can also quickly verify that the data returned for a Section is as it should be by following the "Link" to the same page shown in SEC Interactive Data for a filing. It should be as the data source is the same - the XBRL filed at the SEC.<br /><div><br /></div>Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-88082969037305666262020-11-05T12:17:00.002+00:002020-11-05T13:27:21.725+00:00Getting Tickers and Other Company Details Back with an XBRL API Data Call<p> </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTKcCDsbTvWUZWB2qI_7qVKm6y0JN7Zb96gOw_flr11FbvayI2FDMj8ClE4dhr1Uc_gh4wiTQwr2Hv2UJPc7X_BqCzsacatdL6Mx1Rd9mktjcv4i7Ibwol72YdidgRSrxE_lOh5-gL4Vg/s481/APICosTryBox.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="58" data-original-width="481" height="49" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTKcCDsbTvWUZWB2qI_7qVKm6y0JN7Zb96gOw_flr11FbvayI2FDMj8ClE4dhr1Uc_gh4wiTQwr2Hv2UJPc7X_BqCzsacatdL6Mx1Rd9mktjcv4i7Ibwol72YdidgRSrxE_lOh5-gL4Vg/w400-h49/APICosTryBox.PNG" width="400" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><br /></div>There are three relevant Endpoints when trying to find a company to download.<p></p><p>1. cos/try (as shown above) enables you to search on a company name. Input at least five letters into the Company Box and it will return a list if matching companies when you refresh the query (Alt F5) as shown below.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9d7e2iASWQyw8Z8BIagKRg6mRY7Po3vwOJRF4qgOgNdk1ea34NoEqjS3b7Z8lioBY1Siz_Jiwi_npQQ1DpNi9c6yP_HsM8Z_513vxJjmzPCGc0rmfonzIrZl0c67yebNgZnBj7kY_5C0/s831/APICosResults.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="68" data-original-width="831" height="32" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9d7e2iASWQyw8Z8BIagKRg6mRY7Po3vwOJRF4qgOgNdk1ea34NoEqjS3b7Z8lioBY1Siz_Jiwi_npQQ1DpNi9c6yP_HsM8Z_513vxJjmzPCGc0rmfonzIrZl0c67yebNgZnBj7kY_5C0/w400-h32/APICosResults.PNG" width="400" /></a></div><p>2. cos/ will search by Ticker or CIK to return an exact match. In the case of a Ticker, it may return more than one result as it will show all the different companies that have used that Ticker since Companies have filed XBRL. The most common reason why two companies may have used the same Ticker would be where a company has morphed into a different legal entity so two companies, two CIK's. Don't worry when bringing back results we handle this by mapping the same Ticker to both companies to bring back a seamless series of results. Note: CIK's never change whereas Tickers for a company can.</p><p>3. cos/industry. You can use a SIC code with this Endpoint to bring back all companies with the same SIC. You could copy then SIC code for a target company into the industry box, select this Endpoint and the query will return all the companies operating in the same industry.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjQUNhrI4ElNrmFW4bfiKzs0i4MAkQKnDVW0bpXOGy83wbU-mEQ0YRbZ6vdlcl6_3yld9c0WGwwrMtwdsedv7KZoZiY4j5u4CtKJOUtjr_QHwjNq0whsR4UQjAW6OdvTU_4C9_7dMm8tI/s239/APIIndustryCopy.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="148" data-original-width="239" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjQUNhrI4ElNrmFW4bfiKzs0i4MAkQKnDVW0bpXOGy83wbU-mEQ0YRbZ6vdlcl6_3yld9c0WGwwrMtwdsedv7KZoZiY4j5u4CtKJOUtjr_QHwjNq0whsR4UQjAW6OdvTU_4C9_7dMm8tI/s0/APIIndustryCopy.PNG" /></a></div><br /><p><br /></p>Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-50148872116315986512020-06-17T00:13:00.004+01:002020-06-19T13:32:59.194+01:00XBRL API Quarterly Data Call<div>As explained in our <a href="https://www.blog.xbrlxl.com/2020/06/getting-started-with-xbrl-api-data-call.html" target="_blank">Getting Started</a> guide, We have set up an Excel sheet to make it really easy to start quickly pulling data back using Beryl, our API. All you have to do is fill in the coloured boxes in any of the sheets and run the query to bring back a time series for your chosen data point.</div><div><br />
</div><div>An API call for quarterly data works in much the same way as for annual values. However to bring back Quarterlies, three more boxes come into play. except this time they are black.</div><div><br />
</div><div><span style="background-color: #9e9e9e; color: black;"><b> Black Boxes </b></span> - these either represent fixed elements of the API path or ones that have a small set of finite values so we have rigged them up as drop downs in Excel.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5Irf9qvffQxi3Sb2gwqd8pjHjMeLcdwHlECTj_harUkdImJzHEGkFqHbvyTi8oDeIMebWw2AJIfgiV_n39tXsNxnvdhs6JOrdrgTHtF_np-p1v0_j5wR8rbGfhv81z9qcupuCinvcY68/s532/APISheetQBlackBox.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="62" data-original-width="532" height="46" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5Irf9qvffQxi3Sb2gwqd8pjHjMeLcdwHlECTj_harUkdImJzHEGkFqHbvyTi8oDeIMebWw2AJIfgiV_n39tXsNxnvdhs6JOrdrgTHtF_np-p1v0_j5wR8rbGfhv81z9qcupuCinvcY68/w400-h46/APISheetQBlackBox.png" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div><span style="background-color: #9e9e9e; color: black;"><b> Black Box </b></span> - Annual? Default value is "yr" but you can select "q" to see quarterly values instead.</div><div><br />
</div><div><div><span style="background-color: #9e9e9e; color: black;"><b> Black Box </b></span> - Quarter. Selecting "q" brings this box into play where you can select which quarter 1,2, or 3. You can also specify "123" to get all quarterlies for a year. NOTE: "12" etc will not bring back 1st and 2nd Quarters. Only "123" works for multiple quarters in a year. If this is set to "4", and the next box is set to "ytd", it will bring back the annual figures.</div><div><br />
</div></div><div><div><span style="background-color: #9e9e9e; color: black;"><b> Black Box </b></span> - YTD. Setting this to "ytd" as opposed to blank will bring back cumulative figures for the year - the Year To Date.</div><div><br />
</div><div><font color="#f7cb4d">Orange Boxes</font> work just like they do for annual amounts. So if you set the date to 2019, it will bring back the quarter you selected (e.g. 2) for the reporting year ending in that year. And if you set Years to 5, you will get a times series of quarter 2's going back 5 years.</div><div><br />
</div></div> Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-28601497773232052592020-06-15T22:18:00.003+01:002020-06-17T00:17:50.645+01:00Getting started with an XBRL API Data Call<i>(if you've stumbled across this article and want to know more, email us and I can add you to our beta programme)</i><br /><div><br /></div><div>We have set up an Excel sheet to make it really easy to start quickly pulling data back using Beryl, our API, or indeed, the XBRL US version.</div><div><br /></div><div>All you have to do is fill in the coloured boxes in any of the sheets and run the query to bring back a time series for your chosen data point.</div><div><br /></div><div>For our API you will need a token which you can get from our website by registering or signing in, which then goes in the <b><font color="#d52c1f">Red Box</font></b>.</div><div><br /></div><div>To run the query, move to a cell in the grey area where the results are. Right click and choose refresh query or use the keyboard shortcut ALT F5. In Excel the API call is made using Power Query.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtyi2WkeefB_Ok6NTzXo2tM9IHmb5LHcBzSXSPISFwKiCQjZ4NAKn1xzhdzy1ZFW98cm7oV0MkkouvWTXILWm_Yp6aRpU3_-7y1cJVOkxhR65NHtj_QZgHqRSviV5btJn6muBPWPrTg6Y/s247/APISheetGreenBox.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="63" data-original-width="247" height="51" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtyi2WkeefB_Ok6NTzXo2tM9IHmb5LHcBzSXSPISFwKiCQjZ4NAKn1xzhdzy1ZFW98cm7oV0MkkouvWTXILWm_Yp6aRpU3_-7y1cJVOkxhR65NHtj_QZgHqRSviV5btJn6muBPWPrTg6Y/w200-h51/APISheetGreenBox.png" width="200" /></a></div><div><br /></div><div><b><font color="#41b375">Green Box</font></b> - Tag. Choose a data item by entering an XBRL tag. We have constructed a data set of the most commonly used tags that you are likely to need called the X Financials. These are listed on a sheet at the back of the Workbook. If you want to see all the tags you could possibly ever use! then got to an online XBRL Taxonomy (there are quite a few free ones around). I use the <a href="https://app.wdesk.com/taxonomy-analyzer/" target="_blank">Workiva Wdesk Taxonomy Analyser</a>. You can also just enter "All" in this box and it will bring back data for all the items used the companies you select.</div><div><br /></div><div><font color="#41b375"><b>Green Box</b></font> - "All". If you don't know which tag to select or need to get familiar with the tags in use, you can put "All" in the Green Box as the Tag.. Be aware that if a value lies off the main axis (sorry getting a bit technical here), then these values will not get returned with this call. This only applies to more obscure values that might have been disclosed in a more complex table in the notes. A full explanation of axis and dimensions is explained in another article. Values on these axis will be made available in future. We want to make sure we do this in a meaningful way that doesn't bombard you with incomprehensive incomparable data</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgt1qHUvp-ZK6wq5z0kG7wPT2nOGvoMzApyeveH3rNFkj3S0ux6HRMHcwaQIAP8w2XC_0mCnro54EKvzKOs_2zddbcVp34UNnB-kdW3ICbVA563qWqhOcJf0CRCe8z3sxYyzSpWs6YQ6Qs/s532/APISheetOrangeBox.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="58" data-original-width="532" height="44" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgt1qHUvp-ZK6wq5z0kG7wPT2nOGvoMzApyeveH3rNFkj3S0ux6HRMHcwaQIAP8w2XC_0mCnro54EKvzKOs_2zddbcVp34UNnB-kdW3ICbVA563qWqhOcJf0CRCe8z3sxYyzSpWs6YQ6Qs/w400-h44/APISheetOrangeBox.png" width="400" /></a></div><div style="text-align: left;"><br /></div><div><b><font color="#f7cb4d">Orange Box</font></b> - Date. Choose a year for the values that you want returned. It will get values from filings with a reporting period ending in that year. So specifying 20191231 will return values from a 10-K with a year end up to and including that date. If you just enter 2019, it will do the same. If you enter 20190630, it will bring back values for filing periods ending between 20190630 and 20180701.</div><div><br /></div><div><b><font color="#f7cb4d">Orange Box</font></b> - Years. The other (optional) Orange Box, Years, determines how many years of values are bought back. So the Date box specifies the starting year and Years how many periods are returned in the series. If no Years are specified, it will bring back one year of values.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_wyODaxIIoZ4di-byDu1oFSsEXvXMgvzrIoruk1ChYM0yFUanTzuMI6MH2h-77EzJKSmTWUAHCtOSTzdrLJqaH4IPbhBQkVrKgNJJEAPBgE-UOLb_dhWrB4Kk1zSUTpgGTPCez9RX5ZA/s98/APISheetBlueBox.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="85" data-original-width="98" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_wyODaxIIoZ4di-byDu1oFSsEXvXMgvzrIoruk1ChYM0yFUanTzuMI6MH2h-77EzJKSmTWUAHCtOSTzdrLJqaH4IPbhBQkVrKgNJJEAPBgE-UOLb_dhWrB4Kk1zSUTpgGTPCez9RX5ZA/d/APISheetBlueBox.png" /></a></div><div><br /></div><div><font color="#7baaf7"><b>Blue Box</b></font> - Cos. Enables you to specify which companies you want values returned for. This is where the beauty of modern Excel kicks in. Add a ticker to the bottom of the list and the Excel Table (The Blue Box) automatically expands to include it. Companies have to be specified by ticker, but don't worry if you haven't got the ticker at hand, you can quickly look them up using our API on the Beryl Cos sheet. You can always reduce the number of companies returned by pulling the Table back up to its original size using the triangular (ish) handle at the bottom right corner of the Table.</div><div><br /></div><div>Also read - A Walk Around the Results Returned by an API Data Call (coming soon).</div><div><br /></div><div><br /></div><div><br /></div>Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-70341408855837006332019-04-26T13:50:00.005+01:002020-06-19T10:23:43.408+01:00How does the new Excel Linked Data Type Stocks stack up?Late in 2018 Microsoft rolled out a new concept - Linked Data Types. The first two types to be taken out of this shiny new box are Stocks and Geography. They are in effect connections to external cloud data sources with the initial connections curated using Microsoft Bing AI.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgue-0mR0KHdYoulkM0Y46UEulSrqpZPfTnYFFREwqXsE-QLVdWX6kjAJE5Gzf8JQNQjU2QfN-frwrsjPqixShE3o_0U1Q1qNQCwLGz20C6V4u3tuC_XypC52YxY0L8mbKUkWlE2e_llhA/s1600/StocksDataTypeCard.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="540" data-original-width="564" height="305" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgue-0mR0KHdYoulkM0Y46UEulSrqpZPfTnYFFREwqXsE-QLVdWX6kjAJE5Gzf8JQNQjU2QfN-frwrsjPqixShE3o_0U1Q1qNQCwLGz20C6V4u3tuC_XypC52YxY0L8mbKUkWlE2e_llhA/s320/StocksDataTypeCard.png" width="320" /></a></div>
<br />
So are they any good? Well not surprisingly my interest is in the Stocks Data Type.<br />
<br />
More info on how it all works and an introduction to what you can do with this Data Type can be found in the following links:<br />
<br />
<a href="https://support.office.com/en-ie/article/excel-data-types-stocks-and-geography-61a33056-9935-484f-8ac8-f1a89e210877">Office 365 Support - Excel data types: Stocks and geography</a><br />
<br />
<a href="https://sfmagazine.com/post-entry/january-2019-excel-stocks-data-type/">Strategic Finance - Excel: Stocks Data Type</a><br />
<br />
<a href="https://office-watch.com/2018/microsofts-excels-stock-data-type-is-premature/">Office Watch - Is Excel Stocks Data Type Premature?</a><br />
<br />
I've been playing with this data on and off for a few weeks now and, going a bit deeper, I wanted to look at the good and the bad.<br />
<br />
My interest was in using it as a front end stock selector for bringing back detailed XBRL financials from one of the APIs out there that enable you to do just that (try out the <a href="https://www.blog.xbrlxl.com/2019/03/xbrl-us-api.html">XBRL US API</a> via our Excel template). These invariably work off a Ticker (or other identifier). But you don't always know the ticker so if the Stocks Data Type could magic up an instant ticker this would be a pretty nifty solution. And you can see it doing exactly that in this <a href="https://youtu.be/9kIzZhc22LU">video</a>.<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
So I tried this out on a handy but somewhat out of date list of 1000 bigger companies, essentially by converting this list of names into Linked Stocks (Select All/Click Stocks Data Type on the Data Ribbon)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJ4Df1Iqr48OQieL6gSLUVPGHEt02o1apKN_ek2QY9CYG17aBZFOXJJBoC0XbJiRvRO3swNFh1DdJowC_1ye1Jb6-EB4Ap-WNoit5SyOZJ5pe0KLNPb72QlAJUNMRJeN1os76SbSCShdw/s1600/FoxStocksDataType.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="296" data-original-width="644" height="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJ4Df1Iqr48OQieL6gSLUVPGHEt02o1apKN_ek2QY9CYG17aBZFOXJJBoC0XbJiRvRO3swNFh1DdJowC_1ye1Jb6-EB4Ap-WNoit5SyOZJ5pe0KLNPb72QlAJUNMRJeN1os76SbSCShdw/s320/FoxStocksDataType.png" width="320" /></a></div>
<br />
I had a problem in roughly 300 cases (30%). 10% was due to the fact that some of these companies were no longer listed (merged/acquired etc). So lesson 1, Stocks only works with live companies (well I suppose it is stocks after all!). So I was down to a problem with 20%. For half of these (10% of the whole), it offered me some alternatives via the Data Selector Pane that pops up if no good match is found. What was surprising was it often offered me a choice when I felt it didn't need to e.g. Fox Corp; what was the clear cut choice was on the list and it should have gone straight for it, no quibbles, like it had for the other 80% of live companies.<br />
<br />
And I got there in the end with the final 10% using a couple of strategies:<br />
<ul>
<li>Feeding it a ticker instead (kinda defeated the point as this was supposed to be the output!)*</li>
<li>Removing words (taking it out "The", "Company", "Corporation", "LP" was particularly fruitful - funny kind of intelligence as you'd have thought removing noisy words would be the very first thing Bing did)</li>
</ul>
<div><i><font size="2">*Note you can be sure to get the correct quote more often by prefixing the ticker with the exchange followed by a colon or a two digit country code.</font></i></div><div><br /></div><div>
I was perplexed why for these 100 companies, it gave me no choices to pick at all. Take Deere & Co - I fed it John Deere and it give me not a single option.</div>
<br />
<div>
Another thing to note with Bing is that it loves a bit of context. Feed it Ford and it gets stuck - is it Ford Motor Company or Forward Industries (Ticker = Ford) but put together a table of Auto Industry companies and add Ford underneath it and it uses its intelligence to automatically convert it to a new row containing Ford Motor Company.<br />
<br />
All of this was done with data from their new supplier - Refinitiv (formerly known as Thomson Reuters before the Image Consultants got to work). They had to rather quickly change from their previous suppliers, Morningstar.due to data issues.</div>
<br />
What's good about it is the functionality works really well. I like it. What lets it down at the moment is the AI and sometimes the data. The good thing is that the AI ,by its nature, will get better. When I first started asking Alexa to play songs by the British band James, she would torture me with songs by James Arthur. After admonishing her for several months for her crass stupidity, she finally learnt the error of her ways. I bet in a years time, Bing won't be quite so easily defeated by John Deere.<br />
<br />
<b>Missing data</b><br />
<br />
Some surprises here given the quality of the source.<br />
<br />
For example the employees figure for Fox was missing but I could easily find it elsewhere. Note you won't always realise the data is missing if you just look at the cards as these only show the values that are actually present.<br />
<br />
A lot of data was missing for oil companies which was odd. e.g. Enterprise Products Partners has no industry classification. Peculiar as the industry classification system used by Refinitiv is TRBC (Thomson Reuters Business Classification) i.e. their own so you wouldn't have thought that any large companies would be unclassified.<br />
<br />
<b>Usage limitations?</b><br />
<br />
A word of CAUTION. You will notice what looks like a thin little disclaimer appears when you first add a Linked Stock Data Type:<br />
<br />
<div style="text-align: center;">
<i>Financial market information is provided as is and not for trading purposes or advice.</i></div>
<br />
Annoying enough that you have to click to remove this desktop hogging message but that's only the half of it. Its not a "stocks may go down as well as up" type of message or not just that old chestnut of how they can never be held responsible if you, heaven forbid, decide to trade on the bits of dodgy data that they never got round to cleaning up, that you yourself should have spotted.<br />
<br />
As it says, click on the link and you will learn more about our data sources. And you will learn what not for trading purposes or advice really means. It means you LEGALLY can't use it to do these things, well certainly if you do it for a living or carry out any kind of financial function.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiR7t9Kc5GoYb_D8vy2CjWMRuWWqsk2-r9_ztYY3DPA6v5rALGVelEc8-B-Yo5yMEnYzDYngx-0ONcaLLQ8lAnUxZRI__dWxus4u-_gLe0GIuU2e1R2q64IEtQ9pl4TvDu-etCbsIyaB_0/s1600/StocksDataTypeUsage.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="260" data-original-width="622" height="133" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiR7t9Kc5GoYb_D8vy2CjWMRuWWqsk2-r9_ztYY3DPA6v5rALGVelEc8-B-Yo5yMEnYzDYngx-0ONcaLLQ8lAnUxZRI__dWxus4u-_gLe0GIuU2e1R2q64IEtQ9pl4TvDu-etCbsIyaB_0/s320/StocksDataTypeUsage.png" width="320" /></a></div>
<br />
So this is all a bit confusing given that these professions are by far and away the biggest users of Excel. Do they tip toe round this functionality? Ordered not to use it by Legal on pain of death?<br />
<br />
Whether this has any legal legs is I imagine debatable, given that you are not informed about this up front and this functionality is now baked into a product that to my knowledge has never had any hidden restrictions over what you could do with it before. I only looked because I'm in the financial information business so went sniffing around for this classic little stitch up.<br />
<br />
So in theory, all you can do is look at it. So you have to question the point of it being in Excel at all. In practice I suspect you can get away with doing an awful lot more with it but you would have to question whether you could, say share a workbook with Stock Types in it or data derived thereof. Could this per se constitute advice?Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-72281112025547976372019-03-15T13:54:00.002+00:002019-03-15T14:34:33.357+00:00XBRL US APINow here's a great way to access reported XBRL, as filed with the SEC. <a href="https://xbrl.us/">XBRL US</a> have been databasing filed XBRL since day one and last year they published an accessible restful API designed to make their extensive database available to a wider community.<br />
<br />
Now out of beta and with a small but burgeoning <a href="https://xbrl.us/home/use/xbrl-api-community/">community</a>, the <a href="https://xbrl.us/home/use/xbrl-api/">XBRL US API</a> provides an excellent way to get hold of individual pieces of XBRL via relatively straight forward http API requests. Pretty much everything is there, although it can take some time and experimentation to get all the pieces together you want, given the breadth and complexity of the dataset.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB0ugqEyf0xLipQ4SGBukuEDtPzT6jPi_doA1My4qbcJhZMKz6g-jkV3qPExXvC2G-zcYtY8X2gVDBx446qoKi-TZP7Mc9sFSEVhNblSL2tW2X1WNNVziOcB5LGQm5fneiTtcqwJXwYtM/s1600/XBRLUSAPI2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="129" data-original-width="1268" height="50" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB0ugqEyf0xLipQ4SGBukuEDtPzT6jPi_doA1My4qbcJhZMKz6g-jkV3qPExXvC2G-zcYtY8X2gVDBx446qoKi-TZP7Mc9sFSEVhNblSL2tW2X1WNNVziOcB5LGQm5fneiTtcqwJXwYtM/s400/XBRLUSAPI2.PNG" width="480" /></a></div>
<br />
But never fear, there are two handy templates to get you up and running from day one, one for Excel (which we at <a href="http://fundamentalx.co.uk/">Fundamental X</a> have provided) and one for Google Sheets.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-v9RXVXHy9w4rCMD4vZX4InDIyKBH1Sf1WPEjmqDTz6n8x96DWHYYmevRom4RiC83vDoPaynCb51DC9rGf0tBgtrnYxAecKz2ztdF6SxVgjKC8n7q0TskU484iBjXuE5AN26ng-k08tA/s1600/XBRLUSAPI1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="271" data-original-width="1052" height="125" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-v9RXVXHy9w4rCMD4vZX4InDIyKBH1Sf1WPEjmqDTz6n8x96DWHYYmevRom4RiC83vDoPaynCb51DC9rGf0tBgtrnYxAecKz2ztdF6SxVgjKC8n7q0TskU484iBjXuE5AN26ng-k08tA/s400/XBRLUSAPI1.PNG" width="480" /></a></div>
<br />
The <a href="https://xbrl.us/xbrl-api-excel">Excel</a> sheet shows you how you can quickly use Power Query as a front end to SEC filed XBRL. See how to immediately get going in this <a href="https://youtu.be/taoE_hDq6LM">video</a>.<br />
<br />
<b>Things to note:</b><br />
<ul>
<li>As filed, warts and all. So all the errors and missing data is present (or not!)</li>
<li>Some limits to the free version (see pricing below)</li>
<li>Authentication token required with each request and needs updating every hour</li>
<li>Initial registration and Authentication setup a little tortuous (4 bits of info required)*</li>
</ul>
<div>
*Easier with the Official Google Sheets template as just email and password required via in app login (although new login required each time token expires) and with the Excel template setup is required only once with the option to generate new Authentication tokens automatically.</div>
<div>
<br /></div>
<div>
<b>Pricing (individuals)</b> - see also <a href="https://xbrl.us/membership/benefits/">XBRL US Membership</a></div>
<div>
<ol>
<li>Free - Registration required - Limits: Max 1000** records returned per request; Non-commercial use*.</li>
<li>Individual Membership - $55 a year - Limits: Max 2000** records returned per request; Non-commercial use*.</li>
<li>Power User Membership - $250 a year - Limits: No record limits; Non-commercial use*.</li>
<li>Sole Practitioner Membership - $500 a year - Limits: No record limits; Commercial use allowed*.</li>
</ol>
</div>
<div>
*If you are an investment professional who trades on behalf or advises others then you would need to purchase Sole Practitioner Membership. Different rates apply for corporates. Commercial use also allows you to share the data with your clients, with attribution. Redistribution is not possible.</div>
<div>
<br /></div>
<div>
**The record limit may not necessarily be a problem. It may just require you to make more individual requests and frame them more carefully. There may also be a cap on the number of requests made within certain timescales with Google Sheets.</div>
Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-15090042682761271262019-01-23T13:45:00.000+00:002019-01-23T18:54:39.610+00:00Fixing XBRL to Build a Set of Investment Grade Financials in which you can have Total ConfidenceSo as promised, here's how you fix the XBRL to get your very own set of investment grade financials. See my last <a href="https://www.blog.xbrlxl.com/2019/01/why-verified-and-adjusted-xbrl-is-best.html">post</a> as to why you need to do this.<br />
<br />
This of course is also a sales pitch. We have a set of tools that we think does just that.<br />
<br />
I've chosen a very simple single ratio model so you can see exactly what's going on and how easy it is to improve on the XBRL tagging filed by companies. I'm using our fully featured <a href="https://www.xbrlxl.com/products.vbhtml">totaliZd</a> product to do this and you can download both a <a href="https://www.xbrlxl.com/downloads/XSheetPowerTotaliZd3MConglomMess.xlsx">before</a> and <a href="https://www.xbrlxl.com/downloads/XSheetPowerTotaliZd3MConglomFixed.xlsx">after</a> <b>X Sheet</b> to see the impact of these tools.<br />
<br />
So lets say I'm a strong believer that research & development drives future growth. How does my target company stack up against its competitors?<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6zSiWWnMWHeR7PzbrlSfybTMkcWgmmq99joc6lmuDN73Q9JyVrAFFN8qEhcqpnQheArKWOYvCH0tBHcee4WV6IKfxmxERSm7H2d05Or2vOyrB6hmPcCwntJvGgI0NknGOePwVmQPL-C8/s1600/Ext3mConglomMess.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="156" data-original-width="1016" height="61" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6zSiWWnMWHeR7PzbrlSfybTMkcWgmmq99joc6lmuDN73Q9JyVrAFFN8qEhcqpnQheArKWOYvCH0tBHcee4WV6IKfxmxERSm7H2d05Or2vOyrB6hmPcCwntJvGgI0NknGOePwVmQPL-C8/s400/Ext3mConglomMess.PNG" width="500" /></a></div>
<br />
Well I don't know, because as a result of extensions (and standard totals missing from as reported filed financials - a common problem), my model in Excel is currently a mess.<br />
<br />
Now to give this analysis some credence, I've chosen the first company with a significant extension issue off our previous list of the DJ30 companies and found four of its competitors against which to compare. And I'm getting not a single R&D figure, coming through from the XBRL on the face of the financial statements, for any of them!<br />
<br />
This is not uncommon behaviour for a peer group of companies. They watch each others disclosures and follow suit. There is even a button in the leading brands of XBRL creation software to allow you to do this! This is good and bad. Bad that it happens in the first place. Good in that it makes it easier to fix - we can quickly roll out the same change across the whole group.<br />
<br />
Now at this point I would recommend you watch the <a href="https://youtu.be/J-JYvpQM9b8">video</a> to see how to do this and how quickly it can be done. I was able to make the change, from model to filing and back again, for 4 companies in under 30 seconds.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrZPNAaZNSCChxEHIKIHiMKSTtP5eu6XQ49jsRQLJWnbIJBqiidyWaaoMeI8q5OmaYK7yzrIp3Bto2OfmY2O60mYBqIwq1ilF6f4hPlRB9De0Pt6CcZ4-YcEbmup4knH9BkBIwqDhNy30/s1600/Ext3mConglomFix.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="75" data-original-width="1220" height="30" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrZPNAaZNSCChxEHIKIHiMKSTtP5eu6XQ49jsRQLJWnbIJBqiidyWaaoMeI8q5OmaYK7yzrIp3Bto2OfmY2O60mYBqIwq1ilF6f4hPlRB9De0Pt6CcZ4-YcEbmup4knH9BkBIwqDhNy30/s400/Ext3mConglomFix.PNG" width="500" /></a></div>
<br />
totaliZd allows for layers of tagging so we never change the sacred as reported numbers and tags, we just build layers on top until we get exactly what we want, a view of the company that truly reflects what we wish to see. This way we can always get back to the genuine source figures. So we pick up the tag we want from our handy set of X Financials (broad set of the most commonly used set of values and adjustments used in modelling companies) and plonk it in the User Tag column. Job done. And when you have finished, this is what you will end up with.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2wZVoUlS0j1FQZlEJBT35v4jxnprTIbG-ukvznzFVoVgGTLJ7A-0r7SrPbfvF2VYwUP8E7AZCz0hJyDXGkijXGXKIgMVI6t8fJCawj6DPUmVTpyCCm-iSrh7sik-ZZhYrj3zPmglBeeU/s1600/Ext3mConglomFixedAll.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="156" data-original-width="1005" height="61" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2wZVoUlS0j1FQZlEJBT35v4jxnprTIbG-ukvznzFVoVgGTLJ7A-0r7SrPbfvF2VYwUP8E7AZCz0hJyDXGkijXGXKIgMVI6t8fJCawj6DPUmVTpyCCm-iSrh7sik-ZZhYrj3zPmglBeeU/s400/Ext3mConglomFixedAll.PNG" width="500" /></a></div>
<br />
<br />
So continuing our narrative, 3M looks like a poor performer compared to Corning, so based on our (very) simple and crude analysis, we should instead back Corning to generate greater revenues in the future.<br />
<br />
Now we have only changed one tag here. And we can leave it at that if that's all we want to look at. But what other changes might we have to make, if we wanted to fully prepare 3M, to create a full set of VAXBRL (Verified & Adjusted XBRL), ready for any analytical challenges we might want to throw at our target company? Well if you look at the <a href="https://www.xbrlxl.com/downloads/XSheetPowerTotaliZd3MConglomFixed.xlsx">X Sheet</a> after adjustments, you will see I had to add only five other tags to turn dubious as reported XBRL into Verified & Adjusted XBRL.<br />
<br />
As the name implies, part of the process is Verifying the accuracy and validity of the XBRL and this is what the totaliZd sheet enables you to do. One glance at this sheet tells you everything is tickety boo and good to go. More on this in a later post - suffice to say there is an awful lot of checking going on here (we instigate a triple check procedure that enables you to look at the XBRL from three angles).<br />
<br />
totaliZd enables you to adjust the XBRL, not to change it, but improve on it to match your own modelling requirements. User Tagging creates a backstop so no matter what the company churns out as a filing, you can easily fix or improve on it. You can make as many or as few adjustments as you wish and they will all be reflected in the totaliZd validation. You are in control, enabling you to decide what values go into your models. This way you can create a set of Financials which surpass any that you might purchase from the likes of Capital IQ or Bloombergs. This is serious value for money.<br />
<br />
So confident are we that it will change the way you analyse companies, we have decided to offer a 60 day free trial of totaliZd (no credit card details or prepayment required). Just email us at totaliZd@fundamentalx.co.uk to take up this offer.<br />
<br />
<i>This post is part of series that started <a href="https://www.blog.xbrlxl.com/2019/01/which-source-of-company-financial-data.html">here</a> and is a follow on to my last post - <a href="https://www.blog.xbrlxl.com/2019/01/why-verified-and-adjusted-xbrl-is-best.html">Why Verified and Adjusted XBRL is the Best Choice for Company Analysis</a></i><br />
<br />
<br />
<br />Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com2tag:blogger.com,1999:blog-7519651242666505822.post-55274806025249249472019-01-17T13:49:00.000+00:002019-01-23T18:52:05.573+00:00Why Verified and Adjusted XBRL is the Best Choice for Company AnalysisIf you read my last post <a href="https://www.blog.xbrlxl.com/2019/01/does-current-use-of-extensions-make.html">Do Extensions make XBRL Unusable out of the Box?</a>, you will know that using XBRL without adjusting the tagging could well be calamitous.<br />
<br />
So what are your options? Well in the post before that <a href="https://www.blog.xbrlxl.com/2019/01/which-source-of-company-financial-data.html">Which Source of Company Financial Data should I use?</a>, I set out the options for finding the best source for comparative analysis. Lets re-iterate them here but I'm also gonna add one further choice.<br />
<br />
1.<span style="white-space: pre;"> </span>Lifting values straight from the Financial Reports filed by a company<br />
2.<span style="white-space: pre;"> </span>Data Vendor - Bloomberg, Thomson Reuters, Capital IQ, FactSet etc<br />
3.<span style="white-space: pre;"> </span>XBRL (unadjusted)<br />
4. Verified and Adjusted XBRL (VAXBRL)<br />
<br />
The first choice is only an option if you have ridiculous amounts of time. The second if you have ridiculous amounts of cash and you are willing to take the risk the data is completely error free and has been interpreted correctly. Vendor data by its nature has been handled by a third party so really needs to be verified if it is to form the basis of an expensive transaction. This is why it is never used as the primary source for in-depth company analysis in critical departments such as M&A.<br />
<br />
My previous <a href="https://www.blog.xbrlxl.com/2019/01/does-current-use-of-extensions-make.html">post</a> explains the danger of using neat XBRL. Over half the Dow Jones 30 companies had used extensions in a way that could lead to significant errors in your analysis if you just plugged the raw XBRL into your model. Only 20% of companies had no extensions on the face of their Primary Financial Statements. And if the trend over the last five years is anything to go by (which I also examined in my analysis - I was interested to see what that fifth year of data might look like compared to the first), it not going to get any better.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcF6nAbWTxRve3QIbec7s2bns-uVdyyNQmJbRVj-TylqGhp-s2R3mjxSlkm6hiBcHM0w4C3wg3tiPUcCczmcxBvoWKI5I3U-0P_zd-WXyQh5TXaPoEIe71urlicOsgE1MLNUo40nzICXQ/s1600/ExtDJ30TabSnip.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="155" data-original-width="1177" height="70" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcF6nAbWTxRve3QIbec7s2bns-uVdyyNQmJbRVj-TylqGhp-s2R3mjxSlkm6hiBcHM0w4C3wg3tiPUcCczmcxBvoWKI5I3U-0P_zd-WXyQh5TXaPoEIe71urlicOsgE1MLNUo40nzICXQ/s400/ExtDJ30TabSnip.PNG" width="500" /></a></div>
<br />
There is a viable alternative. And it is not expensive. And by expensive I mean in terms of time, that most precious of commodities. VAXBRL. Just spend a little time verifying and adjusting the XBRL. This way you'll always know the provenance of the source (direct from the company). And with the right tools, it can become an easy and inherent part of your financial modelling process over which you will have complete control.<br />
<br />
What you'll end of with is a set of financials better than any vendors at a fraction of the cost. Now that I think is what the XBRL revolution was supposed to be about.<br />
<br />
Of course I wouldn't be telling you this if I didn't have a set of tools up my sleeve that might be just the job. Take a look at our <a href="https://www.xbrlxl.com/products.vbhtml">totaliZd</a> product. Download a totaliZd <a href="https://www.xbrlxl.com/downloads/XSheetPowerT5FDJ30.xlsx">X Sheet</a> at work (ready for adjustments). Yo can now also see a fully adjusted <a href="https://www.xbrlxl.com/downloads/XSheetPowerTotaliZd3MConglomFixed.xlsx">version</a> of an <b>X Sheet</b> and <a href="https://youtu.be/J-JYvpQM9b8">video</a> which I talk about in my next <a href="https://www.blog.xbrlxl.com/2019/01/fixing-xbrl-to-build-set-of-investment.html">post</a>.<br />
<br />
<br />
<br />Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-29919438581817433192019-01-14T13:53:00.003+00:002019-01-24T11:39:42.111+00:00Does the current use of Extensions make XBRL unusable out of the box?Yes. You can't just plug it in.<br />
<br />
But does that make XBRL useless? No. It just means you'll need to make a few adjustments to the tagging. A small extra step in the process with the right tools. If you don't, if you insert raw XBRL numbers, the ones you can get from the SEC or XBRL.US straight into your models, your analysis will be flawed. I'm not saying it might be flawed - it WILL be flawed.<br />
<br />
I say that because I've looked at the following table. Well actually I compiled it. It shows the incidence of extensions for all 25 of the non financial companies in the Dow Jones Index (DJ30) for the latest published XBRL income and balance sheet and was compiled using our <a href="https://www.xbrlxl.com/products.vbhtml">X Sheet</a>. As long as you only want to compare Apple against Intel you should be fine or the other lone troika of companies that have no extensions on the face of the primary financial statements. Otherwise you've got a problem.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdMTluI5r9WhejKh7v50GdlL0jiiAyeHS2MNKQ34Xi8uJd_jA_Hr-ah8jLkNHyoft418eHmvdzhmnjsQco2XO8pJmwjq515qc_YW8-FG7tzIVGPsb0oe5zXlqhQbDURRUHXBsKthtFE2M/s1600/ExtDJ30Tab.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="721" data-original-width="1149" height="401" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdMTluI5r9WhejKh7v50GdlL0jiiAyeHS2MNKQ34Xi8uJd_jA_Hr-ah8jLkNHyoft418eHmvdzhmnjsQco2XO8pJmwjq515qc_YW8-FG7tzIVGPsb0oe5zXlqhQbDURRUHXBsKthtFE2M/s640/ExtDJ30Tab.PNG" width="520" /></a></div>
<br />
<br />
You can download the full spreadsheet <a href="https://www.xbrlxl.com/downloads/XSheetPowerT5FDJ30.xlsx">here</a>.<br />
<br />
I chose to look at the face of the two principal financial statements for the DJ30 because I wanted to look at the best case scenario. The most comparable values tagged by theoretically the best resourced companies, supported by the top audit firms and therefore capable of delivering pinpoint tagging. The tags I looked at of course represent a fraction of the tagged elements in an annual 10-K, and being the most commonly summarised amounts, the ones in theory least prone to being extended. I excluded the financial companies as they are a bit more peculiar so maybe even more liable to be extended. Like I said I wanted the optimum target result.<br />
<br />
Just in case you are not familiar with extensions, an extended tag is a useless tag. That's being a bit harsh but it does make comparative analysis exceedingly difficult, without intervening to pull it back into the US-GAAP taxonomy (the list of standard tags). The idea is that companies can extend this standard taxonomy by adding their own bunch of tags on top, where items don't fit into the list. This undoubtedly legitimately occurs and this is part of the beauty of XBRL, that it allows for this. It wouldn't be XBRL (eXtensible Business Reporting Language) without it. Unfortunately, in reality, it is up to the company to decide when this is so.<br />
<br />
And so ineffectual is this type of extended tagging, that when Europe joins the XBRL party next year, <a href="https://www.esma.europa.eu/press-news/esma-news/new-rules-make-eu-issuers%E2%80%99-annual-financial-reports-machine-readable">ESMA</a> has specified that this type of disconnected extension will not be allowed. Every value will have a backstop connection to a tag in the IFRS taxonomy and if that does not fully describe the item in question, an extended alias maybe used as its primary tag.<br />
<br />
So having said all that, I'm not sure we should be seeing so many extensions on the face of the financial statements. Surely they should pretty much all look like Apple. Exceptions that prove the rule not make a mockery of it. Anyway I'm not gonna dwell on that. It is what it is. A more pertinent question then is whether these extensions really matter and if so what we can do about the data we have in front of us. This is why the table is not just a simple count of extensions but an examination, albeit cursory, of the potential impact on meaningful company analysis.<br />
<br />
We've had a lot disconcerting <a href="http://xbrl.squarespace.com/journal/2017/4/29/high-quality-examples-of-errors-in-xbrl-based-financial-repo.html">reports</a> on extensions before but I wanted to make this inquiry as real world as possible. So the top level items and my subjective assessment of the likely impact on KPI's or the ability to strip back figures for future forecasting. So some extensions I have deemed as <b>insignificant</b>, some likely to have a <b>minor</b> impact and others, which set off a big red flashing light as, <b>major</b>. So where a total, which can be calculated by combining its disclosed components using established taxonomy data validation rules, has been extended, this is deemed as insignificant but where restructuring charges have disappeared from the standard taxonomy by virtue of an extension, I have considered this a major problem. I have sometimes paid attention to the size of the amount but really it is not necessarily a reliable marker as for example in some years restructuring charges can be massive and others minuscule. <br />
<br />
Anyway you can make your own judgement as I have made the spreadsheet containing the table <a href="https://www.xbrlxl.com/downloads/XSheetPowerT5FDJ30.xlsx">available</a> so you can use the <a href="https://www.xbrlxl.com/products.vbhtml">X Sheet</a> to see all the extensions for yourself by looking at the Filings tabs. Also by looking at the totaliZd tab, you can quickly see the scale of the impact on a comprehensive set of standard values designed for analysis (we call our version the X Financials). The scaling option, a feature of <a href="https://www.xbrlxl.com/products.vbhtml">totaliZd</a>, has deliberately been set to billions so you can more easily see the significance of any impact. A value of one could even be significant within or without its group at this level of magnification.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmdnTTCUVolK-u4I1Cvlf95mAUDw64daNvF94So04WzeX7pUhEasV4_KCuZw7Zifyj4WGcFt6AtvZj_eHkzCOg8JxigygdXB7rxAEraTkIbAXa7q83qR9ittL6ynwmm-SDEB9t5I1lDvI/s1600/ExtTotaliZdMisc.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="132" data-original-width="888" height="57" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmdnTTCUVolK-u4I1Cvlf95mAUDw64daNvF94So04WzeX7pUhEasV4_KCuZw7Zifyj4WGcFt6AtvZj_eHkzCOg8JxigygdXB7rxAEraTkIbAXa7q83qR9ittL6ynwmm-SDEB9t5I1lDvI/s400/ExtTotaliZdMisc.PNG" width="400" /></a></div>
<br />
As the screen shot above shows, any amount in the square green boxes, is a problem that needs fixing. How you do this I cover in the next <a href="https://www.blog.xbrlxl.com/2019/01/fixing-xbrl-to-build-set-of-investment.html">post</a>.<br />
<br />
What you see in totaliZd is as bad* as it can be. That is not a comment on the individual companies involved but the limitations of using neat XBRL. This is the point of <a href="https://www.xbrlxl.com/products.vbhtml">totaliZd</a>, to provide a starting point, from which you can quickly fix this apparent mess and leverage XBRL to perform the kind of analysis never previously possible before. I'm not dissing XBRL. I'm simply qualifying how you use it.<br />
<br />
*Things get off to a very bad start for Exxon and Coca Cola as neither them supply a Revenues tag. This isn't even an extension problem. This is just a "not using enough us-gaap tags" problem. Problems like this automatically get fixed in the X Sheet when using our <a href="https://www.xbrlxl.com/buy.vbhtml">standiZd</a> option.<br />
<br />
<i>This post could really be considered part of a series I started in 2017 - <a href="https://www.blog.xbrlxl.com/2017/01/and-so-has-dream-come-true.html">And so has the dream come true?</a> to look at the state of XBRL disclosures and examine the continuing validity of the objections raised in using it.</i><br />
<br />
<br />Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-17783950445017595992019-01-10T13:31:00.001+00:002019-01-23T17:42:47.230+00:00Which source of Company Financial Data should I use for my model?If we want to create a model of a company's future performance, we need a starting point, a set of inputs which we can examine, adjust and extrapolate our best estimate of how we anticipate the company will perform in the future.<br />
<br />
<h4>
So what are our choices?</h4>
<div>
<br /></div>
1.<span style="white-space: pre;"> </span>Lifting values straight from the Financial Reports filed by a company<br />
2.<span style="white-space: pre;"> </span>Data Vendor - Bloomberg, Thomson Reuters, Capital IQ, FactSet etc<br />
3.<span style="white-space: pre;"> </span>XBRL<br />
<br />
I believe XBRL provides the best possible place to start. Why?<br />
<br />
Well, as my old Physics teacher was never short of telling us, lets go back to first principles.<br />
<br />
Our ideal starting point would be a perfectly accurate picture of how a company is performing right now. We can’t get this however for two reasons:<br />
<br />
1.<span style="white-space: pre;"> </span>We don’t have real time access to companies accounting systems so we are constrained behind the curve by the reporting calendar.<br />
2.<span style="white-space: pre;"> </span>We can only see the published external numbers, the numbers that the company allows us to see, subject of course to any legal disclosure requirements or the opinion of their auditors.<br />
<br />
So even this, our best source, is inherently flawed, so we must be ready at all times to make adjustments/correct the figures put in front of us. Despite these caveats, the company still has to be our first port of call because only they have the closest and best view of the current operating performance.<br />
<br />
<h4>
So why would we use a data vendor?</h4>
<div>
<br /></div>
Well their starting point is exactly the same but what they do is prepare the accounts for financial analysis. If you start from the Financial Reports filed by a company, preparing every single company this way is expensive, which is why they will charge you thousands of dollars for the privilege. Criticisms levelled at data vendors in the past have been that they don’t always get the figures right and that its not always clear how and what adjustments have been made.<br />
<br />
Also a standardised approach can be problematic as the Corporate Finance Institute note:<br />
<br />
“Companies such as Bloomberg, Capital IQ, and Thompson Reuters provide powerful databases of financial data. However, financial statements retrieved from these databases tend to be in a standardized format. Thus, if the company uses an accounting value unique to its business operations, you will not grasp it from data retrieved and it will affect your analysis.”<br />
<br />
This is why in critical decision making, when an investment decision or M&A deal could be worth millions of dollars, vendor data would never be used as a starting point for a single entity centric model. Of course this data has value in screening and modelling whole markets or sectors but even here, for the reasons stated, they are potentially flawed inputs.<br />
<br />
<h4>
So what does XBRL bring to the party?</h4>
<div>
<br /></div>
XBRL takes the effort out of lifting the financial values from the report and provides a first pass at standardization. Not normalization mind, but a first step in that process if your goal is vertical analysis against a company’s peers. As I discuss in this <a href="https://www.blog.xbrlxl.com/2019/01/does-current-use-of-extensions-make.html">article</a>, unfettered XBRL, as filed with the SEC and made available through Edgar or alternatively for a fee via the XBRL.US API, does not, nor indeed intend to, provide a perfect set of standardized values.<br />
<br />
By harnessing the XBRL tagging, your models can be automatically derived from genuinely as reported values, the closest view of past operating performance direct from the company and untampered by any third parties. The hard graft of lifting values, monotonous, error prone and time consuming, is removed. But as I underlined above, this is not quite the finishing point. Most of the hard work is done but we must always be ready and prepared to make a few adjustments*. They will undoubtedly be required.<br />
<br />
<i>*How you can easily make these adjustments is discussed <a href="https://www.blog.xbrlxl.com/2019/01/fixing-xbrl-to-build-set-of-investment.html">here</a> and in the following <a href="https://youtu.be/J-JYvpQM9b8">video</a>. If you want to read more about the need for adjustments in XBRL, then check out the next <a href="https://www.blog.xbrlxl.com/2019/01/does-current-use-of-extensions-make.html">post</a> in this series.</i><br />
<br />
<i>You can also read about <b>totaliZd</b> from Fundamental X <a href="https://www.xbrlxl.com/products.vbhtml">here</a>, our complete solution for preparing XBRL derived inputs to financial models in Excel.</i><br />
<div>
<br /></div>
Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-38233240054920484562019-01-04T13:17:00.000+00:002019-01-04T13:26:06.041+00:00...taking control of an Excel Power Web Query is another thing entirelyThis post is the follow up to <a href="https://www.blog.xbrlxl.com/2018/12/getting-xbrl-in-excel-is-dead-easy-with.html">Getting XBRL in Excel is dead easy with Power Query</a>.<br />
<br />
Now to gain control of a "legacy" web query, to dynamically add your own variable parameters, say input a ticker into an X Sheet, you had to feed the query into Excel via an iqy file (internet query - this tech was actually introduced by Microsoft way back in the last millennium).<br />
<br />
You no longer have to mess around with peculiar files in Get & Transform but in a lot of respects its way more complicated. Because Power Query is about Tables, and its about Tables; did I mention its about Tables? Power Query thinks in Tables, all it outputs is Tables* and what you input better refer to a Table somewhere along the line or there will be no output.<br />
<br />
<i>*Well strictly speaking you can specify that it outputs part of a table so you can squeeze a solitary value out of it.</i><br />
<br />
So you need to know about Excel Tables and you probably need to know a little bit of Power Query M (The query language that powers Get & Transform). There is some good news, you don't need to learn all of this if you don't want to, as we've done it for you, and as always, in the name of transparency, we will try to explain along the way what we've done and how its doing it. And maybe throw in the odd video as well.<br />
<br />
Moving to Tables is mostly a good thing. It encourages good practice in the way you treat and organize your data but it does mean you are entering the realms and rigidity of database structures (sort of Microsoft Access by the back door!). But it does mean (at least while you've got your head stuck in a query) you have to leave behind some of the lovely freedoms of Excel. The quaint idea that a cell could be any old data type depending on what turned up doesn't wash in a Table.<br />
<br />
There are three ways of connecting a dynamic variable, each slightly more complicated and they are all demonstrated in this example Excel <a href="https://xbrlxl.com/downloads/XBRLinPowerQuery.xlsx">Workbook</a>:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinjr9PTbqYD6lbt-1vEy9FEe-xIxZXVXdofRSVfa63ok7wwKUj_n6VTziZleAURRapH6lUxvsUCkzoDdiuYKllDCIdmRhmR841A4Y3ioBMrIqM2kMlnHaYuahowPkwJgvZdoHhFxl4gWA/s1600/DynamicTableVar.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="96" data-original-width="193" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinjr9PTbqYD6lbt-1vEy9FEe-xIxZXVXdofRSVfa63ok7wwKUj_n6VTziZleAURRapH6lUxvsUCkzoDdiuYKllDCIdmRhmR841A4Y3ioBMrIqM2kMlnHaYuahowPkwJgvZdoHhFxl4gWA/s1600/DynamicTableVar.PNG" /></a></div>
<br />
1. Put your dynamic variable in a Table. A Table! Now with a Table, Power Query can potentially see this variable and so we can edit our original query to reference it.<br />
<br />
2. Create a new query to reference our new Table and and edit our original query to reference this new query. Cleaner solution and if we need to transform our variable, we have the space to do so in this new query. Also we now have a dynamic variable query that we can re-use.<br />
<br />
3. Even more editing to turn our second query into a Function. This is what we do in the X Sheet.<br />
<br />
Whichever option you choose, you are going to come face to face with the Power Query M Language. With the third option, you will also need to enter the Advanced Editor.<br />
<br />
Now I'm not gonna run through each stage of the process of creating the above three solutions because I'll be here all day but we have videos to show you exactly how to achieve each result. I will though highlight here the key components in each approach and any other useful observations.<br />
<br />
1. Simple Table reference. Watch the <a href="https://youtu.be/_My5Lt-GN4Y">video</a>.<br />
<br />
Once you have created your new table, you need to replace the static variable in the original web request with this:<br />
<br />
<span style="color: #93c47d;">Excel.CurrentWorkbook(){[Name="TableName"]}[Content]{0}[ColumnName]</span><br />
<br />
So the key line in your original web query will look something like this depending on what you named your Table and its solitary Column:<br />
<br />
<span style="color: #93c47d;">Source = Web.Page(Web.Contents("http://xbrlsheet.xbrlxl.com/?tkn=0&eid=" & Excel.CurrentWorkbook(){[Name="Company"]}[Content]{0}[Ticker])),</span><br />
<div>
<br /></div>
<div>
NOTE: Your variable doesn't have to be in an Table. You can just use a Named Range. It works because a Named Range is in fact a pseudo table. What you have to remember is that a Named Range has no column name so it enforces a default column name on it. So your ColumnName in this circumstance is Column1.</div>
<div>
<br /></div>
<div>
2. New query to reference our new variable Table. Best to watch the <a href="https://youtu.be/Xdi5iX4HvdI">video</a>.<br />
<br />
You can easily make a query out of an existing Table in a sheet via Get & Transform. Tables are very keen to define each Column in a Table as a specific type. We need to change the Changed Type Applied Step to:<br />
<br />
<span style="color: #93c47d;">Table.TransformColumnTypes(Source,{{"Token", type text}}){0}[Token]</span><br />
<br />
This also insures that we return a value rather than the whole table by adding the "{0}[Token]" 1st cell reference to the end.<br />
<br />
The main query can now reference this new query which we called Authenticate in the video:<br />
<br />
<span style="color: #93c47d;">Source = Web.Page(Web.Contents("http://xbrlsheet.xbrlxl.com/?tkn=" & <span style="background-color: #990000;">Authenticate</span> & "&eid=" & Excel.CurrentWorkbook(){[Name="Company"]}[Content]{0}[Ticker]</span><br />
<br />
In the video we actually managed to make all the changes without having to tussle with the Power M Query Language in the Advanced Editor.<br />
<br />
Best to save/convert the query to a Connection Only query. Otherwise you'll end up with a new unwanted Table in a new sheet every time you exit the Query Editor. You need to find the "Load To..." option to do this.<br />
<br />
WARNING: If you are on the default (strictest) Privacy setting, the main query won't run and you will get a message something like this:<br />
<br />
<span style="background-color: yellow;"><span style="color: #444444;">Formula.Firewall: Information about a data source is required.</span></span><br />
<br />
or:<br />
<br />
<span style="background-color: yellow;"><span style="color: #444444;">...references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.</span></span><br />
<br />
Excel, rather pathetically, can't distinguish between a local (our new variable Table) and an outside source as this juncture. By converting this query to a Function, as we do as the next option, resolves this piece of stupidity. We can of course also set our Privacy level either at the Workbook level or in the Query Settings in the Query Editor (File>Options and Settings>Query Options>Privacy) to "We don't care" (Always Ignore Privacy Level Settings).</div>
<div>
<br /></div>
<div>
3. Turn our new query into a Function. And check out this <a href="https://youtu.be/qjG_V2sBQNs">video</a> if you want to beef up the power!<br />
<br />
This is our favorite option, the power Power Query option but you do have to dip your toe into the Advanced Query editor.<br />
<br />
It saves the query from trying to create a new table in Excel every time unless you make it Connection Only and circumvents the mindless security objections as described above. You can also, rather usefully, opt to pass a variable to a Function as we do in the X Sheet. Its like proper programming! These advantages are spelled out in more detail in a post on my other blog - <a href="https://jotaboutexcel.blogspot.com/2018/12/three-reasons-why-you-should-use.html">Jot About Excel</a>. And again of course the Function you build is re-usable in other queries.<br />
<br />
And in reality the changes are fairly simple. Top and tail your query with these two lines:</div>
<br />
<span style="color: #93c47d;">let Years = (YearNo as number) =></span><br />
<div>
<br /></div>
<div>
<span style="color: #93c47d;">in Years</span></div>
<div>
<br /></div>
<div>
where Years is what we named our new Function and YearNo is the variable we pass it (just use empty brackets if no variable is required). As you can see, if you are familiar with Lambda expressions, its uses a similar syntax.</div>
<br />Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-37675339656172861662018-12-07T17:43:00.000+00:002019-01-04T13:29:23.725+00:00Getting XBRL in Excel is dead easy with Power Query...Constructing a web query in Excel using Microsoft's new wonder query engine to access <b>XBRL</b> is really simple.<br />
<br />
Starting with Excel 2010, Microsoft started peddling a new analytical add-in designed to bring data of any shape, size and colour into Excel in one big mashup. An analysts dream.<br />
<br />
In Excel 2016 and Office 365, it became part of the fabric and got a new name - Get & Transform; Microsoft's chosen way to source data from within and without Excel. So much so that the way we used to, well still do, run web queries at <a href="https://xbrlxl.com/" target="_blank">XBRL XL</a> to pull in <b>XBRL</b> is now described as a "legacy data import".<br />
<br />
The web query is dead. Long live the web query! Yep web queries are still a vital part of the mashup and are immediately accessible from the Get & Transform area of the Data Ribbon.<br />
<br />
I have to say, having spent the last 6 months, pulling it and tangling with it, its still a bit ropy round the edges. There are bugs and you can do the same things in confusingly different places. It doesn't all quite fit together but it will do and when it does its going to be epic.<br />
<br />
So we've started to play with it. And you can see the results in this <a href="https://youtu.be/Zug4TN2H_jY">video</a>.<br />
<br />
We designed our <b>XBRL</b> data to be accessible by the most universal route possible. So you could query our data via the most basic of web protocols to download simple tables.<br />
<br />
So a web request to access XBRL from the latest 10-K from Microsoft looks like this:<br />
<br />
<a href="http://xbrlsheet.xbrlxl.com/?tkn=0&eid=msft">http://xbrlsheet.xbrlxl.com?tkn=0&eid=msft</a> (Get a free, more useful token at our website)<br />
<br />
And a query to give you precise control of what filing you get back:<br />
<br />
http://xbrlsheet.xbrlxl.com?tkn=12345&eid=msft&dat=20170630&fil=1&typ=fsn<br />
<br />
fil=1 brings back a 10-Q, fil=4, 10-K. typ=fsn brings back the financial statements & notes.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgImCLtrwQMFwBS4t1g1P8BXdzqnjfKu0CTzM80YR9iyl1-uVD7JeS2w7NEdt_QmBoVIox60Sr_axK9ND2dI8wxbdNEMBI6kYbvRRw03G_OTU11x8waMwdqQHWAqhbn326zl_ifDA2e1To/s1600/SimplePowerQ1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="338" data-original-width="939" height="143" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgImCLtrwQMFwBS4t1g1P8BXdzqnjfKu0CTzM80YR9iyl1-uVD7JeS2w7NEdt_QmBoVIox60Sr_axK9ND2dI8wxbdNEMBI6kYbvRRw03G_OTU11x8waMwdqQHWAqhbn326zl_ifDA2e1To/s400/SimplePowerQ1.PNG" width="400" /></a></div>
<br />
So if you choose Get Data/From Web from the Data Ribbon and insert one of the above web requests (with a token that works rather than the placeholders above - tkn=0 works but its a groundhog - same filing keeps coming back), it will go away and bring back four tables. If you select Table 3 and press load you will instantly have a spreadsheet full of <b>XBRL financials</b> just as the company reported them.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5qpBu7pG9_Lqp6p_NcOp7pocr8qUiOwefqkgy5ZJyxSWMfNwBDIicIuQVnF0EK1RAE5sYDIAAnmEnSrdqK_qt19liOpo6-vzQKZcSNc7X5BwXK1CRzA2v9ZhflLIaqrI7SJht5nxVw80/s1600/SimplePowerQ2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="263" data-original-width="983" height="106" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5qpBu7pG9_Lqp6p_NcOp7pocr8qUiOwefqkgy5ZJyxSWMfNwBDIicIuQVnF0EK1RAE5sYDIAAnmEnSrdqK_qt19liOpo6-vzQKZcSNc7X5BwXK1CRzA2v9ZhflLIaqrI7SJht5nxVw80/s400/SimplePowerQ2.PNG" width="400" /></a></div>
<br />
As it comes with the tags, you can do lookups to pick up precisely whatever values you want to include in your models. You can see these at work here in our Power Query version of the <a href="https://xbrlxl.com/downloads/XSheetPowerX1F.xlsx">X Sheet</a>.<br />
<br />
Of course to get a different filing all you have to do is use a different company ticker. Except how do you do that in the Power Query we've just built? Well you could run through the whole rigmarole again and build another Power Query, putting a different ticker in the web request, and as we saw that's relatively painless. But there's got to be a better way, right? A way to take control of the query? Well there is but it is less than straight forward and you'll need a whole new tool set.<br />
<br />
In the meantime, you can use one we prepared earlier, <a href="https://xbrlxl.com/downloads/XSheetPowerX1F.xlsx">The X Sheet</a> . When we come back, we will show you how you do it. Till then we suggest you brush up on your knowledge of <a href="https://exceljet.net/things-to-know-about-excel-tables">Excel Tables</a>!<br />
<br />
So now you can see exactly how to take control of an XBRL Power Query in the next <a href="https://www.blog.xbrlxl.com/2019/01/taking-control-of-excel-power-web-query.html">post</a>...<br />
<br />
<br />
<br />Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com3tag:blogger.com,1999:blog-7519651242666505822.post-30283790370636823362017-05-05T13:29:00.000+01:002017-05-05T13:50:54.933+01:00Will there ever be less XBRL data items?If you read my previous <a href="http://www.blog.xbrlxl.com/2017/05/what-do-filers-do-with-all-xbrl-data.html">post</a>, you will know that the arrival of the SEC US-GAAP Taxonomy unleashed a veritable tsunami of data items on a concerned body of filers and a bewildered bevy of investors.<br />
<br />
So with so many data items milling around, you would have thought that someone or something might be sent out to clean it up. Well the good news is that in late 2014, the FASB launched the <a href="http://www.fasb.org/jsp/FASB/Page/SectionPage&cid=1176164001455">Xbrl Taxonomy Simplification Initiative</a>.<br />
<br />
And now the not so good news...<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJcNInC_dp8WGuUbJQdsH2lo9r7M7uA67LPE0mUs5_dhY3K9TqDIzeCaG-fUd7YOTfcqBtmL_e8uxolVMFK0jm3RMAEAk4qa9a5x9cVZ5O6EQv-ofkb8Lmq1m1KYwzIrtZanBFhAvWjWQ/s1600/Tax2017Changes.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="171" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJcNInC_dp8WGuUbJQdsH2lo9r7M7uA67LPE0mUs5_dhY3K9TqDIzeCaG-fUd7YOTfcqBtmL_e8uxolVMFK0jm3RMAEAk4qa9a5x9cVZ5O6EQv-ofkb8Lmq1m1KYwzIrtZanBFhAvWjWQ/s320/Tax2017Changes.PNG" width="320" /></a></div><div style="text-align: center;">Source: FASB - <a href="http://www.fasb.org/cs/ContentServer?c=Document_C&pagename=FASB%2FDocument_C%2FDocumentPage&cid=1176168846821">2017 Taxonomy Release Notes</a></div><br />
Looks good until you realise that this represents the rate of change. In other words you don't really want the blue line exceeding the red line. But you can actually see the impact of the 2014 initiative as the red line finally jumps above the blue one through 2015/2016 reaching a peak of 681 deprecated items in 2016. I have to say I still find it staggering that there are so many items to choose from given how few items companies actually disclose in their 10-K's and Q's.<br />
<br />
So is there any way out of this forest? Well all is not entirely lost because in March 2017 the SEC finally agreed to allow foreign filers to use the IFRS taxonomomy. This is good on two counts. One it means that all foreign filers will for the first time be required to file XBRL which starts to answer the <a style="color: #93c47d; "href="http://www.blog.xbrlxl.com/2017/01/and-so-has-dream-come-true.html">is it only for US Companies</a> question (more on this later!) and two it establishes a precedent that a taxonomy does not have to have 16,000 data items.<br />
<br />
<a href="http://www.ifrs.org/XBRL/IFRS-Taxonomy/Pages/IFRS-Taxonomy.aspx">The IFRS Taxonomy</a> which is designed to satisfy the filing requirements of every country around the world, not just one large country, has only 4,610 items. It did have a lot less (47% less to be precise back in 2011 when it was originally scheduled to be mandated) but I think they were told to bulk it up a bit if they wanted to get SEC approval (sound of head banging on table). A group was tasked with adding items by custom or practice (and approx. 1,000 Common Practice items have been added since then). During this process no specific consideration is given to how useful an item's structural relationship might be - this is sadly not the philosophy behind either the IFRS or US taxonomy. Well at least 4,500 is a lot better than 16,000. <br />
<br />
IFRS based XBRL is likely to be adopted for <a href="https://www.esma.europa.eu/press-news/esma-news/esma-proposes-new-digital-format-issuers%E2%80%99-financial-reporting">mandatory filing in Europe</a> in 2020 and one would imagine the rest of the world will fall into line after that (completely answering the <a style="color: #93c47d; "href="http://www.blog.xbrlxl.com/2017/01/and-so-has-dream-come-true.html">US Companies only</a> question). Most of the world now uses International Financial Reporting Standards or has standards based on them.<br />
<br />
I should point out that I am a member of the <a href="http://www.ifrs.org/About-us/IASB/Advisory-bodies/Working-groups/ITCG/Pages/IFRS-Taxonomy-Consultative-Group-ITCG.aspx">IFRS Taxonomy Consulative Group</a> (ITCG). But this is not why I'm a relative fan of the IFRS taxonomy. It's just got less items in.Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-33957198410640003762017-05-02T13:37:00.000+01:002017-05-05T13:37:03.013+01:00What do filers do with all the XBRL data items at their disposal?This is the latest in a series of ponderings on whether the <a href="http://www.blog.xbrlxl.com/2017/01/and-so-has-dream-come-true.html">XBRL dream has come true</a>?<br />
<br />
In our <a href="http://www.xbrlxl.com">XBRL to XL</a> database, we have 20,000 data items that originate from the US-GAAP Taxonomies. That's a heck of alot of data items. Admittedly that includes alot of bumf that is just there for presentation or connecting stuff (abstract items) or axis (column headings) and items that have subsequently been deprecated. But compare that to S&P Compustat's 500 odd or the 1600 data items we use for our <a href="http://www.xbrlxl.com/asp/faq.asp#data">standardisation</a> routines.<br />
<br />
I guess the FASB were worried about companies using extension tags, although perhaps there are more direct routes of limiting i.e. prohibiting extensions.<br />
<br />
Of course the more there are, the greater the risk of mistakes. Choosing the wrong item over another in the same section is not easy to detect. Usual validation routines won't pick it up. And none of this stuff is audited. Ultimately it is up to the company, or whoever they've outsourced their XBRL tagging to, to pick it up.<br />
<br />
The kinda advice stressed by Ernst & Young on the release of new taxonomies is typical and not surprising.<br />
<br />
"As they move to a new taxonomy, companies should review their practices for selecting XBRL tags and thoroughly search the tags in the new taxonomy".<br />
<br />
So what value do we as users get out of all this wonderful detail?<br />
<br />
Well lets have a look at disclosure levels for our probably over the top standard data set of 1632 items (lets refer to this set as the <a href="http://www.xbrlxl.com/asp/faq.asp#stdizd">standidZd</a> 1600. So a couple of weeks ago I did a chop of our SEC XBRL to XL database (powers <a href="http://www.xbrlxl.com/asp/xbrlsheetinfo.asp">XBRL sheet</a>) to check on item usage. I thought it would be interesting to look at usage for our reference set of industrial companies we established when looking at <a href="http://www.blog.xbrlxl.com/2017/02/has-dream-come-true-is-there-enough-xbrl.html">filing histories</a> (seeing our standiZd 1600 was designed explicitly for non financial companies). Keeping everything consistent with all our other data sampling in this series, I only included 10-K's but all the 10-K's they've ever filed in XBRL. I should stress that this is before we've applied our <a href="http://www.xbrlxl.com/asp/faq.asp#stdizd">standardisation</a> routines.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvvS1XEztgMiLr93RbeHmS1yOA2O735O9OeeFhjhCOJGRaGEVbaDJZcMm5EA00Z2UmD0rHLp4cbCkj3EIE-gWvD8PFz5vL5qMUEtawLZ1SAQfZ29Hi9HRnA0Tjq0_Nb7pPgaEfq1EWDiY/s1600/TaxItemUsageTab1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvvS1XEztgMiLr93RbeHmS1yOA2O735O9OeeFhjhCOJGRaGEVbaDJZcMm5EA00Z2UmD0rHLp4cbCkj3EIE-gWvD8PFz5vL5qMUEtawLZ1SAQfZ29Hi9HRnA0Tjq0_Nb7pPgaEfq1EWDiY/s200/TaxItemUsageTab1.PNG" width="270" height="130" /></a></div><br />
So what is this table telling us? That 263 of our standiZd 1600 have NEVER been used by any of the top US industrial companies. And a whopping 65% of what we regard as the most comparable individual data items have been used by 5% or less. So if we were trying to compare any one of these 1000 odd items, we would have at best only 11 other companies (and roughly on average probably only six) to compare them with. Lets hope a couple of them happen to be in the same sector! And we can probably forget about creating any meaningful aggregates for these items.<br />
<br />
What happens if we widen it right out to the total population of US-GAAP tags and the total population of companies. Not surprisingly it gets uglier. 10,000 of US-GAAP tags appear in 5% or less of the total number of 10-K filings made to date (approx. 50,000). And roughly only 1,000 items appear in more than 5% of these filings. <br />
<br />
This isn't a perfect piece of analysis as some items have come and some items have gone (but that has been happening by and large around the margins of the taxonomy)<br />
<br />
So it's probably not too unreasonable to conclude 20,000 data items is drastic overkill for comparative analysis and that our 1600 standiZd data items IS over the top! We will certainly take a close look at those 263 items to see if we need to rename! this set downwards.<br />
<br />
So what use does this disparate tagging have? Well it does allow us to easily find the rare occasions where particular tags have been used but because use is so rare, it probably has no real advantage over them just being tagged using extensions. A simple text search, although not as neat would be just as effective, if not more so as it would pick up all (the right or wrong) occasions when an extension has been used instead.<br />
<br />
All this prompts the question - <a href="http://www.blog.xbrlxl.com/2017/05/will-there-ever-be-less-xbrl-data-items.html">will there ever be less data items?</a><br />
<br />
Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-8671225667888622832017-04-25T17:24:00.001+01:002017-04-25T17:24:31.476+01:00Building Xbrl sheets from scratch (using our query engine)Who are we to tell you how to analyse a company? That's why our Xbrl example spreadsheets are merely that - examples. The tech behind it is simple, been around for years (ie. works!) and is easy to deploy. The examples provide a starting point and hint at what's possible. You build on them to create a custom sheet that meets your specific analytical needs or you can start from scratch.<br />
<br />
So how do I build a sheet from scratch? Well watch this short <a href="https://youtu.be/aKdvC7doJrk">video</a>.<br />
<br />
And these <a href="https://www.youtube.com/user/FundXJim">videos</a> will also give you a flavour of how to customise existing queries as they demonstrate the example sheets in use.<br />
<br />
You can add bells and whistles like set them to automatically update when you open a sheet or provide extra layers of automation by incorporating them in a VBA macro to fully harness the power of Excel. <br />
<br />
Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-47633703026531667652017-03-03T13:38:00.000+00:002017-03-03T19:56:43.445+00:00How to screen for the very latest filings in ExcelNow EDGAR is very good at showing you all the latest filings. But I wanted to take it a step further in my analysis of XBRL filings for the 2017 10-K reporting season. I also wanted to do it in Excel. By the way, this is all part of the <a href="http://www.blog.xbrlxl.com/2017/01/and-so-has-dream-come-true.html">Has the dream come true?</a> series of posts and videos which you can follow starting from <a href="http://www.blog.xbrlxl.com/2017/02/has-dream-come-true-show-me-how-i-can.html">here</a>.<br />
<br />
If you have read my previous <a href="http://www.blog.xbrlxl.com/2017/02/has-dream-come-true-show-me-how-i-can.html">posts</a>, you will know I have set up a control group of companies so we can see for real in this reporting season what we can and can't do with XBRL. Now I've needed to be able to monitor those filers, just as if they were my portfolio of potential investments. So we've added a watchlist feature to <a href="http://www.xbrlxl.com/asp/xbrlsheetinfo.asp">Xbrl sheet</a>. Technologically, it's just a variation on the existing queries you can run in Xbrl sheet. It uses the same query file but you set different parameters to do some very powerful screening of the latest flings at the SEC.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4yAGWzOybVHm7TAQKbsVF010g8w6OqF_JlUZHREOg87d1Z27bx_0GiGvffuGAxcQHxEbIsugBTgu1BoqIJiDwnAu6SlEYDnXViOtE68Ot-Azu3gHwiXRfvQj0vgKF7w5aEe1az0J0Yrs/s1600/WatchList2017.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4yAGWzOybVHm7TAQKbsVF010g8w6OqF_JlUZHREOg87d1Z27bx_0GiGvffuGAxcQHxEbIsugBTgu1BoqIJiDwnAu6SlEYDnXViOtE68Ot-Azu3gHwiXRfvQj0vgKF7w5aEe1az0J0Yrs/s320/WatchList2017.PNG" width="278" height="320" /></a></div><br />
Great thing about Excel data queries is that you can set them up to run whenever you want automatically, so when you open your workbook or indeed at regular intervals, say every hour.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnVMejjZqvNW02kglCfJtN0WVL_iX3dGoE-F2P9dAF9Wr9RjN-uiaOZahQ-lGVCg7aE3DVbyzXiS2XwUQ2YyaGSMjNG61KA0KwDJnvyItmc1lpAWZWe5vuXhFzZAjFJLEIJcTu4cObhdo/s1600/QueryRefresh2017.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnVMejjZqvNW02kglCfJtN0WVL_iX3dGoE-F2P9dAF9Wr9RjN-uiaOZahQ-lGVCg7aE3DVbyzXiS2XwUQ2YyaGSMjNG61KA0KwDJnvyItmc1lpAWZWe5vuXhFzZAjFJLEIJcTu4cObhdo/s320/QueryRefresh2017.PNG" width="261" height="320" /></a></div><br />
You won't always know which companies your wish to watch so we've covered that by adding the ability to screen by filing date. Say for example, show my every company that filed yesterday (whisper it but you can actually search down to the latest minute so you could set it to show you all those that filed in the last hour for example. We update our database from the SEC in real time so as soon as it's available on EDGAR, it's available in <a href="http://www.xbrlxl.com/asp/xbrlsheetinfo.asp">Xbrl sheet</a>). Not only that, you can filter by industry to build a peer group of real time filings. You can specify a particular SIC code or a wider range to pick a bigger industry grouping or search by filer (i.e. find filers in the same industry as your chosen target).<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZTVVhSqJL4jEhX-ku_ezEtEwVhopP9qjJCer3rDbPVkNFMKZVlBPdBJWAlblEvcyCI6qtVQatd2Z4l0QLGiACf-u4ZASC0zbSoBT5e0CyMnSFJY8lgDOhyphenhyphenXEYxYcOPcIAoL-VYjqZbZA/s1600/SICCodes2017.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZTVVhSqJL4jEhX-ku_ezEtEwVhopP9qjJCer3rDbPVkNFMKZVlBPdBJWAlblEvcyCI6qtVQatd2Z4l0QLGiACf-u4ZASC0zbSoBT5e0CyMnSFJY8lgDOhyphenhyphenXEYxYcOPcIAoL-VYjqZbZA/s320/SICCodes2017.PNG" width="320" height="103" /></a></div><br />
Anyway this <a href="https://youtu.be/zAI_7mg2eaY">video</a> shows you how to do all that. And once you've identified a filing, you <a href="http://www.blog.xbrlxl.com/2013/07/xbrl-sheet-direct-access-to-xbrl-from.html">bring down all it's XBRL tagged data</a> using the same mechanism in <a href="http://www.xbrlxl.com/asp/xbrlsheetinfo.asp">Xbrl sheet</a>.<br />
<br />
And there's an example sheet to play with <a href="http://www.xbrlxl.com/egs/LatestFillingsEg4.xlsx">here</a>.<br />
<br />
<br />
Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com2tag:blogger.com,1999:blog-7519651242666505822.post-88705517285044996812017-02-10T14:23:00.000+00:002017-02-10T17:25:03.078+00:00Has the dream come true? - show me how I can see 10 years of XBRL dataThis is the latest installment in what seems to be turning out to be a series - catch the start here - <a href="http://www.blog.xbrlxl.com/2017/01/and-so-has-dream-come-true.html">has the dream come true?</a><br />
<br />
After my last post, I thought I really ought to show you all this data. Provide the evidence as it were. We've just added a few new bits and pieces to Xbrl Sheet so I also I thought it would be a good way to bring you upto date with what we've being doing over at XBRL to XL.<br />
<br />
So it looks like this.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFpN7cW0KZcYUWBwoW38BLybQdSLAIrXMg-B2qjP9PZZO5au7oM0V0E0tD5bT-WkmdTq7L59m0eEuqOGh2QS1YSO-Hul7wu4566dUUIetgsa5OI_AHhIBdFiJE1LPF3XUlmn0V1ZGb66Q/s1600/10yrsNFLX.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="80" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFpN7cW0KZcYUWBwoW38BLybQdSLAIrXMg-B2qjP9PZZO5au7oM0V0E0tD5bT-WkmdTq7L59m0eEuqOGh2QS1YSO-Hul7wu4566dUUIetgsa5OI_AHhIBdFiJE1LPF3XUlmn0V1ZGb66Q/s400/10yrsNFLX.PNG" width="500" /></a></div><br />
And you can see how I created this in next to no time (and how you can do the same) <a href="https://youtu.be/7rR7rXR2I5Y">here.</a><br />
<br />
And if you just can't wait and want to get hold of the data in a hurry for Neflix (or any company), you can download the <a href="http://www.xbrlxl.com/asp/xbrlsheetinfo.asp">Xbrl Sheet</a> from <a href="http://www.xbrlxl.com/egs/10yrEg1.xlsx">here.</a> Just pick up a token from the <a href="http://www.xbrlxl.com/xbrlxl.asp">website</a> and fire away.<br />
<br />
<br />
Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-34482996703315621672017-02-03T15:03:00.000+00:002017-02-03T17:48:05.862+00:00Has the dream come true - Is there enough XBRL?If you missed the introduction, then you can go back to this <a href="http://www.blog.xbrlxl.com/2017/01/and-so-has-dream-come-true.html">post.</a><br />
<br />
I think I'm gonna end up tackling the "enough" question alot. Today as we stand at the beginning of the 2017 10-K reporting season, lets for now just narrow it down to the question of history.<br />
<br />
Throughout this examination of the validity of XBRL today, I want to keep it as real world as possible, so a lot of my work in the coming weeks is gonna be working with a subset of companies from that most popular of general US indices - the S&P500. So I'm gonna draw from approx. 250* constituents to see if we can get closer to an answer.<br />
<br />
*No I haven't just halved the index! I didn't want to work with financial companies as that just makes my life harder and I wanted to just look at companies that were going to lodge 10-K's in the current reporting window. Conveniently that left me with 256.<br />
<br />
So before any reporting for 2017 began, we had (Source: XBRL to XL Database):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1WJEd00dsIGWlkASs8sWGGwSH_6S2ZEWhZPN1XroXCrrMMSx60bKkuM2H4vJDjXf83PctqFK4k8YXuZL82kw1mt7xreytYoGWD6DyyGa__aqocu0I6xiKydwJC-jXYdEQu4PcKWN8T1g/s1600/2017RepTots1.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="79" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1WJEd00dsIGWlkASs8sWGGwSH_6S2ZEWhZPN1XroXCrrMMSx60bKkuM2H4vJDjXf83PctqFK4k8YXuZL82kw1mt7xreytYoGWD6DyyGa__aqocu0I6xiKydwJC-jXYdEQu4PcKWN8T1g/s320/2017RepTots1.PNG" width="320" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Which means not surprisingly, given how long XBRL has been a requirement, pretty much the entire population already has a five year history (not 100% as not all constituents were listed when XBRL filing started). That's a good start but even more excitingly, <span style="color: #6fa8dc;">nearly 60% of this index population is suddenly gonna have 10 years of data!</span> As the majority of their data points will now stretch back 10 years for the first time by the end of this reporting cycle. Companies are required to disclose 3 years of income and cash flow data (in each filing) so 8 filings will take you back 10 years for period items (9 years for balance sheet items).<br />
<br />
In fact 4 companies (chevron corp, fluor corp, newmont mining corp, united technologies corp) had already filed 10'K's 8 times so this year will have a complete 10 year history.<br />
<br />
10 years has always been regarded by analysts as the kinda historical period you can properly get your teeth into. The theory being that it equates roughly with an entire macro-economic cycle - from boom to bust.<br />
<br />
<br />
<br />
<br />
<br />
<br />Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-60449020180622124462017-01-31T13:37:00.000+00:002019-01-11T18:05:21.739+00:00And so has the dream come true?Seeing I thought I had some stuff to say and I hadn't bloggged for far too long, I thought I'd look back at my early posts. When I started back in 2011, 2,000 companies had made their first fledgling filings. Now 11,000 plus companies have made over 170,000 filings (Source: XBRL to XL database). Now that we are swimming in a sea of XBRL, are we living in a world where analysis is easy, accurate and free (well cheap at least!)?<br />
<br />
When XBRL started, there was a tremendous <a href="http://www.blog.xbrlxl.com/2011/08/why-was-xbrl-invented.html">euphoria</a> that we were on the cusp of analytical nirvana. The first mandatory filing season quickly burst that bubble. XBRL it appeared (or certainly the FASB/SEC implementation) wasn't all it was cracked up to be. Those problems are <a href="http://www8.gsb.columbia.edu/ceasa/sites/ceasa/files/An%20Evaluation%20of%20the%20Current%20State%20and%20Future%20of%20XBRL%20and%20Interactive%20Data%20for%20Investors%20and%20Analysts.pdf">well documented</a> but all the same I thought it would be worth listing them here to see what if anything has changed. Has nirvana sailed back into sight or is it still a case of manana?<br />
<br />
So why couldn't I use XBRL? <br />
<br />
<span style="color: #93c47d;">There wasn't enough of it.</span> If I'd only got a few 10-K and Q filings, how could I do any meaningful historical analysis?<br />
<br />
<span style="color: #93c47d;">There was too much of it!</span> Over 16,000 tags, but I'm only interested in two or three hundred data points at the most! <br />
<br />
<span style="color: #93c47d;">It was complicated.</span> A lot of the interesting numbers that were being structured for the first time were held in weird structures - multi-dimensioned hypercubes. And what the hell is a <a href="https://docs.oracle.com/cd/E57185_01/DISUG/apa.html">tuple?</a><br />
<br />
<span style="color: #93c47d;">I couldn't access it.</span> Yes I could go to the SEC website and download it for free but then what? It certainly wasn't playing nice with Excel which was somewhat of a surprise for structured data.<br />
<br />
<span style="color: #93c47d;">This structured data didn't have any structure.</span> Why could I get a tagged figure for Revenues for Google but not for Microsoft. How does that work? And what was I meant to do with extensions which seemed to allow Filers to bypass completely any semblance of standardisation that might exist. <br />
<br />
So apart from too little data, too much data, obscure complicated structures, poor accessibility, evanescent structures, extensions, what else was wrong?<br />
<br />
<span style="color: #93c47d;">...Oh did I mention the errors?</span> Filings in the early years were <a href="http://sfmagazine.com/wp-content/uploads/sfarchive/2013/09/XBRL-XBRL-SEC-Filings-Are-Hampered-By-Errors.pdf">plagued with errors</a>. The top three spots on the podium of errors were taken in reverse order by missing required values (like err, earnings per share), negative values (due to yet another layer of complication) and right at the top, invalid axis combinations?? Exactly! (Filers were tying themselves up in tuples trying to dimension this stuff). The most oft cited error - the mis-used extension didn't even get a look in. Pushed way off the rostrum by more basic booby traps. <br />
<br />
So given what I've just said, the next question might seem a little rhetorical - <span style="color: #93c47d;">could I trust the source? </span>There ought to have been a simple answer to this question. Something along the lines of yes. I was no longer having to deal with a middle man to get my structured data (a vendor), it was being signed off by the company and of course audited. Except it wasn't - the audited accounts were a separate filing, the html 10-K. The xml XBRL was in a seperate file which wasn't audited and for which the Directors had no liability.<br />
<br />
I've mentioned the missing data values but I haven't mentioned the missing data. <span style="color: #93c47d;">Not everything was being xbrl'd.</span> It only applied to the financial statements and accompanying notes. Not for example the MD & A (Management Discussion and Analysis) into which companies often like to lob a lot of interesting and important values. And yes the 10-K got the treatment but not the preceding earnings announcement.<br />
<br />
So apart from too little data, too much data, obscure complicated structures, poor accessibility, evanescent structures, extensions, errors, unaccountability, missing tranches of data, was there anything else?<br />
<br />
Well...<span style="color: #93c47d;">it was only for US companies</span> and so in an increasingly globalised world in which the conventional orthodoxy of free trade prevailed and your competitor could be on the other side of the world then even if you managed to straighten out your data you might not have any companies to compare all this comparative data with. So is it possible that Donald Trump could be the saviour of XBRL?<br />
<br />
Realising that Donald Trump is not a satisfactory answer to my question (or indeed any question for that matter), I will address each of these issues in turn in my next few posts to see whether this dream is still giving analysts nightmares. For now I've run out of blog.<br />
<br />Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com3tag:blogger.com,1999:blog-7519651242666505822.post-1550581039675733392015-02-23T14:37:00.000+00:002015-02-23T14:37:57.926+00:00The SEC Structured Data Sets technically speakingIn my last post <a href="http://www.blog.xbrlxl.com/2015/02/the-sec-structured-data-sets-uh.html">The SEC Structured Data Sets</a>, I talked a little about this new SEC initiative to make XBRL more accessible. This time I'm gonna major on how it works.<br />
<br />
At this point you may want to refer to the SEC technical document <a href="http://www.sec.gov/data/financial-statements/aqfs.pdf">Annual and Quarterly Financial Statements</a>, the <a href="http://www.sec.gov/dera/data/financial-statement-data-sets.html">Financial Statement Data Sets page</a> (where the files reside) and if you want to see what the data contained in these files actually looks like, you can download one of our example spreadsheets <a href="http://www.xbrlxl.com/egs/XBRLFlatEg3.xlsx">here</a>. The web queries in this sheet access XBRL Flat, our name for this data set. The sheet itself contains links to videos & info on how it all works. I will talk more about our item for item implementation of this data set in my next post.<br />
<br />
On the Financial Statement Data Sets page, you will see there are currently 24 files. After we pass the last business day of this quarter (March 31 2015), they'll be 25. Don't try to open the latest files in Excel - they're too big but you could download one of the early ones to take a peak at the data layout of the files contained in these zips. <br />
<br />
As the comprehensive technical document explains, there are 4 files. The one that counts is the num.txt. This has the values. In theory this file by itself has enough in it to do your analysis - values matched with dates and most importantly, tags for each filing. The files are not cumulative so you need to access each one to be sure of finding your filing. This is the point, in other words, where you need to load all these files into a database. If you load it all, its gonna be big (over 10 gig for starters). <br />
<br />
The filings are keyed on the Accession Number (adsh) which is what EDGAR uses, so if you want to find the values for a particular company, you need to look up the adsh. This is where the sum.txt file comes in, which contains the company names & CIK's, so you need to load this into another table. Of course you could just find the adsh by going to EDGAR or our website - if you select a filing from <a href="http://www.xbrlxl.com/">xbrlxl.com</a>, the adsh corresponds to what the aNo = in the address bar, but the adsh adds some annoying dashes! (In our implementation, we use a more comprehensive and timely database for these lookups - this is what you see at xbrxl.com).<br />
<br />
You could stop there, as for example all the values we download in XBRL Flat come from just these two files. But if you want to see what the company has called these data items and if like us, you are sticklers for as reported data, then pre.txt contains the layout along with the labels. The final file, tag.txt contains important information on the tags but you may consider it not important enough.<br />
<br />
So what to watch out for? Duplicate values! - surely impossible but no it's been seen and verified in the original filings. And the fields aren't quite in the order shown in the documentation so use the header records. Also you may want to exclude any records where the coreg field is populated, as more than likely you ain't looking at a value for the entire consolidated entity in these cases (I anticipate this will become more prevalent and relevant when they release values for the notes).<br />
<br />
Two small bits of standardisation have occurred in these files that are not explicitly documented. <br />
<br />
The financial statement headings do not have standard names and tags in the US-GAAP implementation of XBRL i.e. what's in the original filings (Yes I know - ridiculous!) but they do now in this SEC data set; these names and codes (or shall we call them tags?!) are actually all listed in the technical document. <br />
<br />
The only slight problem is that a filing can have two financial statements which bear the same code (e.g. BS). One for the consolidated group and one for the parent. Can you tell the difference? Er No. Of course the parent one should have drastically less items. But of course if I'm gonna read this with a computer, I have to go through the hassle of counting items or something and that of course is not necessarily an exhaustive solution. There is also a code called UN (Unclassifiable Statement) which suggests that the SEC classifications may not themselves be exhaustive! I don't actually know why I'm going on about this as we solve this problem (differently) in our full database.<br />
<br />
Secondly, the month end of the dates attached to each value have been standardised. This is good and bad. Good as it makes searching and aggregating easier, unless you were specifically looking for values at Apple's year end (27th Sept 2014) when the values are held as 30th Sept. Bad as those few companies that don't adhere to standard month end periods (last Friday of the month etc) can have say 51 week or 53 week years and you wouldn't know it from the num.txt file. This could lead to say revenues being over or understated by 2% on an annual basis, more so if quarterly. To pick this up, you need to keep an eye on the period date in the sub.txt file (don't use the fye field as its filled in inconsistently - 0927 for Apple but 1130 for adobe).<br />
<br />
Probably worth re-iterating that no additional standardisation of data values has occurred in this data set. For more details on what needs to be done, see my <a href="http://www.blog.xbrlxl.com/2015/02/dealing-with-xbrl-data-issues-part-1.html">Missing Totals</a> post.<br />
<br />
Next time I will explain how we have replicated this database and what you can do with it.Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com1tag:blogger.com,1999:blog-7519651242666505822.post-44232472832801581352015-02-18T13:55:00.000+00:002015-02-24T18:19:11.810+00:00The SEC Structured Data Sets - uh?This <a href="http://www.sec.gov/news/pressrelease/2014-295.html">SEC Announcment</a> may have slipped your notice, as it was slid out at the back end of last year when certainly my thoughts were more about parties than databases. The SEC announced that some of that mountain of XBRL data that's been piling up on their computers is now available as a series of flat files. The <a href="http://www.datacoalition.org/">Data Transparency Coalition</a> certainly thought it was <a href="http://www.datacoalition.org/press-releases/data-coalition-applauds-sec-for-ringing-in-the-new-year-with-new-transparency-tool/">significant</a>.<br />
<br />
So what are these Structured Data Sets and what does this all mean for accessing Comparative XBRL data for financial analysis? By bulking it up and stripping out the markup they've made part of the XBRL filing data more accessible but with a number of caveats. Note I said "more accessible" and not "accessible". You can't open these files in Excel - you might think you can because they are tab separated flat files but actually you can't; they are too big. They are designed to be read into a database (from whence they come). So if you quickly just want to get hold of some tagged data for a few companies from the SEC, you're out of luck. Note you can also load the XBRL instance document into Excel as XML but that doesn't make it any more readable.<br />
<br />
But it is now much easier to read them into an database. Yes you still need to build an intermediary database. But you don't have to worry about context references and dimensions and all that messy XML tagging. e.g. In an existing XBRL filing I might find 43 values for "Revenues" but which is actually the one I want? In the flat file, num.txt, there will probably be just 3 values - one for each of the primary financial periods.<br />
<br />
Of course you don't have to build your own database - because we built one earlier! So if you quickly just want to get hold of some tagged data for a few companies then you can! We thought it would be an interesting exercise in evaluating the worth of this pilot program. We found it relatively easy and we like what we see. We chose to add a little pre-processing to the files, to make the resulting database run more efficiently and coalesce better with our existing ones. <br />
<br />
Our copy can be accessed in exactly the same way as all our data - through Excel. A simple web query brings the data into a sheet according to the parameters you supply. It works just like the existing <a href="http://www.xbrlxl.com/xbrlsheetinfo.asp">XBRL Sheet</a>. The query is available <a href="http://www.xbrlxl.com/egs/XBRLFlatEg3.zip">here</a> and the example XBRL Sheet <a href="http://www.xbrlxl.com/egs/XBRLFlatEg3.xlsx">here</a>. You can also find a video <a href="https://www.youtube.com/user/FundXJim">here</a> that shows you how to get started with the example sheet. Probably worth pointing out that because access is simply through the rendering of a customisable web page - the web query, access isn't confined to Excel; many other applications and programming languages can interface with this.<br />
<br />
So what are the caveats? It is only data from the Financial Statements themselves that are in these files. Nothing from the Notes to Financial Statements for now and new files only appear once a quarter. And it is just as it was filed - there are no corrections in these data sets.<br />
<br />
In the next couple of posts I'll talk more about the technical aspects, our implementation and the current limitations of this initiative.Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com1tag:blogger.com,1999:blog-7519651242666505822.post-30255387288009498752015-02-02T17:07:00.000+00:002015-02-02T17:22:37.574+00:00Dealing with XBRL data issues part 1 - missing totalsAs can be detected from the title, there are numerous issues, many of which in reality are (or can be seen as) positive features of XBRL! I'm gonna deal with each of them one by one by demonstrating the various strategies we use to create comparative data.<br />
<br />
I first touched on this in an earlier <a href="http://www.blog.xbrlxl.com/2012/09/comparing-xbrl-step-2-standardisation.html">post</a> and explained what was then our rather clumsy solution to the problem. <br />
<br />
Missing totals mean missing standard values when trying to do comparative analysis. They are missing because XBRL preserves the presentation that companies have always used to show their financial performance, namely the succinct and more readable presentation you see in a paper report. Why would you want to repeat a figure just to create a complete set of totals? There's no need, it will only create clutter and make it less readable.<br />
<br />
Fortunately we can do something about this and we do in the latest version of XBRL Sheet (the latest version is not yet generally available so email us - jimtruscott@fundamentalx.co.uk - if you would like to start using it). You can find more info on XBRL Sheet in this <a href="http://www.blog.xbrlxl.com/2013/07/xbrl-sheet-direct-access-to-xbrl-from.html">post</a> and you can watch XBRL Sheet solving the missing totals problem in this <a href="https://www.youtube.com/watch?v=BB30G3yMgz4">video</a>.<br />
<br />
Lets have a look at an XBRLSheet download...<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxZ18i5-L2YayV_HDm_1rM966GcCAjckeRQMAwMcL8Q4wC509O1nFELC-GTPMnIOJ8wofNAjgq2maWZrGyzdypH0koqQspIpDi6NMs0JvsDWbwS-7mYcRVMs_Q8GSCUEaVjt5qRvnpeNk/s1600/StdEg1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxZ18i5-L2YayV_HDm_1rM966GcCAjckeRQMAwMcL8Q4wC509O1nFELC-GTPMnIOJ8wofNAjgq2maWZrGyzdypH0koqQspIpDi6NMs0JvsDWbwS-7mYcRVMs_Q8GSCUEaVjt5qRvnpeNk/s400/StdEg1.PNG" /></a></div><br />
Before we used to download one column of tags, now we download two! The 1st column contains the standard tags which is our understanding of the high level tag that the company should be using in a "Standard" rendition of the values. This will usually marry up with the actual tag they used (in column 3) as per line 4 - Cost of Revenue in the example above. But if they have been more specific (which is great as more precise tags gives us a better understanding of their business), then we supply the different high level tag as well (which they haven't used as they would have to duplicate the line, creating a cluttered presentation as discussed above). So line 3 - Revenues is a case in point. Microsoft used the more specific tag "SalesRevenueNet". To make these easy to spot and check if necessary, the different standard tag will appear in a different colour. So we see another one further down. Again we may be interested in quantifying all provisions when a business is restructured rather than just goodwill. These two different tags enable us to do this.<br />
<br />
So how do we use this info in our model? Well as we always recommend (see this <a href="http://www.blog.xbrlxl.com/2012/01/looking-up-xbrl-in-excel.html">post</a> - specifically the bit about transparent data), you should connect with this data via an intermediate sheet (see below), to create values that can plug straight into your model.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzyvMVMc7fznZgmbBY5tFL60RtOAtcmdragGh6iFtNvbMfTwzEVpf2so7tN8zIPMMf7Ib9qY1Wwv9iwq2uD0UIpbedye0PmXslcWBG0MKaODoG9vELS_kV8SsHcWNTRObUakwvmTP9XZc/s1600/StdEg2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzyvMVMc7fznZgmbBY5tFL60RtOAtcmdragGh6iFtNvbMfTwzEVpf2so7tN8zIPMMf7Ib9qY1Wwv9iwq2uD0UIpbedye0PmXslcWBG0MKaODoG9vELS_kV8SsHcWNTRObUakwvmTP9XZc/s400/StdEg2.PNG" /></a></div><br />
Now with this extra column, we don't need to create a calculation to catch all the multiple alternatives for revenue (as represented by the Total Revenues line); we just need to use a simple lookup on "revenues" in this new column and the values will appear as shown. The 2nd column above contains the tag that is looked for. We in fact do a double lookup - we look in both tag columns in the Filings sheets to make sure we never miss an item and this also allows us to pick up the specific values if we want as well (e.g. the Revenue - Sales line). The names in the 1st column by the way are our names for the items and demonstrate how having an in-between sheet enables you to customise the data before it hits your model.<br />
Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-38134780377090085262014-01-24T14:08:00.000+00:002014-01-24T14:08:19.382+00:00Fixing errors in XBRL Instance documents again<i>You can find a previous example in the post "<a href="http://www.blog.xbrlxl.com/2014/01/fixing-errors-in-xbrl-instance-documents_8405.html">Fixing errors in XBRL Instance documents</a>" and an introduction to XBRL data errors <a href="http://www.blog.xbrlxl.com/2014/01/xbrl-data-errors.html">here</a>.</i><br />
<br />
The next example is a little more subtle. Wrong but not immediately obvious, unless you are trying to model some business sectors, perhaps using our <a href="http://www.xbrlxl.com">Sector3</a> product! - find loads more info on our award winning product <a href="http://xbrl.us/research/appdev/Pages/498.aspx">here</a>.<br />
<br />
Boeing in their 2012 10-K slightly changed the tag for one of their top level business segments in one of the sections showing data for their businesses. It was referring to exactly the same segment (the label i.e. the description was the same) but the tag was different. A mistake - someone wasn't paying attention. In fact with reference to the previous example, they created an entirely bogus "context" for this identical segment.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiR80P2Rl4IUpsixcHeFir-BvDbzRiDQtNI5lEtsXbA_VP-iixoAVL9rJREM23oHSzDOiV0Qzm9_-EJUaldmrpGxZQqvxDVJ5TslekN4-7lSq-Rg3c_0WEvTVCM_4McoMZ9Q_Iv50yXFi0/s1600/InstErr4.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" width="500" height="60" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiR80P2Rl4IUpsixcHeFir-BvDbzRiDQtNI5lEtsXbA_VP-iixoAVL9rJREM23oHSzDOiV0Qzm9_-EJUaldmrpGxZQqvxDVJ5TslekN4-7lSq-Rg3c_0WEvTVCM_4McoMZ9Q_Iv50yXFi0/s640/InstErr4.PNG" /></a></div><br />
So we got rid of it, replacing all connections to it with the correct context reference (shown below). In fact there was more than one wrong context so they all went the same way.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5Fh7UaCqxxWiNTQVZfsuMxqx8MKu-cSRDtCAdXU107QHHS4glRcAmycAbQRoAe2fX7JkiMPDXBnejPND3cdF-0OB-o0JyaRhOvu9HrNBdgsnufkwtKpmAEuU8_hRFNE1PjQrYT5_x3xc/s1600/InstErr5.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" width="500" height="70" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5Fh7UaCqxxWiNTQVZfsuMxqx8MKu-cSRDtCAdXU107QHHS4glRcAmycAbQRoAe2fX7JkiMPDXBnejPND3cdF-0OB-o0JyaRhOvu9HrNBdgsnufkwtKpmAEuU8_hRFNE1PjQrYT5_x3xc/s640/InstErr5.PNG" /></a></div><br />
There was a little more work to do here than previously as a tag comes with a panoply of associated data - labels, definitions & the like, all of which we felt it was prudent to remove. Details were as ever recorded in the "xsd" file as shown below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkf-3pYSas58pQHH1mrCZzgLt6XaW6o40ISHVWoYEH7eR8wgGKff-4DvixpZQymo3bfoGtdcb7Xi-b3bURg2tCxa-S0ZNoQnx20rmBeiG3khClkwLBvmbUmyZUXp2IOqJipTfplqgI8_o/s1600/InstErr6.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" width="500" height="42" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkf-3pYSas58pQHH1mrCZzgLt6XaW6o40ISHVWoYEH7eR8wgGKff-4DvixpZQymo3bfoGtdcb7Xi-b3bURg2tCxa-S0ZNoQnx20rmBeiG3khClkwLBvmbUmyZUXp2IOqJipTfplqgI8_o/s640/InstErr6.PNG" /></a></div><br />
The consequence of this error was that data was missing for assets in Boeing's Defense, Space And Security business. Well it isn't anymore in Sector3.Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0tag:blogger.com,1999:blog-7519651242666505822.post-2624058991071182572014-01-23T20:26:00.000+00:002014-01-23T20:26:30.509+00:00Fixing errors in XBRL Instance documents<i>(Should be read in conjunction with the post – <a href="http://www.blog.xbrlxl.com/2014/01/xbrl-data-errors.html">XBRL Data Errors</a>)</i><br />
<br />
In my last post, I talked about the different XBRL errors that you could possibly encounter. And we at <a href="http://www.fundamentalx.co.uk/">Fundamental X</a> are going to fix errors in the first two categrories (XBRL formatting errors & stupid mistakes) whenever we come across them.<br />
<br />
This approach suits us as the data we currently output to Excel via <a href="http://www.xbrlxl.com/">XBRL to XL</a> and Sector3 is generated on the fly from the original instance documents filed with the SEC. And we figure there are a lot of people out there who prefer to deal with the original documents rather than data that’s been mangled through a database.<br />
<br />
XBRL formatting errors are rare. Stupid mistakes made in the creation of the XBRL filing are not (Invalid Axis Member Combination, one of the <a href="http://xbrl.us/research/pages/csuiteissues.aspx">XBRL US classifications</a> of this type of mistake, currently sits at the top of the error leader board) so I’m going to focus on these.<br />
<br />
The following two examples should give you a flavour of the nature of these errors and how we will fix them.<br />
<br />
<u>Texas Capital Bancshares 2012 10-K</u><br />
<br />
If you look at the Document and Entity Information in the <a href="http://www.sec.gov/cgi-bin/viewer?action=view&cik=1077428&accession_number=0001193125-13-068855&xbrl_type=v#">interactive data</a> on the SEC site, you immediately see the problem:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioFPxAP_xcpny5rb3r-3uAXj6MTwMmfufeBYS6ogElLhyQoysVZk3mW8Ye4BvIRUHMmTLu3m1tDP8YPnl4qkY9WqlWHgsQualLMCsfu80Ptq0zfMOeM0CLSmzLAXmzFsYmPIqgjmJHW7k/s1600/InstErr1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioFPxAP_xcpny5rb3r-3uAXj6MTwMmfufeBYS6ogElLhyQoysVZk3mW8Ye4BvIRUHMmTLu3m1tDP8YPnl4qkY9WqlWHgsQualLMCsfu80Ptq0zfMOeM0CLSmzLAXmzFsYmPIqgjmJHW7k/s320/InstErr1.PNG" /></a></div><br />
The "Document Period End Date" and the axis date don't match. That's because the date for this filing hasn't been updated in the XBRL from what is more than likely a previous XBRL filing (you can double check that the date is indeed incorrect by referencing the html <a href="http://www.sec.gov/Archives/edgar/data/1077428/000119312513068855/d468799d10k.htm">10-K</a>), Therefore the dates for the most important contexts in the entire filing are wrong. At this point our processing software throws a wobbly and we fix the error in the instance document. Seeing it is an elementary error in the XBRL, it seems judicious to fix it in the source rather than further down the processing and storage road.<br />
<br />
Of course this kind of error should be bought to the attention of the SEC and the company concerned. The filing will not be corrected but I think an additional amended 10-K/A would be filed instead. But markets wait for no man.......<br />
<br />
So what do we do? Well here is what the error looked like in the XBRL:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4HoBFjkWpp4837_YothTGki7c-0E0sYh9mgBmZOS-OIjmcqn5theH1L8DNkKasEH8qqqO9hvSMyk-rmC0PNByP60gHfPkQkBqqNBSERnn-PBFeAU2g-c8TQFxa1ekJ4VhQ7rlRwqlaYs/s1600/InstErr2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4HoBFjkWpp4837_YothTGki7c-0E0sYh9mgBmZOS-OIjmcqn5theH1L8DNkKasEH8qqqO9hvSMyk-rmC0PNByP60gHfPkQkBqqNBSERnn-PBFeAU2g-c8TQFxa1ekJ4VhQ7rlRwqlaYs/s400/InstErr2.PNG" /></a></div><br />
So we changed the date in the context (and all other relevant contexts) and corrected the context name to reflect the change. At this point all references to this context also needed to be changed. It's important to note that we didn't re-create the XBRL filing as we want to retain the integrity of the existing filing, so we merely amended it. All changes are noted at the top of the file involved and all file changes are summarised in the xsd file as shown below:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMxLz2LVpSVrOAs2ZxN5dYpFdWYXpqUz7lbub2BErReu7C0zWSZBo4b0hIEVkFsYyaKvmDt8PmV333Mh1aO_RNJfZ85r5F-xAXCmFtarC4ZLH5-w2AoA8uUked5zNAsoIm0Dz-u0-90Lw/s1600/InstErr3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" width="500" height="35" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMxLz2LVpSVrOAs2ZxN5dYpFdWYXpqUz7lbub2BErReu7C0zWSZBo4b0hIEVkFsYyaKvmDt8PmV333Mh1aO_RNJfZ85r5F-xAXCmFtarC4ZLH5-w2AoA8uUked5zNAsoIm0Dz-u0-90Lw/s640/InstErr3.PNG" /></a></div><br />
The amended files are then used in preference to those on the SEC site when generating <a href="http://www.xbrlxl.com/">XBRL to XL</a> products. <br />
<br />
I feel this post is getting a little tedious so the second example will have to wait till the next post. You can download the amended XBRL instance document for Texas Capital Bancshares in it's entirety from <a href="http://www.xbrlxl.com/egs/0001193125-13-068855.zip">here</a>.Jim Truscotthttp://www.blogger.com/profile/04795494311746888733noreply@blogger.com0