Pulling YQL data into Excel

yql logo

I hope everyone had a nice Thanksgiving!

A little while ago I posted An Introduction to YQL for VBA.

Rob asks for examples of pulling YQL results into Excel. So let's do that.

The simplest method uses Excel's XML import feature. You'll need Excel 2003 or higher to use this. But the code is very short and doesn't require parsing the XML.

I took an empty workbook and placed the following code in a standard module. I named cell B1 "QueryCell" and set up a command button to run the code.

Const baseURL As String = &quot;http://query.yahooapis.com/v1/public/yql?format=xml&amp;q=&quot;
Const destinationCell As String = &quot;A10&quot;

Sub TestYQL()

Dim query As String

  ' delete existing XML map, if any
  If ActiveWorkbook.XmlMaps.Count &gt; 0 Then
    ActiveWorkbook.XmlMaps.Item(1).Delete
  End If

  ' clear out range
  Range(destinationCell).CurrentRegion.Cells.Clear

  query = Range(&quot;QueryCell&quot;).Value

  ' hide any XML error alerts
    Application.DisplayAlerts = False

    ' import from YQL
    ActiveWorkbook.XmlImport URL:= _
        baseURL &amp; URLEncode(query), ImportMap:=Nothing, _
        overwrite:=True, Destination:=Range(destinationCell)
    ActiveSheet.Cells.WrapText = False
End Sub

Click here to grab the URLEncode function.

In my first post about YQL, I used the following queries:

select * from answers.search where query="vba"
select * from flickr.photos.search where text="chart"
select * from weather.forecast where location=11103
select * from rss where url='http://www.dailydoseofexcel.com/feed'
select title,url,abstract from search.web where query="vba"

Put any of these in cell B1 and run TestYQL (or click the button) to see the result in Excel. Or you can just download the example workbooks and do it there!

Download YQL sample workbook for Excel 2003
Download YQL sample workbook for Excel 2007

Filed Under: Excel

Tagged With: XML, YQL

11 Comments on Pulling YQL data into Excel:

Jon writes:
November 30, 2010 at 12:33 pm
I was wondering do you have to pay for these services, e.g., if I wanted to download historical stock information from yahoo and then put this in an excel application and sell it to users would yahoo (or whatever company) require payment for this service?
- JP writes:
  November 30, 2010 at 6:05 pm
  The short answer is, you can't do that.
  You do not have to pay for YQL, but you may need an account or authentication to use some services. You may use YQL in commercial applications.
  Don't hold me to that — Yahoo can change their terms at any time.
  See the YQL Developer Page for more information, and YQL Terms of Service as well.
  That being said, I don't see any way to pull stock info from Yahoo using YQL. They have Finance RSS feeds (see http://developer.yahoo.com/finance/) but you cannot use Yahoo Finance data in a commercial app. That, combined with available free options (this comes to mind: SMF AddIn) would seem to make it hard for a commercial app to be viable.
  - Jeff writes:
    December 24, 2010 at 3:25 pm
    datatables.org has a list of open community datatables. There is a link to pull all of the available tables into YQL console for testing.
    For Yahoo finance, this seems to work:
    select * from yahoo.finance.historicaldata where symbol = "YHOO" and startDate = "2009-09-11" and endDate = "2010-03-10"
    For republishing the data as a paid service that would be a violation of Yahoo's terms of service.
JayC writes:
January 5, 2011 at 5:57 pm
Hi JP,
I've tried your workbook for pulling YQL data into excel, but it seems to only work on a select number of commands– not a single query related to Yahoo fantasy sports seemingly will work, even the ones pertaining to just public data (i.e. select * from fantasysports.players where player_key='249.p.4389'). I'm new to VBA, so I don't quite know how to fix the XML Parse runtime error -2147217376 (80041020) that occurs in these queries. Any advice/help?
Thanks!
- JP writes:
  January 6, 2011 at 4:56 pm
  The YQL fantasy sports table requires authentication. Try logging into Yahoo first. Here's the XML response that needs to be accounted for:
  [cc lang='xml']< ?xml version="1.0" encoding="UTF-8"?>
  
  false
  Authentication Error. The table fantasysports.players requires a higher security level than is provided, you provided ANY but at least USER is expected
  [/vb]
  - JayC writes:
    January 10, 2011 at 2:47 pm
    Ugh, I guess I'll have to figure out Oauth in VBA then. Thanks for your help!
  - Will writes:
    March 14, 2011 at 2:05 pm
    Would it be possible to go into a bit more detail on the authentication piece? I, like JayC, would love to be able to pull raw fantasysports data into Excel and play with it. I have myself an OAuth key and all that jazz… I just don't know what to do with it yet!
  - Raph writes:
    May 3, 2011 at 2:31 pm
    Like JayC and Will, I've been searching for some info on oAuth and vba. It has been a fruitless exercise. Has anyone figured this out?
Rob writes:
April 25, 2011 at 8:06 pm
JP – You are the man. I couldn't run the 2007 one (was getting some errors), but used the 2003, then converted to 2007 and it worked. Weird.
Love the code!
Rob writes:
April 25, 2011 at 8:27 pm
Actually JP, I noticed that none of the Yahoo queries are working, but everything else seems to work. For example, the following query does not work for me:
select * from yahoo.finance.historicaldata where symbol = "YHOO" and startDate = "2009-09-11" and endDate = "2010-03-10"
I get a runtime error '-2147217376 (80041020)'
Any clue how to fix this?
Dean Hill writes:
September 23, 2011 at 10:13 pm
I was also getting the '-2147217376 (80041020)' error. After some research, I found that the error is caused by an XML file that Excel can't parse.
I took the default query included with the Excel workbook (select title,url,abstract from search.web where query="vba") and put it in the YQL Console:
http://developer.yahoo.com/yql/console
It turns out the search.web table is no longer valid. I think it has been renamed or something.