Download stock data from Yahoo Finance

Question

This Python 3.4 script downloads stock data and puts it into an Excel file.

## Imports/Initiation

# Put a "#" in front of the one you don't want to use.

signs = 'a abc abt ace acn act adbe adi aet afl agn agu aig all alxn amgn amt amzn apa apc apd axp azo ba bac bam bax bbby bdx ben bfb bhi bhp biib bmy bp brk-b bud bwa bxp c cah cam cat cbs celg cern chkp ci cmcsa cme cmg cmi cnq cof cog coh cost cov cs csco csx ctsh ctxs cvs cvx dal dd deo dfs dgx dhr dis dlph dov dtv dva dvn ebay ecl el emc emn enb eog epd esrx esv etn f fb fdx fis flr gd ge gild gis glw gm gps gsk gww hal hd hes hmc hog hon hot hst hsy hum ice intc ip isrg jci jnj jpm kmp kmx ko kr krft kss l lly low lvs lyb m ma mar mat mcd mck mdlz mdt met mfc mhfi mmc mo mon mos mpc mrk mro mro ms msft mur myl nbl ne nem nke nlsn nov nsc nue nvs orcl orly oxy pcp pep pfe pg ph pm pnc pnr ppg pru psx px pxd qcom qqq regn rio rl rop rost rrc rsg sbux se shw sjm slb slm sndk spg stt stz su swk syk tck tel tjx tm tmo trow trv twc twx tyc ual unh unp ups utx v vfc viab vlo vno vz wag wdc wfc wfm wmb wmt wy wynn yhoo yum zmh'
#signs = ''  # Testing purposes
dates = [1429228800 , 1431648000, 1434672000, 1442534400]

import time
from subprocess import call
import os
from datetime import datetime

# This shows all modules that failed to install rather than just the first.
importErrors = []
try:
    import requests
except ImportError:
    importErrors.append('requests')
try:
    from lxml import html
except ImportError:
    importErrors.append('lxml')
try:
    import xlsxwriter
except ImportError:
    importErrors.append('xlsxwriter')
if importErrors != []:
    raise ImportError('Unable to import {}'.format(', '.join(importErrors)))

dt = datetime.fromtimestamp(time.time())
date = dt.strftime('%d-%m-%Y')

path = 'options_report_{}.xlsx'.format(date)

try:
    excel = xlsxwriter.Workbook(path)
except:
    sys.exit('Unable to open workbook. Please close it if it is open and try again.')

start = time.time()

try:
    test_web = requests.get('http://yahoo.com')
except:
#     raise ConnectionError('Unable to contact the Internet. Please check your connection and try again.')
    pass

## Download Data

signs = signs.upper().replace('  ', ' ').split(' ')
site = 'https://finance.yahoo.com/q/op?s={}&date={}'  # Call .format(sign, date)
left_col = "//div[@id='optionsCallsTable']//tbody/tr"
path_table = "//div[@id='optionsCallsTable']//tbody/tr[{}]/td/*//text()"
path_last = "//*[@id='yfs_l84_{}']//text()"  # Call .format(sign)


site_2 = 'https://finance.yahoo.com/q/in?s={}+Industry'  # .format(sign)
paths_info = ['//*[@id="yfi_rt_quote_summary"]/div[1]/div/h2/text()', '//tr[1]/td/a/text()', '//tr[2]/td/a/text()']
all_data = {}

for sign in signs:
    all_data[sign] = {}
    print('\n{:{}} ({:{}} of {})'.format(
            sign, len(max(signs, key=len)) + 1, signs.index(sign) + 1,
            len(str(len(signs))), len(signs)
        ), end='')
    page = requests.get(site_2.format(sign))
    tree = html.fromstring(page.text)
    try:
        all_data[sign]['Info'] = [tree.xpath(path)[0] for path in paths_info]
    except IndexError:
        print(' Error: stock does not exist.', end='')
    else:
        for date in dates:
            all_data[sign][date] = []
            print('.', end='')
            page = requests.get(site.format(sign, date))
            tree = html.fromstring(page.text)
            left_data = tree.xpath(left_col)  # So we know how many rows there are
            exists = True
            for row_n in range(len(left_data)):
                temp_row = tree.xpath(path_table.format(row_n + 1))
                try:
                    temp_row.insert(0, tree.xpath(path_last.format(sign))[0])
                except IndexError as e:
                    exists = False
                if exists:
                    all_data[sign][date].append(temp_row)
            if not exists:
                print(' Stock does not exist.', end='')
                break

