CODEWITHSUNDEEP
excelweb-scrapinggoogle-sheetspowerquery
user113156
user113156

Reputation: 7107

Updated solution to importing financial information into Excel or Google Sheets?

Does anybody know of an updated solution to import financial statments (balance sheets, income statements, cash flows) from yahoo finance / google finance into excel or google docs?

Since they both changed their API / URLS I have not been able to find a working solution.

Running the following powerquery seems to import the correct data, but its slow and the data gets stored as text and VALUE()does not seem to convert to numeric very well.

let
    Ticker = Excel.CurrentWorkbook(){[Name="Ticker"]}[Content]{0}[Column1],
    Quelle = Web.Page(Web.Contents("https://finance.yahoo.com/quote/"&Ticker&"/balance-sheet?p="&Ticker&"")),
    Data = Quelle{2}[Data],
    FirstRowHeader = Table.PromoteHeaders(Data)
in
    FirstRowHeader

I can´t seem to get it working with importhtml in google docs either.

EDIT: I have the following excel data. Just edit the symbol in the sheet FinalData in column 1 and row 1 and refresh the queries. (currently HOG is the symbol loaded, type GOOG, MSFT etc.). To refresh the queries, go to Data>> show queries.

Link to file

Upvotes: 1

Views: 1932

Answers (1)

ashleedawg
ashleedawg

Reputation: 21639

When stuck, start over… (especially since you said the site you're scraping the data from has changed.)

I'm using Access 2016. If you're using a different version the steps might vary slightly.

  1. On the Data tab, click From Web (in the Get & Transform Data group)

      pic1

  2. Paste in your source URL, ie: https://finance.yahoo.com/quote/msft/balance-sheet?p=msft

      pic2

  3. Click OK and wait for the Navigator to load.

     pic3

  4. Click the table you want (2, I think) and then click Load

       pic4

The data will load onto a new worksheet.

If desired, you can add parameters to the query (the same way you apparently did before).

Upvotes: 2

Related Questions