[PYTHON] Save TOPIX time series in pickle, csv, Excel format

What to do

Since I did the previous post to display the TOPIX time series, Save it in various formats for easy processing later.

Code to parse Yahoo Finance html

Same as before. However, I put in a function call called savedataAs somehow (filename, data).

#!/usr/bin/env python
#-*- coding: utf-8 -*-
import pylab
import urllib2
import lxml
import lxml.html
import re

dateFr = {"year": 2000, "month":1, "day":1}
dateTo = {"year": 2013, "month":11, "day": 1}

data = []
for page in range(1, 30):
    print page
    url = "http://info.finance.yahoo.co.jp/history/?code=998405.T&sy=%d&sm=%d&sd=%d&ey=%d&em=%d&ed=%d&tm=d&p=%d"
    url = url % (dateFr["year"], dateFr["month"], dateFr["day"], dateTo["year"], dateFr["month"], dateFr["day"], page)

    html = urllib2.urlopen(url).read()
    root = lxml.html.fromstring(html)
    table = root.xpath(' //*[contains(concat(" ",normalize-space(@class)," "), " boardFin ")]')[0]

    for tr in table.xpath("descendant::tr"):
        tmp = [td.text for td in tr.xpath("descendant::td")]
        if len(tmp) != 5:
            continue
        begin = float(tmp[1].replace(",", ""))
        high  = float(tmp[2].replace(",", ""))
        low   = float(tmp[3].replace(",", ""))
        end   = float(tmp[4].replace(",", ""))
        data.append([low, high, low, high])
savedataAsPickle('topix.pickle', data)
savedataAsCSV('topix.csv', data)
savedataAsExcel('topix.xlsx', data)

Code to save

pickle Format in the standard python library

def savedata(filename, data):
    pickle.dump(data, open(filename, "w"))

csv Abbreviation for Comma Separated Values. Text format.

def savedata(filename, data):
    import csv
    csvobj = csv.writer(open(filename, "w"))
    csvobj.writerows(data)

Excel format

Save in a format that can be read by Excel

def savedata(filename, data):
    import openpyxl.workbook
    import openpyxl.cell
 
    wb = openpyxl.workbook.Workbook()
    ws = wb.worksheets[0]
    ws.title = "TOPIX"
    for idat, dat in enumerate(data):
        col = openpyxl.cell.get_column_letter(idat + 1) # from 1, 2, ... to A, B, ...
        for irow, d in enumerate(dat):
            ws.cell('%s%s'%(col, irow + 1)).value = d
    wb.save(filename)