Dynamically Generating Excel Files from Web Data using SpreadScript

A sample application written in Tcl using SpreadScript to dynamically generate a native Excel file from Yahoo Finance quote data. This particular script downloads the Yahoo quote page and inserts a link that will dynamically create the Excel file.

#!/bin/sh
# the next line restarts using tclsh \
exec /usr/local/bin/tclsh "$0" "$@"

set QuoteServer         finance.yahoo.com
set QuoteCGI            q


proc do_http_get { theServer theFile theParams } {
  set len [string length $theParams]
  set websock [socket $theServer 80]
  puts $websock "GET /$theFile?$theParams HTTP/1.0\n"
  flush $websock
  set result [read $websock]
  close $websock
  return $result
  }

set page [do_http_get $QuoteServer $QuoteCGI $env(QUERY_STRING)]

# remove the first line, which will be supplied by the HTTP server

regexp "\[^\n\]*\n(.*)" $page match page

# make the Yahoo Finance page the base URL

regsub {<html>} $page {<html><BASE HREF="http://finance.yahoo.com/">} page

# add our informational header

regsub {<body>} $page {<body>\
   <b>This live page from Yahoo Finance was modified by\
   <a href="http://www.greytrout.com/">GreyTrout Software </a>\
   to demonstrate the power of\
   <a href="http://www.greytrout.com/spreadscript.html">SpreadScript</a>.</b>\
   <p>\
   Select "Standard CSV" below\
   to download a comma separated values (CSV) file as provided by Yahoo.\
   <br>Select <b><font color=red>"Native Excel File"</font></b> below\
   to download a native Excel XLS spreadsheet as generated using\
   <a href="http://www.greytrout.com/spreadscript.html">SpreadScript</a>\
   using the Yahoo data.<p><hr size=4><p>} page

# add a second "Download Spreadsheet" link and mark them both visually
# the first link goes to Yahoo and gets a CSV file
# the second goes to GreyTrout and gets a native Excel file

regsub {<a href="/d/quotes.csv\?(s=.*\.csv)">} \
  $page \
  {<b>Standard Yahoo CSV \&gt;</b> \
   \0Download Spreadsheet</a></td></tr> \
   <tr><td></td><td align=right><font face=arial> \
   <font color=red><b>Native Excel File \&gt;</b></font> \
   <a href="http://www.greytrout.com/cgi-bin/quotes.xls?\1">} \
  page

puts $page