print()  # Allow printing of the last line

download_end = time.time()
print('Download completed in {:.2f} seconds (average {:.2f} seconds per stock)'.format(download_end - start, (download_end - start) / len(signs)))

## Format Data

formats = [
    'str', 'str', 'str', 'str', 'float',
    'str', 'str_f', 'str', 'float', 'float',
    'float', 'int', 'int', 'float', 'float_f',
    'int_f', 'int_f', 'int_f', 'float_f', 'percent_f',
    'percent_f', 'float_f', 'percent_f', 'percent_f', 'str_f'
]
headers = [
    'co_symbol', 'company', 'industry', 'sector', 'Last',
    'Option', 'exp_date', 'Call', 'Strike', 'Bid',
    'Ask', 'Open interest', 'Vol', 'Last', '3/24/2015',
    'days', '60000', ' $invested', '  $prem', ' prem%',
    'annPrem%', ' MaxRet', ' Max%', 'annMax%', '10%'
]

data = []

for sign in all_data:
    for date in all_data[sign]:
        if date != 'Info':
            for r in all_data[sign][date][:]:
                # human-readable date = hrd
                try:
                    hrd_lst = [r[2][-15:-9][x:x + 2] for x in range(0, 6, 2)]
                except IndexError as ie:
                    raise IndexError(ie.args, r) from ie
                hrd_str = '/'.join((hrd_lst[1], hrd_lst[2], hrd_lst[0]))
                try:
                    row = [sign, all_data[sign]['Info'][0], all_data[sign]['Info'][1], all_data[sign]['Info'][2], r[0],
                        r[2], hrd_str, 'C', r[1], r[4],
                        r[5], r[9], r[8], r[3], '=IF(J{n}<F{n},(J{n}-F{n})+K{n},K{n})',
                        '=H{n}-P$6', '=ROUND(R$6/((F{n}-0)*100),0)', '=100*R{n}*(F{n}-0)', '=100*P{n}*R{n}', '=T{n}/S{n}',
                        '=(365/Q{n})*U{n}', '=IF(J{n}>F{n},(100*R{n}*(J{n}-F{n}))+T{n},T{n})', '=W{n}/S{n}', '=(365/Q{n})*X{n}', '=IF((ABS(J{n}-F{n})/J{n})<Z$6,"NTM","")']
                except IndexError as ie:
                    raise IndexError(row) from ie
                data.append(row)

# Check that everything that's supposed to be the same length is.
if len(formats) != len(headers) or len(headers) != len(data[0]):
    raise Exception('The "formats" list, "headers" list, and rows in the data are not all the same length!')

for row in data:
    for i, cell in enumerate(row):
        if '_f' in formats[i]:
            row[i] = str(row[i])
        elif 'percent' in formats[i]:
            row[i] = float(row[i].replace('%', ''))
        else:
            try:
                row[i] = eval('{}(row[i])'.format(formats[i].replace('_f', '')))
            except ValueError:
                if '-' in row[i]:
                    row[i] = str(row[i])

## Output Data

write_start = time.time()

sheet = excel.add_worksheet()
r_offset, c_offset = 5, 1

pt = excel.add_format({'num_format': '0.00\%'})
ft = excel.add_format({'num_format': '0.00'})
it = excel.add_format({'num_format': '0'})
sr = excel.add_format({})
fa = excel.add_format({})

print('Writing data...', end='')

for i, header in enumerate(headers):
    sheet.write(r_offset, i + c_offset, header)

r_offset += 1

for r, row in enumerate(data):
    for c, cell in enumerate(row):
        if '_f' in formats[c]:
            sheet.write(r + r_offset, c + c_offset, cell.format(
                n=str(r + r_offset + 1)), eval(formats[c][0] + formats[c][-3]))
        else:
            sheet.write(r + r_offset, c + c_offset, cell, eval(formats[c][0] +
                                                               formats[c][-1]))

excel.close()

## Finish Up

end = time.time()
print(' Completed in {:.2f} seconds'.format(end - write_start))
print('Script completed in {:.2f} seconds'.format(end - start))

