I am excited to share an open source of interesting data.  The data set discussed today can be accessed using Yahoo Query Language (YQL) and the XML import techniques I have been talking about recently.  The first step is to create a url that will capture the data.  This can be done on the YQL console.

http://developer.yahoo.com/yql/console/

On the right of the page you will see a frame with the heading DATA TABLES.  Underneath this heading there is a link for Show Community Tables.  Click on this link.  Now you can either use the search feature or navigate to the data of interest.  For the example today navigate down to the yahoo tables.  Expand the yahoo tree and select yahoo.finance.quote

A query will now be displayed in Your YQL Statement for the yahoo finance data.  The results of the query will be displayed below that in XML format.  Below the results there will be THE REST QUERY.  Modify the YQL statement to include the stock symbol you are interested.  In this example we will query data for Yahoo.  Copy the url under THE REST QUERY.

The following code can be used to import the XML.  Create a blank macro in STATISTICA.  Paste the code below into the macro window.  Add a reference to Microsoft XML Services version 3.0.  Paste the url in the constant after the equals sign.  You will need to add quotations around the url.  I have done this already for you in the example code.

Code to import Yahoo stock quote in XML format:

Const Yahoo = “http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22YHOO%22)&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys”
Sub Main
Dim objXML As DOMDocument
Set objXML = CreateObject(“MSXML2.DOMDocument”)
objXML.async = False
objXML.validateOnParse = False
objXML.resolveExternals = False
Dim objXMLHTTP As MSXML2.XMLHTTP
Set objXMLHTTP = CreateObject(“MSXML2.XMLHTTP”)
objXMLHTTP.Open “Get”, Yahoo,False,,
objXMLHTTP.setRequestHeader “Content-type”, “text/html”
objXMLHTTP.Send
sAns = objXMLHTTP.responseText
bAns = objXML.loadXML(sAns) ‘ Ensure you have a valid XML response
If Not bAns Then GoTo EmptySetTrap
objXML.Save(“C:\Temp\Yahoo.xml”)
EmptySetTrap:
End Sub

 

Run the code and look at the resulting XML with a tool such as XML Notepad.  Here is a screenshot of some of the results I obtained using the code:

XMLNotepad_yahoo quote

I was interested in the values for AskRealtime and BidRealtime.  The following code picks off those values and writes them to a STATISTICA spreadsheet.  Notice what XPath I used to pick off the values of interest.  Also note that you will need a STATISTICA spreadsheet open that has one case and three variables.  The spreadsheet should look like the following screen shot:

StockQuoteSpreadsheet

 

Code to write XML results to STATISTICA spreadsheet:

‘#Language “WWB-COM”
Option Base 1
Const Yahoo = “http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20(%22YHOO%22)&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys”
Sub Main
Set s = ActiveDataSet
Dim objXML As DOMDocument
Set objXML = CreateObject(“MSXML2.DOMDocument”)
objXML.async = False
objXML.validateOnParse = False ‘ necessary because MSXML doesn’t seem to work very well when an external DTD is referred to
objXML.resolveExternals = False
Dim objXMLHTTP As MSXML2.XMLHTTP
Set objXMLHTTP = CreateObject(“MSXML2.XMLHTTP”)
objXMLHTTP.Open “Get”, Yahoo,False,,
objXMLHTTP.setRequestHeader “Content-type”, “text/html”
objXMLHTTP.Send
sAns = objXMLHTTP.responseText
bAns = objXML.loadXML(sAns) ‘ Ensure you have a valid XML response
If Not bAns Then GoTo EmptySetTrap
Set oRoot = objXML.documentElement
Set oItemNodes = oRoot.selectNodes(“//quote”)
Dim oNode As IXMLDOMNode
For Each oNode In oItemNodes
Set sTime = Now()
Set sAskNode = oNode.selectSingleNode(“./AskRealtime”)
If Not sAskNode Is Nothing Then sAsk = sAskNode.Text Else sAsk = “”
Set sBidNode = oNode.selectSingleNode(“./BidRealtime”)
If Not sBidNode Is Nothing Then sBid = sBidNode.Text Else sBid = “”
s.AddCases(s.NumberOfCases, 1)
Set cell = s.NumberOfCases
s.SetData(cell, 1, sTime)
s.SetData(cell, 2, sAsk)
s.SetData(cell, 3, sBid)
Set sTime = Nothing
Set sAsk = Nothing
Set sBid = Nothing
Next
EmptySetTrap:
End Sub

 

Run the code and see what results are written to the spreadsheet.  Explore some of the other data available on YQL using the techniques discussed today.  If you have questions please feel free to leave a comment below the blog post.  Next time I will share some information on on how to automate the collection of the XML shown today using a Windows Scheduled Task.  Have some fun in the meantime. 😉