Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How do I use Power Query to get data from SEC EDGAR with API and/or M?

I've just about exhausted Google and Bing searches here.

 

WHAT I'M TRYING TO DO
I'm trying to find out how to use Power Query to connect to web data source (www.sec.gov) so that I can extract company filings and financial data directly into Power Query, then excel

 

I do not have the ability to use Python to assist (Tech department has controls on this) so I need to know how to use Power Query and/or M Code

 

I've read through the below link a lot of times and have not had success. There is a way to do it but I'm just not smart enough

https://www.sec.gov/edgar/sec-api-documentation 

 

I can 'see' the filings i need in Power Query but can't find a way to extract the content

IF SOMEONE CAN ASSIST
I would have the following data as an 'input' somewhere on 'Sheet1':
1. Ticker Symbol of company (i.e MSFT)
2. CIK (per API documentation)

 

 

https://data.sec.gov/ 

 

 

 

 

 

 

 

Need Help

1 Reply

  • artemus's avatar
    artemus
    Microsoft Employee
    3 years ago

    Hrm... interesting... it looks like they have the data column per column, instead of row per row. Anyways, go to the advanced editor and try this:

    let
    Source = Json.Document(Web.Contents("https://data.sec.gov/submissions/CIK0000789019.json")),
    filings = Source[filings],
    recent = filings[recent],
    ToTable = #table(type table Value.Type(recent), List.Zip(Record.FieldValues(recent)))
in
    ToTable

     

    You can replace "https://data.sec.gov/submissions/CIK0000789019.json" with "https://data.sec.gov/submissions/" & CIK & ".json" if you have CIK stored as its own query (drill down on the cell that has the value.