An Introduction to YQL for VBA

YQL logo

YQL is a flavor of SQL that allows you to query Yahoo's databases in a simple way. In Yahoo's words:

The Yahoo! Query Language is an expressive SQL-like language that lets you query, filter, and join data across Web services. With YQL, apps run faster with fewer lines of code and a smaller network footprint.

Yahoo! and other websites across the Internet make much of their structured data available to developers, primarily through Web services. To access and query these services, developers traditionally endure the pain of locating the right URLs and documentation to access and query each Web service.

With YQL, developers can access and shape data across the Internet through one simple language, eliminating the need to learn how to call different APIs.

If you are familiar with SQL or any kind of database programming, you should be comfortable using YQL to pull data into Excel or other Office apps.

YQL Query Function

Let's start with a simple function that can be used to run any YQL query. All queries start with the following base URL:

Const baseURL As String = "http://query.yahooapis.com/v1/public/yql?format=xml&q="

We specify the format as XML, then append the query to the end. You can also visit the YQL Developer Console to test out queries and see results immediately. The query must be URL-encoded — visit URLEncode to grab the function.

Function YQLQuery(query As String) As String
' run any query
Dim xml As Object ' MSXML2.XMLHTTP

  Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

  With xml
    .Open "GET", baseURL & URLEncode(query), False
    .send
  End With

  YQLQuery = xml.responseText

End Function

Note that this function simply dumps the results of the query into a String variable and returns it. No parsing or editing is done. Hey, it's just an introduction after all.

Sample usage

Here we'll call the function three times, passing a different query each time.

Sub TestYQL()

Dim result As String
Dim query As String

  ' create Yahoo Answers query
  query = "select * from answers.search where query=" & Quote("vba")
  result = YQLQuery(query)
  Debug.Print result

  ' query flickr
  query = "select * from flickr.photos.search where text=" & Quote("chart")
  result = YQLQuery(query)
  Debug.Print result

  ' query weather
  query = "select * from weather.forecast where location=11103"
  result = YQLQuery(query)
  Debug.Print result

End Sub

Quote Function

This function makes it a bit easier to nest double quotes. It is used in the sample procedure above so you'll want to include it in the same project.

Function Quote(str As Variant) As String
  Quote = Chr(34) & CStr(str) & Chr(34)
End Function

Using YQL we can even grab RSS feeds and do web searches, for example

Sub TestYQL()

Dim result As String
Dim query As String

  ' browse DDoE feed
  query = "select * from rss where url='http://www.dailydoseofexcel.com/feed'"
  result = YQLQuery(query)
  Debug.Print result

  ' search for "VBA" on Yahoo
  query = "select title,url,abstract from search.web where query=" & Quote("vba")
  result = YQLQuery(query)
  Debug.Print result

End Sub

Visit the YQL Developer Console to look at all the databases you can query and the syntax for each.