Fetching Data From the Web

Resolver Hacks contains information and code for doing fun and useful things with Resolver One. This site is created and maintained by Michael Foord, not by Resolver Systems. Please read the disclaimer before using any of the code on this site.

Follow Michael on:

Fetching Data From the Web

Including data from the internet in a spreadsheet

Contents

Introduction
Fetching Data from the Internet
Making the Query
From Response to Data
Creating the Chart

Introduction

One of the ways to illustrate the power of Resolver One is to create spreadsheets that include data from several different sources. Whether your data comes from financial data-stream, databases, web services or external files, pulling it into Resolver usually boils down to adding the right chunk of user code.

Download the "From the Web" Spreadsheet (zipfile 120Kb)

This example fetches historical exhange rates from a website, called Oanda.com, and creates a chart from the data.

The chart is generated using ZedGraph [1], and so as well as an example of fetching data from the web it is also another example of using ZedGraph.

Warning

The code here relies on 'screen scraping' the results of querying the Oanda website. This is not reliable, becuase it relies on the specific format that Oanda use for making queries and returning a response. This is only a 'proof-of-concept', showing how easy integrating data from across the internet is with Resolver.

In the meantime, if you know of a generally available web service that I could use to illustrate the concept then do get in touch!

This example has been adapted from something originally created by Andrzej Krzywda, another Resolver developer.

Fetching Data from the Internet

As you know, Resolver is 'scripted' with IronPython code. We can easily fetch a web-page using a couple of .NET classes.

To get the information, we mustmake a request to the Oanda website - filling a few parameters specifying exactly what it is that we want to know. There is a handy entry in the IronPython Cookbook which tells us how to do this from IronPython.

The FetchURIWithParameters, takes the URI we want, plus the parameters to send, and returns us the result as a string:

def FetchURIWithParameters(uri, parameters=None):
    request = WebRequest.Create(uri)
    if parameters is not None:
        request.ContentType = "application/x-www-form-urlencoded"
        request.Method = "POST"
        bytes = Encoding.ASCII.GetBytes(parameters)
        request.ContentLength = bytes.Length
        reqStream = request.GetRequestStream()
        reqStream.Write(bytes, 0, bytes.Length)
        reqStream.Close()

    response = request.GetResponse()
    result = StreamReader(response.GetResponseStream()).ReadToEnd()
    return result

So we need to pass the right parameters to FetchURIWithParameters and pull the data out of the response (which will be sent as a comma separated list between <PRE> ... </PRE> tags). This is the job of another function:

URI = 'http://www.oanda.com/convert/fxhistory'
PARAMETERS = "date1=%s&date=%s&lang=en&result=1&format=CSV&date_fmt=normal&exch=%s&expr=%s"

def GetRealTimeData(date1, date2, exch1, exch2):
     parameters = PARAMETERS % (date1, date2, exch1, exch2)
     fullURI = URI + "/" + parameters
     print 'Starting Reguest for:', fullURI
     start = DateTime.Now
     result = FetchURIWithParameters(URI, parameters)
     print 'Finished. Took %s seconds' % ((DateTime.Now - start).TotalMilliseconds / 1000.0)

     # scrape the results from the returned web page
     indexOfOpenPRE = result.find('<PRE>')
     indexOfClosePRE = result.find('</PRE>')
     return result[indexOfOpenPRE+5:indexOfClosePRE], fullURI

(Both FetchURIWithParameters and GetRealTimeData are both included in the module FromTheWeb.py which comes with this example spreadsheet.)

Making the Query

The required parameters are two exchange rates to compare and two dates (a start date and a finish date).

These are set in cells in the first worksheet. and are pulled out in the 'Pre-formulae user code'.

Note

This code can't go in the 'Pre-constants user code' - because values entered by the user aren't available until they have been set on the workbook by the 'Constants and Formatting' generated code.

The user code also does a basic check that the first two values contain valid dates. If they don't it sets an error on the cell and raises an exception:

sheet = workbook["Query"]

date1 = sheet.B2
date2  = sheet.B3
exch1 = sheet.B4
exch2 = sheet.B5

if not isinstance(date1, Date):
     sheet.Cells.B2.Error = Exception('"%s" is not a date' % date1)
     raise sheet.Cells.B2.Error
if not isinstance(date2, Date):
     sheet.Cells.B3.Error = Exception('"%s" is not a date' % date2)
     raise sheet.Cells.B3.Error

