# -*- coding: UTF-8 -*- """Usage: update_quote.py [log_filename] This script will scan your Excel spreadsheet for stock symbols. It will fetch the stock quote from the web and update the data in the spreadsheet. The spreadsheet should have a table on active sheet. The first row should be the header names and the first column should have the stock symbols. Blank cells is used to delimit the range of the table. (Last updated 2008-06-22 11:02:56) """ import csv import datetime import sys from pprint import pprint from StringIO import StringIO import urllib from win32com.client import Dispatch # Yahoo stock quote URL URL = "http://download.finance.yahoo.com/d/quotes.csv?s=%s&f=sl1d1t1c1ohgv&e=.csv" # header of CSV, used to match header name in spreadsheet QUOTE_HEADER = [ "Symbol", "Price", "Last Trade", "Last Time", "Change", "Open", "Day High", "Day Low", "Volume", ] LOG_DATA_FETCHED = True LOG_FILENAME = 'quotes%s.csv' def log(msg): print(msg.encode('utf-8')) def scan_spreadsheet(xl): symbols = [] for row in range(2,999): value = xl.Cells(row,1).value if not value: break symbols.append(value) header_map = {} for col in range(1,99): value = xl.Cells(1,col).value if not value: break header_map[value.lower()] = col # quote (0 based) col number -> spreadsheet (1 based) col quote_col_map = [header_map.get(header.lower(), None) for header in QUOTE_HEADER] return symbols, quote_col_map def fetch_quote(symbols, timestamp, cached_file=None): url = URL % '+'.join(symbols) if not cached_file: # fetch log('Fetching %s' % url) fp = urllib.urlopen(url) try: data = fp.read() finally: fp.close() # log result if LOG_DATA_FETCHED: log_filename = LOG_FILENAME % timestamp.replace(':','-') out = open(log_filename, 'wb') try: log('Fetched %s bytes logged in %s' % (len(data), log_filename)) out.write(data) finally: out.close() else: data = open(cached_file,'rb').read() return StringIO(data) def update_spreadsheet(xl, fp , header_map): # don't want to update symbol (col 0) header_map[0] = None update_count = 0 reader = csv.reader(fp) for y_0, row in enumerate(reader): row_num = y_0+2 # take at most number of values defined in QUOTE_HEADER row = row[:len(QUOTE_HEADER)] # make sure the quoted symbol match what we have requested quote_symbol = unicode(row[0],'utf-8') excel_symbol = xl.Cells(row_num,1).value if excel_symbol == quote_symbol : log('Updating %s'% quote_symbol) else: log('Wrong Symbol %s != %s' % (excel_symbol , quote_symbol)) continue # update data on that row for x_0, value in enumerate(row): col_num = header_map[x_0] if col_num: xl.Cells(row_num,col_num).value = value update_count += 1 log('Updated %s rows'% update_count) def main(argv): cache_pathname = argv[1] if len(argv) > 1 else None now = datetime.datetime.now() timestamp = now.isoformat()[:19] log('\nDate: %s'% timestamp) # Open Excel xl = Dispatch("Excel.Application") xl.Visible = 1 try: pathname = xl.ActiveWorkbook.Fullname except: log('Error: No active excel workbook.') sys.exit(-1) filename = pathname.rpartition('\\')[2] # scan for symbols to lookup symbols, quote_col_map = scan_spreadsheet(xl) log('Updating spreadsheet %s #symbol: %s'% (filename, len(symbols))) if symbols: data_fp = fetch_quote(symbols, timestamp, cache_pathname) update_spreadsheet(xl, data_fp, quote_col_map) if __name__ =='__main__': if '--help' in sys.argv: print __doc__ sys.exit() main(sys.argv)