def createCurrentTopGainersTable():
#Create the current_ohlc table if it doesn't exist yet
doesTableExist = qs.checkTableExistence("current_gainers")
if doesTableExist == 0:
#create table before adding
print "creating table current_gainers!"
qs.createCurrentTopGainersTable()
def createCurrentPricesTable():
#Create the current_prices table if it doesn't exist yet
doesTableExist = qs.checkTableExistence("current_prices")
if doesTableExist == 0:
#create table before adding
print "creating table current_prices!"
qs.createCurrentPricesTable()
def createCurrentOHLCTable():
#Create the current_ohlc table if it doesn't exist yet
doesTableExist = qs.checkTableExistence("current_ohlc")
if doesTableExist == 0:
#create table before adding
print "creating table current_ohlc!"
qs.createOHLCurrentTable()
def readDropboxStockQuotes (myFile):
with open(myFile,'rb') as f:
reader = csv.reader(f)
header = ['Quote','Date','Open','High','Low','Close','Volume','Unknown']
jsonData = []
rownum = 0
for row in reader:
#print row
temp = []
colnum = 0
for col in row:
#jsonCol = header[colnum]
#print '%s: %s' % (jsonCol, col)
temp.append(col)
colnum += 1
try:
temp[1] = datetime.datetime.strptime(temp[1], "%m/%d/%Y").strftime("%Y-%m-%d %H:%M:%S")
except ValueError:
temp[1] = datetime.datetime.strptime(temp[1], "%m-%d-%Y").strftime("%Y-%m-%d %H:%M:%S")
'''
jsonData.append({header[0]: temp[0], header[1]: temp[1], \
header[2]: temp[2], header[3]: temp[3], \
header[4]: temp[4], header[5]: temp[5], \
header[6]: temp[6], header[7]: temp[7]})
'''
tempQuote = temp[0]
tempQuote = tempQuote.replace("^", "_")
tempQuote = tempQuote.replace("-", "_")
tempQuote += "_"
doesTableExist = qs.checkTableExistence(tempQuote)
#print "does quote: %s have a table? %s" % (tempQuote, doesTableExist)
if doesTableExist:
#insert data to database
print "%s table exists... writing data..." % tempQuote
qs.writeQuoteDataToDB(tempQuote,temp,"dropbox")
else:
#create table before adding
print "creating table %s!" % tempQuote
qs.createTable(tempQuote)
print " writing data to %s" % tempQuote
qs.writeQuoteDataToDB(tempQuote,temp,"dropbox")
rownum += 1
def downloadPseCSVdata(quoteData):
#file to be written to
tempFile = "stockquote.csv"
#segregate the data
company = quoteData[0]
id = quoteData[1]
security = quoteData[2]
doesTableExist = qs.checkTableExistence(company)
#print "does quote: %s have a table? %s" % (company, doesTableExist)
if doesTableExist == 0:
#create table before adding
print "creating table %s!" % company
qs.createTable(company)
latestTS = qs.GetLatestTimestamp(company)
if latestTS == None:
latestTS = "1900-01-01 00:00:00"
try:
latestTS = latestTS.strftime("%Y-%m-%d %H:%M:%S.0")
except AttributeError:
latestTS = datetime.datetime.strptime(latestTS, "%Y-%m-%d %H:%M:%S")
#Quick Check if stocks data has been updated for today
curTS = time.strftime("%Y-%m-%d 00:00:00.0")
#print "timestamp today: %s" % (curTS)
if curTS == latestTS:
print "company: %s, id: %s, security: %s, stock is UP TO DATE!!!" % (company, id, security)
return
else:
#Current timestamp
curTS = time.strftime("%Y-%m-%d %H:%M:%S.0")
#Philippine Market Close Time
targetTS = time.strftime("%Y-%m-%d 15:35:00.0")
prevTS = (pd.to_datetime("2016-03-11") - td(1)).strftime("%Y-%m-%d 00:00:00.0")
if (latestTS == prevTS) and (curTS < targetTS):
print "company: %s, id: %s, security: %s, stock is UP TO DATE!!!" % (company, id, security)
return
#pass
print "company: %s, id: %s, security: %s, latest timestamp: %s" % (company, id, security, latestTS)
url = 'http://pse.ph/stockMarket/companyInfoHistoricalData.html?method=downloadHistoricData&ajax=true&security=%s' % (security)
response = urllib2.urlopen(url)
print "The Headers are: ", response.info()['content-disposition']
try:
# Get all data
html = response.read()
time.sleep(1)
#print "Get all date:", html
#open the file for writing
fh = open(tempFile,"w")
fh.write(html)
fh.close()
with open(tempFile,'rb') as f:
reader = csv.reader(f)
header = []
rownum = 0
for row in reader:
if rownum == 0:
header = row
rownum += 1
#print header
continue
#print row
temp = []
colnum = 0
for col in row:
temp.append(col)
colnum += 1
if temp[0] > latestTS:
# write to database only if the timestamp is greater than
# the latest timestamp
print "Writing row with timestamp: %s" % (temp[0])
qs.writeQuoteDataToDB(company,temp,"pse")
rownum += 1
except:
print "\nERROR: Info might be wrong for company: %s, id: %s, security: %s\n" % (company, id, security)
