Welcome, guest | Sign In | My Account | Store | Cart

This script update the stock quote on your spreadsheet by fetching the latest quote from Yahoo!Finance web services. It uses the pywin32 library to update the cells on an Excel spreadsheet.

Python, 153 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
# -*- 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)

There are many free tools available to help people manage their stock portfolio. For example, you can use Yahoo! Finance to track your current holdings. For each stock it list many detail financial data as well as stock chart for various periods. Still, when you need to do more sophisticated analysis, there is nothing that beats the power of spreadsheet.

Fortunately it is easy to pull the data from the web tool to a spreadsheet. You can simple copy and paste the data into a spreadsheet to get a properly formatted table. For there you can add your custom formula or charts. The problem is unlike the web view, data downloaded on a spreadsheet is not updated with current market information. On the other hand if you download the again you will lose all custom formula you have added.

To get the best of both world, I have created a script to so that you can update your spreadsheet with the latest stock quote from the web. This is a simple Python script. It first looks for the stock symbols in your portfolio in your spreadsheet. It then fetches the (15 minute delayed) stock quote from a Yahoo web services. The data is a simply a CSV output. Finally it updates the cells in the spreadsheet with new data from Yahoo.

It is quite a challenge to find an ASPN category to submit this recipe. I think the "Image and scientific data processing" category is a fitting one. This category is for people who like to work with numbers.

Finally, you can download a sample spreadsheet and see some screenshot on my website - http://tungwaiyip.info/blog/2008/06/22/python_stock_quote.

3 comments

K. Killebrew 15 years, 9 months ago  # | flag

This is handy. Thanks!

Sim 12 years, 7 months ago  # | flag

I've just started hacking around in Python (and Ruby) , and this is way useful. I've already connected Excel to Yahoo Finance and Excel to Historical Forex Rates but I tend to all my number crunching and manipulation in VBA (and now Python)

Harrison Delfino 7 years, 10 months ago  # | flag

Well, using MarketXLS works for me. It's great. http://marketxls.com/stock-quotes-in-excel/