The next step is to fetch the data by calling GetRealTimeData:

currValues, fullURI = FromTheWeb.GetRealTimeData(date1, date2, exch1, exch2)
sheet.B6 = fullURI

GetRealTimeData returns the exchange rate data (as a single string) and the full URI from which the request was made (including the parameters).

If you at the code in the previous section, you will see a couple of print statements. GetRealTimeData times how long the request takes (which depends on your internet connection and the mood of the Oanda site). The print statements appear in the output view [2]:

Resolver tells you how long the query took

From Response to Data

So now that we've got what we asked for (admittedly as a single long string), what now? We need to turn the respons from Oanda into a form of data that we can use.

The response from Oanda is a single long string in the following format:

01.01.2005,1.91870
02.01.2005,1.91960
03.01.2005,1.91960
04.01.2005,1.90460
05.01.2005,1.88160
06.01.2005,1.88350
07.01.2005,1.87520
08.01.2005,1.8690
09.01.2005,1.87130
...

From this we want a table of data containing the matching dates with the exchange rate. First the data is split into lines, and each line split at the comma - to give us the data-value pairs. These pairs are then put into a temporary worksheet called "Currency Data", so that we can get at it easily.

The raw exchange rate data from Oanda.com

dataWorksheet = workbook.AddWorksheet("Currency Data")
dataWorksheet.ShowGrid = False

data = [entry.split(',') for entry in currValues.splitlines() if len(entry.split(',')) == 2]

cellRange = CellRangeFromDimensions(dataWorksheet.Cells.A1, 2, len(data) + 1)

# Formatting cell range skipped...

for index, (date, rate) in enumerate(data):
cellRange['Date', index + 2] = Date(date.replace('.', '/'))
cellRange['Rate', index + 2] = float(rate)

Creating the Chart

Most of the code for creating the chart is almost identical to the code shown in Charting with ZedGraph. This chart is slightly different though, the X-axis is a date axis.

This is achieved by configuring the XAxis Property of the pane (which is a subclass of Axis):

# Set the X-Axis to be a date axis
pane.XAxis.Type = AxisType.Date
pane.XAxis.Scale.Format = "dd-MMM-yy"
pane.XAxis.Scale.MinorUnit = DateUnit.Month
pane.XAxis.Scale.MinorStep = 1.0

line1 = PointPairList()
for index in range(2, cellRange.MaxRow + 1):
     date = cellRange['Date', index]
     rate = cellRange['Rate', index]
     day, month, year = date.DateTime.Day, date.DateTime.Month, date.DateTime.Year
     line1.Add(XDate(year, month, day).XLDate, rate)

firstDate = cellRange['Date', 2]
day, month, year = firstDate.DateTime.Day, firstDate.DateTime.Month, firstDate.DateTime.Year
pane.XAxis.Scale.Min = XDate(year, month, day).XLDate

lastDate = cellRange['Date', cellRange.MaxRow]
day, month, year = lastDate.DateTime.Day, lastDate.DateTime.Month, lastDate.DateTime.Year
pane.XAxis.Scale.Max = XDate(year, month, day).XLDate

pane.AddCurve("Exchange Rate", line1, Color.Red, SymbolType.None)

Important things to note:

The dates are put into the curve as numbers. We construct an XDate from the from the year, month and day and then out it into the point list using the XLDate property: line1.Add(XDate(year, month, day).XLDate, rate)
We set the span of the scale by setting the minimum and maximum from the first and last dates:

pane.XAxis.Scale.Min = XDate(year, month, day).XLDate

pane.XAxis.Scale.Max = XDate(year, month, day).XLDate
Because we have lots of data points, we don't want a symbol marking each point on the line. We use SymbolType.None in the call to AddCurve.
We set the scale of the XAxis to show months, with the major steps being every three months:

pane.XAxis.Scale.MajorUnit = DateUnit.Month

pane.XAxis.Scale.MajorStep = 3.0

[1]	The zipfile includes the `ZedGraph.dll` needed for producing the chart. See Charting with ZedGraph for more information on the subject.

[2]	The output view is at the very bottom of the Resolver interface. If it isn't visible, move your cursor down until the mouse pointer changes to the 'drag' symbol - and drag up!

Return to Top

Last edited Sun Mar 8 00:37:37 2009.