How to download IE csv direct to excel

[jonathanwang003]

Hi All,

My macro below worked until today. I have a URL that used to open and paste right into excel. Not sure why this broke. Is there another method I could use to download the qurl into Excel? When I paste the qurl into IE, it opens up the CSV so it appears the qurl is still valid.

Sub TestDownload()
Dim qurl as string
qurl = "https://query1.finance.yahoo.com/v7/finance/download/^GSPC?period1=1544400000&period2=1583366400&interval=1d&events=history&crumb=nfhoFBTOnvj"

With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Range("A1")
            .PostText = True
            .SaveData = True
            .Refresh BackgroundQuery:=False
    End With
End Sub

 

[sandy666]

{"finance":{"result":null,"error":{"code":"Unauthorized","description":"Invalid Crumb"}}}
maybe you need authorization or key

 

[jonathanwang003]

Interesting. Okay I just went straight to the Yahoo website to pull a fresh URL.

https://query1.finance.yahoo.com/v7/finance/download/^GSPC?period1=1544400000&period2=1583366400&interval=1d&events=history&crumb=nfhoFBTOnvj

When we paste this into IE, it automatically prompts us to the csv download. How would I modify VBA to bring that straight into a sheet?

 

[sandy666]

I got exactly the same error
but you can try via Power Query (Get&Transform)
Ctrl, A, PN, O, W

 

[jonathanwang003]

Hi All,

So I've been testing the validity of the URL and researching power query. Not sure how to incorporate power query into this but I did confirm the URL works... so it seems like the crumb is unique to each user. What I did confirm is that how the URL is constructed is consistent so I can still use the URL above to test.

I suspect yahoo must have changed something on their end. The URL construct is from the following URL, right clicking on the "Download Data" and selecting "Copy Link Location"

Microsoft Corporation (MSFT) Stock Historical Prices & Data - Yahoo Finance

Discover historical prices for MSFT stock on Yahoo Finance. View daily, weekly or monthly format back to when Microsoft Corporation stock was issued.

finance.yahoo.com

If you download this attachment, it should generate a unique "crumb" that would work for each specific user. Is it possible to use VBA to pull this csv into excel?

 

[sandy666]

you can use url you posted and you will get the same (I think) data as in csv from Table2

 

[jonathanwang003]

Sandy, were you able to pull that screenshot from the power query method? This is what I got when I tried. Not sure how to overcome this.

 

[sandy666]

https://finance.yahoo.com/quote/MSFT/history?p=MSFT
and method from post#4
then select Table2
Edit

there should be Alt not Ctrl, sorry

like: Alt A, PN, O, W

 

[jonathanwang003]

Got it. Yes I am able to get that url to work too. The challenge is that I modify the date range (ie 500 days) which won't show on screen, but is in the "download data" link.

I was previously able to use the link to the download to import to csv, but now come across an error. It's strange that I can construct the url for that link location but it just doesn't seem to work with VBA trying to retrieve it.

 

[sandy666]

you can still download csv but you cannot use this link directly to excel (Authentication failure)
ask yahoo why

I'm against vba so I can't help , sorry
have a nice day