Writing Values From Python into Excel

Question

What I am trying to do is to get this line of code to work by putting a price from Yahoo Finance for a stock into a spreadsheet. I have asked quite a few people and I have researched the issue but I haven't gotten it to work.

This is the trouble line of code:

ws.cell(1,i+1).value = str.replace(price','price')

I get errors likes, "replace' requires a 'str' object but received a 'list' or 'int' object has no attributes everytime I modify it. Thanks for the help in advance.

from openpyxl import Workbook

import urllib
import re

from openpyxl.cell import get_column_letter

wb = Workbook()

dest_filename = r'empty_book.xlsx'

ws = wb.create_sheet()

ws.title = 'Stocks'
symbolslist = ["aapl","spy","goog","nflx"] 

i=0
while i<len(symbolslist):
    #counts each object as 1 in the list
    url = "http://finance.yahoo.com/q?s="+symbolslist[i]+"&q1=1"
    htmlfile = urllib.urlopen(url)
    htmltext = htmlfile.read()
    regex = '<span id="yfs_l84_'+symbolslist[i]+'">(.+?)</span>'
    pattern = re.compile(regex)
    price = re.findall(pattern,htmltext)
    print "The price of", symbolslist[i], " is ", price 
    ws.cell(1,i+1).value = str.replace(price','price')
    i+=1


wb.save(filename = dest_filename)

Importing the CSV module is better than using Excel for data analysis because it is open source friendly.

zinking · Accepted Answer · 2013-10-06 03:00:47Z

2

I don't even think you need the EXCEL API to do this, you are merely work offline, so write the finance stock price using CSV format, and then open the csv file using EXCEL. like this (psudo code, not exact one ):

with( f=open( "a.csv" ) ):
    f.writeline( ",".join(l) );

answered Oct 6, 2013 at 3:00

zinking

5,7255 gold badges52 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rob B. Over a year ago

That's an interesting idea...but where do I put that code? I want the Excel file for manipulating the data later.

meantobe · Accepted Answer · 2013-10-06 06:23:59Z

0

re.findall return a list object, you may want to get the first object of the list.
you can only use str.replace from an string object, such asprice.replace('a', 'b')
should use ws.cell('F5') instead of ws.cell(1,1) to access one cell.

edited Oct 6, 2013 at 6:23

answered Oct 6, 2013 at 6:16

meantobe

11 bronze badge

Collectives™ on Stack Overflow

Writing Values From Python into Excel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related