How to deal with duplication

[Tim Bui]

I was trying to download the revenues of different companies, using Rev_Tot = Revenues, SalesRevenueNet, SalesRevenueGoodsNet or TotalRevenuesAndOtherIncome

However, some companies changed their tags and the API returns 2 numbers of sales for the same year. Using APD Corp (CIK 0000002969) as an example:

As you can see below, APD used the tag “SalesRevenueNet” from 2009 to 2017, but
in 2018 it started to use “Revenues.” Thus the “Revenues” are reported for 3 years, 2018,2017 and 2016. Since APD did not go back to change the tag for the previous years, we end up have duplications for 2016 and 2017 that were reported under “SalesRevenueNet” tag.

I noticed that this situation occurs at other companies as well.

Is there any way we can eliminate the duplications?

Thank you!

APD Revenues_Y16 9/30/2016 7503.7
APD Revenues_Y17 9/30/2017 8187.6
APD Revenues_Y18 9/30/2018 8930.2
APD SalesRevenueNet_Y09 9/30/2009 8256.2
APD SalesRevenueNet_Y10 9/30/2010 8616.1
APD SalesRevenueNet_Y11 9/30/2011 9673.7
APD SalesRevenueNet_Y12 9/30/2012 9611.7
APD SalesRevenueNet_Y13 9/30/2013 10180.4
APD SalesRevenueNet_Y14 9/30/2014 8384
APD SalesRevenueNet_Y15 9/30/2015 7824.3
APD SalesRevenueNet_Y16 9/30/2016 7503.7
APD SalesRevenueNet_Y17 9/30/2017 8187.6

[Tim Bui]

I worked around the duplication issue by copying the data into Excel and naming the concept as X_Rev_Tot_Yxx, then use “Remove Duplicate”. In this case the duplicates are 2016 and 2017.

ADP X_Rev_Tot_Y10 09/30/10 8,616.10
ADP X_Rev_Tot_Y11 09/30/11 9,673.70
ADP X_Rev_Tot_Y12 09/30/12 9,611.70
ADP X_Rev_Tot_Y13 09/30/13 10,180.40
ADP X_Rev_Tot_Y14 09/30/14 8,384.00
ADP X_Rev_Tot_Y15 09/30/15 7,824.30
ADP X_Rev_Tot_Y16 09/30/16 7,503.70
ADP X_Rev_Tot_Y16 09/30/16 7,503.70
ADP X_Rev_Tot_Y17 09/30/17 8,187.60
ADP X_Rev_Tot_Y17 09/30/17 8,187.60
ADP X_Rev_Tot_Y18 09/30/18 8,930.20

[David Tauriello]

Tim – fact.ultimus-index=1 returns the latest reported value for the fact(s) in the query. Each increment of the integer represents a previous report in which the fact.value was reported, although the number(s) may or may not have changed.

We’re planning to convert fact.ultimus to a boolean in 2019 (it’s currently integer, so it functions the same as the ‘index’), so eventually ‘fact.ultimus=true’ will return the same data points as fact.ultimus-index=1

See Restated markers for more information.

[Peter Guldberg]

If I understand Tim correctly then both the Revenues and SalesRevenueNet facts have ultimus-index of 1.
The problem can be that some companies use one concept name and other companies use another for the same concept. This is partly what we tried to solve in the balance sheet template by mapping other concepts to the same “main” concept.
The way we handled it in there was by using the different concept names as mutually exclusive alternatives. So, we try if the first concept name works and returns a valid number, if it doesn’t we try the next alternative etc.
In the sheet that you and I were working on Tim, I think the lookup function would just pick the first occurence of the two concepts.

[Author]

Posts