def xrefChanges2(date1=None, date2=None):
#read the secids we are interested in. we might want to change the source
uni = set()
#with open("/".join((os.environ["ROOT_DIR"],"run",os.environ["STRAT"],"old.secids.txt")),"r) as file:
with open("/apps/ase/run/useq-live/old.secids.txt", "r") as file:
for line in file:
tokens = line.strip().split("|")
uni.add(tokens[2])
if date2 is None:
date2 = util.now()
if date1 is None:
date1 = util.exchangeTradingOffset(
os.environ["PRIMARY_EXCHANGE"],
util.convert_millis_to_datetime(date2).strftime("%Y%m%d"), -1)
date1 = util.convert_date_to_millis(str(date1))
tickerChanges = []
cusipChanges = []
for secid in uni:
ticker1 = database.execute(
"SELECT value FROM xref WHERE secid={secid} AND xref_type=2 AND source=2 AND born<={date} AND (died IS NULL OR died>{date})"
.format(secid=secid, date=date1)).fetchone()
ticker1 = ticker1["value"] if ticker1 is not None else None
ticker2 = database.execute(
"SELECT value FROM xref WHERE secid={secid} AND xref_type=2 AND source=2 AND born<={date} AND (died IS NULL OR died>{date})"
.format(secid=secid, date=date2)).fetchone()
ticker2 = ticker2["value"] if ticker2 is not None else None
cusip1 = database.execute(
"SELECT value FROM xref WHERE secid={secid} AND xref_type=1 AND source=2 AND born<={date} AND (died IS NULL OR died>{date})"
.format(secid=secid, date=date1)).fetchone()
cusip1 = cusip1["value"] if cusip1 is not None else None
cusip2 = database.execute(
"SELECT value FROM xref WHERE secid={secid} AND xref_type=1 AND source=2 AND born<={date} AND (died IS NULL OR died>{date})"
.format(secid=secid, date=date2)).fetchone()
cusip2 = cusip2["value"] if cusip2 is not None else None
if ticker1 != ticker2: tickerChanges.append((secid, ticker1, ticker2))
if cusip1 != cusip2: cusipChanges.append((secid, cusip1, cusip2))
report = []
report.append("Xref changes between {} and {}".format(
util.convert_millis_to_datetime(date1).strftime("%Y%m%d"),
util.convert_millis_to_datetime(date2).strftime("%Y%m%d")))
for secid, x1, x2 in tickerChanges:
report.append("{}: {} => {}".format(secid, x1, x2))
for secid, x1, x2 in cusipChanges:
report.append("{}: {} => {}".format(secid, x1, x2))
return "\n".join(report) if len(report) > 1 else None
###############
#code to handle files with mod time less than already processed files. signifies a potential problem
previousFile = database.getLastProcessedFileTuple(source)
previousModTime = previousFile[
"date_modified"] if previousFile is not None else None
if previousModTime is not None and util.convert_date_to_millis(
file["date"]
[1]) < previousModTime - util.convert_date_to_millis(
datetime.timedelta(minutes=45)):
util.error(
"Encountered file {} has mod_time {} before most recently processed file's mod time {}"
.format(
file["path"], file["date"][1],
util.convert_millis_to_datetime(
previousModTime)))
if options.ignore_mod_time == 2:
util.error("Fake processing file {}".format(
file["path"]))
database.addProcessedFiles(source,
file["path_rel"], None,
file["date"][1], False)
database.commit()
continue
elif options.ignore_mod_time == 1:
util.error(
"Proceeding with processing file {}".format(
file["path"]))
pass
else:
util.error(
#email the changes
combined = set()
for xx in newMappings - existingMappings:
combined.add((xx[0], xx[1], xx[2], xx[3], 1))
for xx in existingMappings - newMappings:
combined.add((xx[0], xx[1], xx[2], xx[3], -1))
uni = __getUni()
report = ["operation, secid, ticker, barraid, from, to "]
for xx in sorted(combined, key=lambda xx: (xx[0], xx[4], xx[2])):
secid = int(xx[0])
highlight = "***" if int(xx[0]) in uni else ""
operation = "D" if xx[4] < 0 else "I"
ticker = database.getXrefFromSecid("TIC", secid)
barraid = xx[1]
fromDate = util.convert_millis_to_datetime(long(
xx[2])).strftime("%Y%m%d")
toDate = util.convert_millis_to_datetime(long(
xx[3])).strftime("%Y%m%d") if xx[3] is not None else None
report.append("{} {}, {}, {}, {}, {}, {}".format(
highlight, operation, secid, ticker, barraid, fromDate,
toDate))
#as a bonus, see which universe securities are unmapped
for xx in newMappings:
secid = int(xx[0])
died = xx[3]
if (died is None or died > util.now()) and secid in uni:
uni.remove(secid)
if len(uni) > 0:
#!/usr/bin/env python
import sys
import re
import util
if len(sys.argv) == 1:
data = sys.stdin.readlines()
for line in data:
fields = line.strip().split("|")
newlin = ""
for i, field in enumerate(fields):
if re.match("\d{12,13}", field):
newlin += str(
util.convert_millis_to_datetime(
long(field)).strftime("%Y%m%d %H:%M:%S"))
else:
newlin += field
if i != len(fields) - 1:
newlin += "|"
print newlin
elif len(sys.argv) == 2:
print util.convert_millis_to_datetime(long(
sys.argv[1])).strftime("%Y%m%d %H:%M:%S")
else:
print "Either pipe lines, or give a single long as input"
def newProcessedFiles():
sources = {}
for x in database.execute("SELECT code,name FROM {} GROUP BY code".format(
database.SOURCE_TYPE)).fetchall():
sources[x["code"]] = x["name"]
attributes = {}
for x in database.execute(
"SELECT code,name,source,tableref FROM {}".format(
database.ATTRIBUTE_TYPE_TABLE)).fetchall():
attributes[x["code"]] = (sources[x["source"]], x["name"],
x["tableref"])
output = []
#get last check timestamp
lastCheckFilepath = "/".join((os.environ["SCRAP_DIR"], "processed.last"))
try:
file = open(lastCheckFilepath, "r")
data = file.read().strip()
lastTimestamp = long(data)
dt = util.convert_millis_to_datetime(lastTimestamp)
dt = dt.astimezone(pytz.timezone("US/Eastern"))
output.append("Files processed since {}".format(
dt.strftime("%Y%m%d %H:%M:%S")))
file.close()
except:
lastTimestamp = util.now() - util.convert_date_to_millis(
datetime.timedelta(hours=1))
output.append(
"No last check time available. Displaying new files processed within the last 1 hour."
)
currentTimestamp = util.now()
rows = database.execute(
"SELECT * FROM processed_files WHERE ts>={} ORDER BY ts".format(
lastTimestamp)).fetchall()
numOfFiles = len(rows)
if numOfFiles == 0: #if nothing to be printed, print nothing. do not update timestamp
return None
output.append("")
processedBySource = {}
for row in rows:
dt = util.convert_millis_to_datetime(long(row["ts"]))
dt = dt.astimezone(pytz.timezone("US/Eastern"))
source = sources[row["source"]]
path = row["path"].split("/")[-1]
secs = float(row["processing_time"]) / 1000
p = processedBySource.get(source, None)
if p is None:
p = []
processedBySource[source] = p
p.append((dt, path, secs))
for source in sorted(processedBySource.keys()):
files = processedBySource[source]
if len(files) <= 20:
for dt, path, secs in files:
output.append("[{}]: {}, {}, {} secs".format(
dt.strftime("%Y%m%d %H:%M:%S"), source, path, secs))
else:
#print first 10
for dt, path, secs in files[0:10]:
output.append("[{}]: {}, {}, {} secs".format(
dt.strftime("%Y%m%d %H:%M:%S"), source, path, secs))
output.append("... ({} more files) ...".format(len(files) - 20))
#print last 10
for dt, path, secs in files[-10:]:
output.append("[{}]: {}, {}, {} secs".format(
dt.strftime("%Y%m%d %H:%M:%S"), source, path, secs))
output.append("")
rows = database.execute(
"SELECT stats.type AS type, SUM(stats.inserted) AS inserted,SUM(stats.killed) AS killed FROM processed_files AS files, processed_files_att_stats AS stats WHERE files.ts>={} AND files.source=stats.source AND files.path=stats.path GROUP BY stats.type ORDER BY stats.type"
.format(lastTimestamp)).fetchall()
output.append("")
output.append("Database attributes affected (inserted/killed)")
for row in sorted(rows, key=lambda x: attributes[x["type"]][1]):
output.append("{}, {}, {}: {} / {}".format(attributes[row["type"]][0],
attributes[row["type"]][1],
attributes[row["type"]][2],
row["inserted"],
row["killed"]))
#save the check time
file = open(lastCheckFilepath, "w")
file.write(str(currentTimestamp))
file.close()
return "\n".join(output)