Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Yahoo Finance Patch preventing me retrieving stock data

0

Hi guys,

I am wondering how i can get the data in chart from financials -> income statement.

My actual code is not able to get it and I dont know why :

Sub Financials_Income_statement()

ticker = Sheets(1).Cells(1, 1)

qurl = "https://finance.yahoo.com/quote/" & ticker & "/balance-sheet?p=" & ticker & ""

Sheets(3).Select

Sheets(3).Cells.Clear

With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets(3).Range("A1"))

    .BackgroundQuery = True

    .Refresh BackgroundQuery:=False

End With

End Sub

Thanks,

JF

excelmacros-vbachartsinternet-web
MS Office Version: Excel 2010
posted by: StockHunter (rep: 2) on Sat Sep 28, 2019 at 6:07 pm
Answer
Discuss

Discussion

I'm thinking that you should use the method that get's data based on specific tags instead of entire tables. I'm pretty sure this link covers it Excel VBA Web Import
don (rep: 1989) Sep 30, '19 at 5:11 am
Add to Discussion

Answers

0
Selected Answer

I don't know if the code below does what you want but it does download data. I tested for Ticker = "AAPL"

Option Explicit

Sub Financials_Income_statement()

    Dim Ticker As String
    Dim Qurl As String

    Ticker = Sheets(1).Cells(1, 1).Value
    'Ticker = "AAPL"

    Qurl = "https://finance.yahoo.com/quote/" & Ticker & "/balance-sheet?p=" & Ticker & ""

    With Worksheets(3)
        .Cells.Clear
        With .QueryTables.Add(Connection:="URL;" & Qurl, Destination:=.Range("A1"))
            .BackgroundQuery = True
            .Refresh BackgroundQuery:=False
        End With
    End With
End Sub
posted by: Variatus (rep: 4889) on Sat Sep 28, 2019 at 10:40 pm
Discuss

Discussion

Hi Variatus,

Thanks for the answer, I know I am pulling some data. I was wondering how I could pull the hole income statement chart. This code was doing it before yahoo's update
StockHunter (rep: 2) Sep 29, '19 at 6:36 pm
And if you run this code on these 3 url you will get the same data (ex facebook ticker) :
https://finance.yahoo.com/quote/FB/financials?p=FB
https://finance.yahoo.com/quote/FB/balance-sheet?p=FB
https://finance.yahoo.com/quote/FB/cash-flow?p=FB

I would like to get the data from the chart
StockHunter (rep: 2) Sep 29, '19 at 7:05 pm
Sorry to disappoint you but this is a task, not a question. To be truthful, I looked at your question for what I might learn from it, meaning I'm not an expert in the field of scraping. I regret, my interest doesn't extend to wanting to take on a programming task. Good luck!
Variatus (rep: 4889) Sep 29, '19 at 9:13 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login