XBRL Abstract Model Database- How to load all items of Consolidated Balance Sheets?

[arden liu]

I have loaded some xbrl files inot XBRL Abstract Model Database.

Cik of Alphabet Inc is  0001652044

--- I can use the following sql to find 10-Q report_id of Alphabet.

select dp.report_id

 from data_point dp

 join aspect a on dp.aspect_id = a.aspect_id

 join entity_identifier ei on dp.entity_identifier_id = ei.entity_identifier_id

 where a.aspect_id in (

  select a.aspect_id from aspect a where a.name = 'DocumentType'

 )

 and

  dp.value in ('10-Q')

 and  

  ei.scheme='http://www.sec.gov/CIK' and ei.identifier='0001652044'

--- The result of above sql is:

47009378
88639810
123898394

---------------------------------------------------------------------------------------------------

Then I run the following sql to get aspect_id of AssetsCurrent:
------

select a.aspect_id,a.*
from aspect a
where a.name in ('AssetsCurrent')
--- The result of above sql is:

123480
16407
25328645

---------------------------------------------------------------------------------------------------

Then I can get all Current Assets of Alphabet from 10-Q report.

select p.end_date,dp.value

from data_point dp join period p on dp.period_id = p.period_id

where

dp.report_id in (

47009378,88639810,123898394 -- the result from first sql

)

and

dp.aspect_id in (

123480,16407,25328645 -- the result from second sql

)

---------------------------------------------------------------------------------------------------

But I found different company assets may include different sub items.

For Tesla:
Assets = LiabilitiesAndStockholdersEquity = AssetsCurrent + PropertySubjectToOrAvailableForOperatingLeaseNet + tsla_LeasedAssetsNet + PropertyPlantAndEquipmentNet + OperatingLeaseRightOfUseAsset + IntangibleAssetsNetExcludingGoodwill + Goodwill + LongTermAccountsNotesAndLoansReceivableNetNoncurrent + RestrictedCashNoncurrent + OtherAssetsNoncurrent

I want to get all those sub items for Assets, Or I want to get all items of Consolidated Balance Sheets.

How do I know which aspect ids are part of Consolidated Balance Sheets? which aspect ids are part of Assets ?

Thanks.

Arden

[arden liu]

I also loaded xbrl files inot XBRL-US Public database.

When I run the following sql, it will return:

select * from relationship where to_element_id = 17807 -- 17807 is CashCashEquivalentsAndShortTermInvestments

------

from_element_id    to_element_id
17807    16458
17807    4933
------

16458 is CashAndCashEquivalentsAtCarryingValue

4933 is MarketableSecuritiesCurrent

Then I know this relationship:
CashCashEquivalentsAndShortTermInvestments = CashAndCashEquivalentsAtCarryingValue + MarketableSecuritiesCurrent

Let me check XBRL Abstract Model Database, maybe we have the similar relationship.

Thanks.

[arden liu]

XBRL Abstract Model Database also has relationship table. Now, I can find the relationship as well.

[arden liu]

I can use the relationship to figure out the relationship between some aspects.

When I load those aspects from data_point, normally, current quarterly report includes some data(same aspects) for previous quarter.

I want to get the data for current quarter. I think I have to check period table (from period_id of data_point), but I found one report could have many period_id.

It's a little bit harder to check start_date and end_date to find which period is for current quarter.  

I found is_instant column from period table, what's the meaning of that column?

Is there any easy way to get current quarter data?

Thanks

[arden liu]

I loaded google goog10-qq12019.htm (https://www.sec.gov/Archives/edgar/data/1652044/000165204419000015/0001652044-19-000015-index.htm)

I want to find NetIncome(Value=6657000000) for Q1.

I found one aspect(NetIncomeLoss 5122) have many data points, which have the same report_id(1), same document_id(18), same aspect_id(5122), same period_id(23551).

There have different xml_id, context_xml_id, xml_child_seq.

Now, I have four different values(6657000000, -7000000, 104000000, 97000000). Is there a way to distinguish those four values?

----------------------------------------------------------------------------------------

datapoint_id report_id document_id aspect_id period_id value aspect_value_selection_id xml_id context_xml_id xml_child_seq

23856 1 18 5122 23551 6657000000 <null> d80229267e1152-wk-Fact-B0B29B4D34E3505EB3119933C0A9B182 FD2019Q1YTD /1/2/109/1/1/18/7/1/1/1/1
23862 1 18 5122 23551 6657000000 <null> d80226141e683-wk-Fact-B0B29B4D34E3505EB3119933C0A9B182 FD2019Q1YTD /1/2/120/1/1/6/7/1/1/1/1
23932 1 18 5122 23551 6657000000 23592 d80230647e2838-wk-Fact-2022D690BC312C5105C7FE3E0CF5D974 FD2019Q1YTD_us-gaap_StatementEquityComponentsAxis_us-gaap_RetainedEarningsMember /1/2/132/1/1/13/11/1/1/1/1
23933 1 18 5122 23551 6657000000 <null> d80230647e2857-wk-Fact-B0B29B4D34E3505EB3119933C0A9B182 FD2019Q1YTD /1/2/132/1/1/13/14/1/1/1/1
23942 1 18 5122 23551 6657000000 <null> d80224516e723-wk-Fact-B0B29B4D34E3505EB3119933C0A9B182 FD2019Q1YTD /1/2/144/1/1/7/7/1/1/1/1
24489 1 18 5122 23551 -7000000 23575 d80253576e3081-wk-Fact-C8282A97017E544CAA10E59ED7AEC855 FD2019Q1QTD_us-gaap_ReclassificationOutOfAccumulatedOtherComprehensiveIncomeAxis_us-gaap_ReclassificationOutOfAccumulatedOtherComprehensiveIncomeMember_us-gaap_StatementEquityComponentsAxis_us-gaap_AccumulatedNetUnrealizedInvestmentGainLossMember /1/2/274/3/1/1/1/10/8/1/1/1/1
24497 1 18 5122 23551 104000000 23611 d80253576e3358-wk-Fact-8FC731EE26C35C8C81BAEF17759470A2 FD2019Q1QTD_us-gaap_ReclassificationOutOfAccumulatedOtherComprehensiveIncomeAxis_us-gaap_ReclassificationOutOfAccumulatedOtherComprehensiveIncomeMember_us-gaap_StatementEquityComponentsAxis_us-gaap_AccumulatedGainLossNetCashFlowHedgeParentMember /1/2/274/3/1/1/1/15/8/1/1/1/1

24499 1 18 5122 23551 97000000 23571 d80253576e3418-wk-Fact-DF45727891C657BEA30BC91EAF8E4AE7 FD2019Q1QTD_us-gaap_ReclassificationOutOfAccumulatedOtherComprehensiveIncomeAxis_us-gaap_ReclassificationOutOfAccumulatedOtherComprehensiveIncomeMember /1/2/274/3/1/1/1/16/8/1/1/1/1

---------------------------------------------------------------------------------------

[Sherif ElGamal]

There should be a 'context_xml_id' column.