An Introduction to YQL for VBA
July 30, 2010 • JP • No Comments • Rate This Article
• Links to this article
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:
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.
' run any query
Dim xml As Object ' MSXML2.XMLHTTP
Set xml = CreateObject("MSXML2.XMLHTTP")
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.
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.
Quote = Chr(34) & CStr(str) & Chr(34)
End Function
Using YQL we can even grab RSS feeds and do web searches, for example
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.
VBA ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Q2 2010 Stats
Next Post: Add Data Validation to any worksheet using VBA
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].