Python: Excel is being fed real-time stock prices. How to append prices every 10 min to csv?

Question

Here is my python script:

from xlrd import open_workbook
from xlrd import xldate_as_tuple

book = open_workbook('C:/Users/.......Constantquotes.xlsx')

sheet0 = book.sheet_by_index(0)  #Initializes the first row
print sheet0.row(0)              #Prints the first row
date_value = xldate_as_tuple(sheet0.cell(0,1).value,book.datemode)
print date_value                 #Prints the date cell


#  Next step: make the a list for the stock price
#  Next step: append the list to a csv

So I am getting the prices into excel, and they update themselves every second. What I would like to do is have the python script read the excel sheet every ten minutes (I will use cronjob to activate the python script) and then append the latest price to a csv file that contains prices for only that stock. The problem is that when I run the python script, it gives me the prices and the date of the last time I SAVED the excel file, and not the CURRENT price. I tried to set options-->save-->save-->save autorecover information--> 1 min, but that didn't help me. So to rephrase the question, how do I make it so that everytime xlrd reads into the .xls file (so that the python script can pull out the current price and date) I get the current price and date, and not the price and date the last time that I saved the .xlxs file?

Ideally I would like to be able to step away from the computer for a few days and come back to a populated .csv

Or perhaps there is a better solution to populate the .csv?

Is there any way that Python could just access the feed that provides the data? How is the data getting into Excel? — steveha, Jan 25 '13 at 08:35

score 0 · Answer 1 · answered Jan 25 '13 at 01:56

I think this is one of those tasks which would be better handled with VBA. Python can't access the data because it hasn't been saved - it's in memory, but you're telling python to read from disk. In my view you have one of two reasonable choices.

Have a VBA macro populate the csv.
Have a VBA macro save the xls at regular intervals.

Aziz · Answer 2 · 2013-01-25T08:32:58.023

Well, this should rather be achieved in Excel as opposed to Python. In your VBA you can use the following:

At the start of your VBA code.

Application.DisplayAlerts = False
When you want to save your workbook.

ThisWorkbook.Save

The reason for using step 1 is to make sure it doesn't ask for manual confirmation before saving the workbook in case you are away from your computer. I would advise against saving workbook on each price update. Maybe you can use a timer to periodically save it.

score 0 · Answer 3 · edited Dec 24 '22 at 01:45

WHy Create CSV?

Use small VBA timer script to copy stock price in the same worksheet, just increase the row number by 1 on every 10 min, your limit is excel max row numbers. I'm doing this or write to file if you want a file.

Sub timer()
  Application.OnTime Now() + TimeValue("00:10:00"), "main" 'repeat main every 10minute
End Sub
'------------------------------------------------------

Sub main() 

    Cells(i+1, 1) = price
     i = i + 1
     timer

End Sub

Python: Excel is being fed real-time stock prices. How to append prices every 10 min to csv?

3 Answers3