Forum Replies Created
-
AuthorPosts
-
Tim BuiParticipant
Hi Mikko, I am not a real programmer but here is how I get the Fin Data Set into my Postgresql (I do not use MySQL).
– Within each quarterly release, there is a readme.html file that lists the fields of each of the 4 txt files. I create 4 tables in my Postgresql with these fields using the required datatypes. I use the fields of each table as listed on Section 3 (Organization) as primary keys.
– I then use Python to clean up the data of each of the txt file and save as csv files with ‘~’ as delimiter (not comma because some fields have commas).
– Finally I use the Copy command in Postgresql to import the csv files into Postgres.David TaurielloKeymasterFrom an XBRL US Member:
Is there way to keep track of changing taxonomy over the period of time? I am mot sure if companies keep track of it or not.
I am trying to monitor the tags released, added, changed, deleted over the period of time and also wanted to see if any tag after being deleted/discarded was inserted back.
David TaurielloKeymasterI’m not aware of any end-user analytics products that monitor concept lineage. With the XBRL API, you can use the concept object to query by dts.id for elements in a filing (or include concept.local-name to narrow the results). Something like this would return a few extension elements in Kraft Heinz financials from the past couple of years:
https://api.xbrl.us/api/v1/concept/search?dts.id=439705,418858,401372,383175,365074,348665,338987,338941&concept.local-name=Nonmonetarycurrencydevaluationlossgain,Carryingvalueoftotaldebt,Equityawardcompensationexpenseexcludingintegrationandrestructuringexpenses&fields=dts.id.sort(DESC),concept.namespace,concept.local-name,concept.substitution,label.text
The full list of available fields for the /concept endpoint is available from the XBRL API Interactive Documentation linked at right.
Tim BuiParticipantHi David,
Do you know why the US GAAP Taxomony for 2018 in XLS format is not downloading? I have no problems with other years.
(Essentials
All Taxonomies: viewer including disclosure templates || .zip format || .xls format)Thank you, David!
David TaurielloKeymasterHi Tim – thanks for writing; the link on https://xbrl.us/2018-us-gaap is fixed. FWIW, we post details based on what FASB publishes initially, so if the location of the file is updated our link breaks. We also link to the ‘FASB Explanatory Page’ for each Taxonomy they publish – if our link is broken, you should find the updated link on their page.
Tim BuiParticipantAs always, I am grateful for your help, David!
Bruno LererParticipantIn order to try out the API (using this interface (although I don’t think it makes a difference), I randomly picked a company (Carnival Corporation), a form (10-K), two reporting periods (2019,2020) and one concept (local name “LiabilitiesCurrent”). In an ideal world, only two hits should be returned (“8,686” for 2020 and “9,127” for 2019, in each case using “fact.inline-display-value”).
However, this search
https://api.xbrl.us/api/v1/fact/search?concept.local-name=LiabilitiesCurrent&dimensions.count=0&entity.cik=0000815097&period.fiscal-period=Y&period.year=2020,2019&report.document-type=10-K&fields=concept.id,concept.local-name,dimensions,dts.id,entity.name,fact.inline-display-value,period.year,report.entry-url
returns 4 hits. The correct value for 2020 is returned once in the 2020 filing. That filing, however, also returns the 2019 value which is included as comparison in the 2020 financials. Hence the first question: is it possible to exclude items related to periods other than the period for which the specific filing is filed?
The 2019 filing returns the 2019 value twice: once, correctly, as it appears in the balance sheet, but once again since it appears in one of the notes to the financials. Hence question two: is it possible to limit hits to only those concepts included, in this case, in the balance sheet?
Thanks.
David TaurielloKeymasterHi Bruno – apologies for the delay in getting to your questions.
On the first question – to restrict values to a single year, remove the other year from the query.
If you want to use both the 2019 and 2020 values from the 2020 report, you should first get the dts.id or report.id from the /report endpoint.
https://api.xbrl.us/api/v1/report/search?entity.cik=0000815097&report.document-type=10-K&fields=report.entity-name,report.id,dts.id,report.limit(3),report.entry-type,report.period-end.sort(DESC)
Use either dts.id or report.id to return facts from the report for the years required.
https://api.xbrl.us/api/v1/fact/search?dts.id=435013&concept.local-name=LiabilitiesCurrent&dimensions.count=0&entity.cik=0000815097&period.fiscal-period=Y&period.year=2020,2019&report.document-type=10-K&fields=concept.id,concept.local-name,dimensions,dts.id,entity.name,fact.inline-display-value,period.year,report.entry-url
We’re working on a filter parameter that will prevent ‘duplicate’ values like you note for Carnival in 2019, and we’ll update the community when it’s available.
Let me know if I’ve misunderstood your questions.
Arthur OlevskiyParticipantHi David,
I’m trying to figure out with /api/v1/network/relationship/search endpoint and get Balance Sheet network.role-description. I was trying different ways, unfortunately all attempts wasn’t successful. However, I have find this endpoint useful.
Can you point me how I can get Balance Sheet data using this endpoint, please.
Thanks in advance.
Friday, June 11, 2021 at 4:27 PM in reply to: API question /api/v1/network/relationship/search #192055Arthur OlevskiyParticipantCIK 0000769397
DTS ID 27599Friday, June 11, 2021 at 6:08 PM in reply to: API question /api/v1/network/relationship/search #192057David TaurielloKeymasterHi Arthur –
Try a known, good query like
/relationship/search?dts.id=306447&network.link-name=presentationLink&network.role-description=balance%20sheet&stringmatch=network.role-description(like)&fields=network.role-description,relationship.id.sort(ASC),relationship.tree-sequence.sort(ASC),relationship.target-name
be sure the stringmatch parameter exists in the dts.id you’re calling (or try ‘statement’ to list all in the report – this may take a minute or two).
and see also https://xbrl.us/forums/topic/how-to-get-specific-statement-from-report/
Sunday, June 13, 2021 at 1:34 PM in reply to: API question /api/v1/network/relationship/search #192076Arthur OlevskiyParticipantHi David,
Thank you so much.
Matthew BeveridgeParticipantTim, Mikko, and others — I have a primitive (so far) python package I am working on to simplify manipulating this data by hosting the dera financials database remotely and standardizing query results into pandas dataframes. If you are interested in collaborating on this, don’t hesitate to reach out. I plan for an alpha release in the next few weeks.
Functionality will be along the lines of:
# import the package from mypackage import fundamentals # get the data form = fundamentals.ten_k('aapl', [2018, 2019]) # manipulate the data debt_cap = form.debt_capitalization() margin = form.gross_margin() roe = form.return_on_equity()
and so on. Results will look like:
print(form)
fy 2018 2019
tag uom
AccountsPayableCurrent USD 5.588800e+10 4.623600e+10
AccountsReceivableNetCurrent USD 2.318600e+10 2.292600e+10
AccruedIncomeTaxesNoncurrent USD 3.358900e+10 2.954500e+10
AccumulatedDepreciationDepletionAndAmortization… USD 4.909900e+10 5.857900e+10
AccumulatedOtherComprehensiveIncomeLossNetOfTax USD -3.454000e+09 -5.840000e+08
… … …
UnrecordedUnconditionalPurchaseObligationBalanc… USD 9.328000e+09 8.211000e+09
UnrecordedUnconditionalPurchaseObligationDueAft… USD 6.600000e+07 1.100000e+08
WeightedAverageNumberDilutedSharesOutstandingAd… shares 4.473200e+07 3.107900e+07
WeightedAverageNumberOfDilutedSharesOutstanding shares 5.000109e+09 4.648913e+09
WeightedAverageNumberOfSharesOutstandingBasic shares 4.955377e+09 4.617834e+09‘print(debt_cap)`
[251 rows x 2 columns]
fy 2018 2019
tag uom
DebtCapitalization ratio 0.707029 0.732692- This reply was modified 3 years, 1 month ago by Matthew Beveridge. Reason: code formatting
- This reply was modified 3 years, 1 month ago by Matthew Beveridge. Reason: code formatting pt 2
- This reply was modified 3 years, 1 month ago by Matthew Beveridge. Reason: include example results
Tim BuiParticipantHi Matthew, I would love to pitch in and help in anyway I can. After spending months on it, I almost gave up on the project because I couldn’t think through the proper way to standardize the financial items. I am a new student of Python, so hopefully I can learn from your Python program. Please let me know what steps I can do to help.
Mikko OlkkonenParticipantHi Tim, Matthew and others,
I am now reading dera data (and European style ESEF XBRL) to mongoDB databases (i.e. I abandoned SQL). I have developed server functionality for querying the data with somebody. Our server code is mainly typescript, javascript/node.js. I guess that we have produced samewhat similar solution as Matthew has. Loose standardisation/use of the taxonomy/tags is our key problem even if we only need limited number of key items including ProfitLoss, Revenue, Cash, …
Tim, Matthew: where do you collaborate? In Github? -
AuthorPosts