Performing calculations from an excel list

[StarryEyedSoy]

I have a list of the stock prices over 14 years and am required to calculate the daily returns as
Returns = log(today_s price/yesterday_s price)

Here's what my data looks like:

7/18/2008 19.57
7/17/2008 18.08
7/16/2008 14.27

How do I calculate this while reading the data from excel? I have split the string into a list, but don't know how to simultaneously use data from 2 different lines (I am reading one line at a time using a for loop)

Any help would be greatly appreciated, thank you!!

[jlm699]

You can simply store the data of the previous line in a variable, so that on the following iteration you can calculate with the previous line and the current one.

[StarryEyedSoy]

import math

for line in inp:
    myList = line.split()
    prev_i = None
    for i in myList:
        if not prev_i:
            continue
        dailyReturns = log(i.myList[1] - prev_i.myList[1])
    myList.append(dailyReturns)
    print myList

thats what I used, but I am getting an error saying that dailyReturns is not defined. )-:

[jlm699]

import math

for line in inp:
    myList = line.split()
    prev_i = None
    for i in myList:
        if not prev_i:
            continue
        dailyReturns = log(i.myList[1] - prev_i.myList[1])
    myList.append(dailyReturns)
    print myList

thats what I used, but I am getting an error saying that dailyReturns is not defined. )-:

You must consider that if myList is empty, dailyReturns will never be initialized (defined) so you will run into this problem. You should initialize dailyReturns to some arbitraty value (like 0) and then check to see if it has changed after the for i in myList loop. That way you only append it if it changed.

[woooee]

This might be easier to understand (not tested). This is not as efficient as storing the value as you perform one additional split() per record instead of storing it, but it may be more straight forward.

import math

data = open(filename, "r").readlines()   ##  reads entire file into list
stop=len(data)
daily_list=[]
for ctr in range(1, stop):     ## start with the second record
    current_list = data[ctr].split()
    prev_list =data[ctr-1].split()
    daily_returns = log(current_list[1] - prev_list[1])
    daily_list.append(daily_returns)
print daily_list

[StarryEyedSoy]

Thank you so much JLM and Wooee (-: I managed to work it out, here is my code:

import math
import time

prevRec = None
for i in inplis:
    rec = i.strip().split(",")
    if rec[0] == 'Date':
        continue
    if prevRec == None:
        prevRec = rec[:]
    else:
        currPrice = float(rec[-1])
        prevPrice = float (prevRec[-1])
        prevRec = rec[:]
        dailyReturns = math.log(currPrice/prevPrice)        
        rec.append(dailyReturns)
        myTime = str(rec[0])
        print myTime
       
    import string           
    newStr = string.join(map(str,rec),",")+'\n'
    outp.write(newStr)
   

outp.close()

This worked well for me. I have another quick question, though. How do I read a date from the file and read into a type of time so that I can convert it to another (strftime?)

The logic I am trying to put to work here is this:
I am trying to read the first element of rec into a string, namely myStr. I need to read this into the time format 2008-7-21 (how do I do this? I've tried so many ways, do I use mktime?) so that once Python stores it as a date I can do time.strftime(%y%m%d,myTime)

Thanks! (-:

[jlm699]

You should use strptime to translate the time into a time structure. This works exactly like strftime only backwards.. you supply it with the format that the time will appear in and it will give you a time struct instead of giving it a time struct and telling the function how to format the output!
An example:

>>> import time
>>> my_time = '2008-7-21'
>>> time.strptime( my_time, '%Y-%m-%d' )
(2008, 7, 21, 0, 0, 0, 0, 203, -1)
>>> new_time = time.strptime( my_time, '%Y-%m-%d' )
>>> time.strftime( '%y%m%d', new_time )
'080721'
>>>

Refer to this page for more information on the time formatting strings for strftime and strptime.

[StarryEyedSoy]

You should use strptime to translate the time into a time structure. This works exactly like strftime only backwards.. you supply it with the format that the time will appear in and it will give you a time struct instead of giving it a time struct and telling the function how to format the output!
An example:

>>> import time
>>> my_time = '2008-7-21'
>>> time.strptime( my_time, '%Y-%m-%d' )
(2008, 7, 21, 0, 0, 0, 0, 203, -1)
>>> new_time = time.strptime( my_time, '%Y-%m-%d' )
>>> time.strftime( '%y%m%d', new_time )
'080721'
>>>

Refer to this page for more information on the time formatting strings for strftime and strptime.

Jlm, thank you so much, that worked like a charm! My code now works perfectly! (-: (-: I really appreciate your taking the time to answer my questions!