try:
    os.startfile(path)
except OSError:
    print('Unable to open Excel. The file is called {}.'.format(path.split('/')[-1]))

print('Press Enter to exit')
end = input()

First, the import section. How can I make this less... er... bulky? It works, but doesn't seem very Pythonic.

Next, the formatting section. This is quite convoluted. How can I slim it down?

And finally, the writing section. Is there a better way to prevent conflicts with the actual filetype names that are in the formats list than just having the first and last letters?

Answer 1

A few general comments:

• It's good that you're being proactive in trying to catch problems, but you could be losing valuable information. An Exception includes an error message and a traceback, both of which are useful for debugging. It's better to let those show up, so that you have all that information for debugging, than to wrap it with your own message that might be inaccurate or incorrect.
• Use better variable names! It makes your code easier to read if your variable names match their intended function.
• You should comment your code explaining why something is set up in a particular way: for example, the URL options in the finance.yahoo.com are meaningless to me because I've never used that API. You should explain roughly what these options are doing, at a high level at least, and what you expect to happen.

Here are some suggestions:

• Drop the try ... except ImportError blocks. Although it might be useful to see all the modules which failed to import, you could actually be losing information. If the module fails to import, it might give you some information in the exception message that's more useful than simply knowing it went wrong. Don't throw that away; just do the import the usual way.

  If the script crashes out at the first import, that's okay.

  Note also that rather than testing if importErrors != [], you could just do if importErrors, because an empty list is implicitly coerced to False.

• PEP 8, the Python style guide, has a few things to say about imports. In particular:

  Imports are always put at the top of the file, just after any module comments and docstrings, and before module globals and constants.

  You've inadvertently grouped the imports with your ImportError blocks. There's also a convention that module imports should be alphabetically ordered, which is a good habit to get into.

• Another common convention is that module constants are in UPPERCASE to make them easy to spot. You should do this with your signs and dates variables, and also make the names more descriptive – starting at the top of your file, I don't know what they're for.

• There's no need to go to the time module to get the current date; the datetime module can do that for you. This is how it works:

  from datetime import datetime
  
  today_date= datetime.now().strftime('%d-%m-%Y')
  spreadsheet_path = 'options_report_{date}.xlsx'.format(date=today_date)
  

  Note that I've also tried to give the variables more descriptive names.

• Next, opening the notebook. Again, by trying to wrap the error in try ... except, you could be losing information. Two reasons why:

  • Using bare except is a bad idea, because it can catch errors you didn't mean to (such as SystemExit and KeyboardInterrupt). It's better to catch the specific exception you meant to, and let others bubble up.
  • When you try to open the notebook, there could be all sorts of problems – the file might not exist, there might be a file lock, it could be the wrong format, etc. That information will show up in the Exception, but not if you mask it with your own message.

  Likewise, don't wrap the exception around requests.get('yahoo.com'). And don't save the result of that line – you don't do anything with it afterwards.

• When you split signs, you can simplify this line. If you don't specify a separator, split() will just use whitespace, and compress consecutive runs of whitespace into a single split. From the stdtypes docs:

  str.split([sep[, maxsplit]])

  If sep is not specified or is None, a different splitting algorithm is applied: runs of consecutive whitespace are regarded as a single separator, and the result will contain no empty strings at the start or end if the string has leading or trailing whitespace.

  So this line could be simply signs = signs.upper().split().

• Rather than assigning the values of all_dict as empty dicts for every new key, you might want to look at collections.defaultdict. This is a rather handy object that mimics a dictionary, but allows you to set a default value. So you'd set it up as follows:

  import collections
  
  all_data = collections.defaultdict(dict)
  

• You can simplify this block of code:

  try:
      temp_row.insert(0, tree.xpath(path_last.format(sign))[0])
  except IndexError as e:
      exists = False
  if exists:
      all_data[sign][date].append(temp_row)
  

  Rather than assigning to all_data after you've finished the try ... except block, do it within the try:

  try:
      temp_row.insert(0, tree.xpath(path_last.format(sign))[0])
      all_data[sign][date].append(temp_row)
  except IndexError as e:
      exists = False
  

  If you hit an Exception, you'll never run that line anyway, but now the code is a little simpler.