Arelle + Google Apps Script = Recent Filings Viewer

I discovered a few exciting things about 1) arelle 2) Google App Scripts– over the course of the week. But I’ll save the boring-stuff for the bottom of the post. Log in to your Google account and open up this Google Spreadsheet.

Warning to XBRL Gurus; this is nothing miraculous. Indeed, the code mainly parsed the SEC’s RSS feed.

Warning to Excel-ophiles; Google Spreadsheet is fantastic, and can create Excel outputs. Try it, you’ll like it.

 

Here are the three steps:

 

Choose Finanze > Recent Filings

The sheet should install a “Finanz” in your toolbar. Click it, and choose “Recent Filings” from the dropdown

Click on the "Refresh" button. You'll know it's working if it says "Loading..." after your click. It is loading the SEC's latest XBRL filings, which they publish in RSS format here: http://www.sec.gov/Archives/edgar/usgaap.rss.xml

Click on the “Refresh” button. You’ll know it’s working if it says “Loading…” after your click. It is loading the SEC’s latest XBRL filings, which they publish in RSS format here: http://www.sec.gov/Archives/edgar/usgaap.rss.xml

Hover or click a company for options

After the latest filings have loaded, hover (or click) a company for more options. Choose the “Load” button. Later on, I’ll make the “Info” button show some summary statistics.

Loads the XBRL data into the spreadsheet

arellecmd.appspot.com processes the XBRL instance document, and I print the facts here (sorted by number-of-facts-which-use-that-element; an additional column I added, “Count”). Now you can export to Excel, sort, filter, or make graphs.

I think you can “make a copy” to view all the source code (and modify it for yourself!)  Google Developer has great documentation.

Here are some things I learned, in quick succession:

<div class="progress-bar" data-caja-role="progressbar" data-caja-aria-valuenow="45" data-caja-aria-valuemin="0" data-caja-aria-valuemax="100" style="width: 45%;"> <span class="sr-only">45% Complete</span> </div>
    • Is “Service”-oriented
      • Reaches to scripts run on server, but written in Javascriptin files suffixed “.gs” (GoogleScript?)
    • Can write scripts for different contexts:
      • UI/Client-side Scripts
        • Can use libraries; but not like running directly in a browser
      • Server-side Scripts
        • suffixed .gs
        • Run on server; no worries about browser support?
        • For instance, I can trust gs to create a JS (GS?) Date object from a standard date string
    • Can access Google API’s
    • Can be deployed as a Web App, or as an add-on
      • Process for publishing to App Script library:
        1. Container-bound
        2. Authorization lifecycle
        3. Apply to be published
    • Has its own template language inside its HTMLService

 

 

EDIT: I realize arelle already made this site to search the latest, with the JSON URLs here 

Advertisements

2 thoughts on “Arelle + Google Apps Script = Recent Filings Viewer

  1. dd says:

    Hi Nate! Good write up!
    Is it possible with arelle to get the facts for a particular kind of statement – like the statement of income / Consolidated balance sheet etc.? Different companies seem to be using different elements to report their financials. In such case how is a income statement created in a automated way?

    • redpeas says:

      Thanks, dd.

      Yes I think you can get the facts for a particular kind of statement. For example, in this recent XBRL filing from the SEC: https://www.sec.gov/cgi-bin/viewer?action=view&cik=29915&accession_number=0000029915-18-000026&xbrl_type=v you can see separate statements, including Income Statement and Balance Sheet. The SEC does this using the XBRL “Presentation Linkbase” metadata, which is in a separate file from the facts data.

      My blog post here focuses on a single REST API which gives a “flat” list of facts, I’m not sure you can query it for “Income Statement”. however, if you open an XBRL filing *package* in the Arelle desktop app, Arelle can analyze the “Presentation Linkbase”. This Presentation Linkbase shows how certain elements are “grouped” into reports: “presentation linkbase” is the same metadata the SEC used to group facts. Therefore with Arelle Desktop app can analyze : facts -> elements —presentation linkbase ->reports . While Arelle shows you the presentation linkbase in its UI; you can use the Arelle Python API to parse all information about a filing, including its facts, its elements, its presentation linkbase and more!

      ALSO please check the XBRL US “XBRL API”, which lets you query a very feature-rich API,
      without downloading an app, or writing Python code: https://xbrl.us/home/use/xbrl-api/ In the XBRL US API documentation (https://xbrl.us/wp-content/uploads/2018/09/XBRL-API-V1.pdf) you can see an example which queries by these relationships to see “Balance Sheet”-related relationships. I’m not sure if this API call returns facts directly, but you can combine this relationship API call with a fact API call to figure your results
      /api/v1/network/27624452/relationship/search?relationship.sourcename=BalanceSheetComponentsDisclosureAbstract&fields=network.*,relationship
      .*,relationship.limit(4),relationship.tree-sequence.sort(ASC)

      XBRL US also demonstrates how their API can be integrated into Google Spreadsheet, which is nice.

      Finally the SEC recommends some other tools/vendors who may do what you need, including idaciti https://xbrl.us/home/use/howto/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: