def get_qwop(connection, dateBeg, dateEnd, company):
'''Quantity weighted offer price query, from Ramu'''
q = """Select
com.Fp_Offers.DTTM_ID,
com.Fp_Offers.Trading_DATE as 'Date',
com.Fp_Offers.Trading_Period as 'TP',
com.MAP_PNode_to_POC_and_Island.Island,
com.MAP_Participant_names.Parent_Company_ID,
(Sum((com.Fp_Offers.Offer_Price * com.Fp_Offers.Offer_Quantity)) /
Sum(com.Fp_Offers.Offer_Quantity)) As 'QWOP'
From
com.Fp_Offers Inner Join
com.MAP_Participant_names On com.Fp_Offers.Trader_Id =
com.MAP_Participant_names.Trader_Id Inner Join
com.MAP_PNode_to_POC_and_Island On com.Fp_Offers.PNode =
com.MAP_PNode_to_POC_and_Island.PNode
Where
com.Fp_Offers.Trading_DATE >= '%s' And
com.Fp_Offers.Trading_DATE <= '%s' And
com.Fp_Offers.trade_type = 'ENOF' And
com.MAP_Participant_names.Parent_Company_ID = '%s' And
com.MAP_PNode_to_POC_and_Island.Island = 'SI'
Group By
com.Fp_Offers.DTTM_ID, com.Fp_Offers.Trading_DATE,
com.Fp_Offers.Trading_Period, com.MAP_PNode_to_POC_and_Island.Island,
com.MAP_Participant_names.Parent_Company_ID
order by
com.Fp_Offers.DTTM_ID""" % (dateBeg.strftime("%Y-%m-%d"),
dateEnd.strftime("%Y-%m-%d"), company)
t = sql.read_frame(q, connection, coerce_float=True)
t['Date'] = t['Date'].map(lambda x: date_converter(x))
t = t.set_index(['Date', 'TP']).QWOP
return t
def get_qwop(connection,dateBeg,dateEnd,company):
'''Quantity weighted offer price query, from Ramu'''
q = """Select
com.Fp_Offers.DTTM_ID,
com.Fp_Offers.Trading_DATE as 'Date',
com.Fp_Offers.Trading_Period as 'TP',
com.MAP_PNode_to_POC_and_Island.Island,
com.MAP_Participant_names.Parent_Company_ID,
(Sum((com.Fp_Offers.Offer_Price * com.Fp_Offers.Offer_Quantity)) /
Sum(com.Fp_Offers.Offer_Quantity)) As 'QWOP'
From
com.Fp_Offers Inner Join
com.MAP_Participant_names On com.Fp_Offers.Trader_Id =
com.MAP_Participant_names.Trader_Id Inner Join
com.MAP_PNode_to_POC_and_Island On com.Fp_Offers.PNode =
com.MAP_PNode_to_POC_and_Island.PNode
Where
com.Fp_Offers.Trading_DATE >= '%s' And
com.Fp_Offers.Trading_DATE <= '%s' And
com.Fp_Offers.trade_type = 'ENOF' And
com.MAP_Participant_names.Parent_Company_ID = '%s' And
com.MAP_PNode_to_POC_and_Island.Island = 'SI'
Group By
com.Fp_Offers.DTTM_ID, com.Fp_Offers.Trading_DATE,
com.Fp_Offers.Trading_Period, com.MAP_PNode_to_POC_and_Island.Island,
com.MAP_Participant_names.Parent_Company_ID
order by
com.Fp_Offers.DTTM_ID""" % (dateBeg.strftime("%Y-%m-%d"),dateEnd.strftime("%Y-%m-%d"),company)
t = sql.read_frame(q,connection,coerce_float=True)
t['Date'] = t['Date'].map(lambda x: date_converter(x))
t = t.set_index(['Date','TP']).QWOP
return t
def FP_getter(connection,q):
from time import clock
tic = clock()
s = sql.read_frame(q,connection,coerce_float=True)
if type(s.Date[0]) is unicode: #Need to test this as either different versions or operating systems appear to have an effect on the date parsing...
s['Date'] = s['Date'].map(lambda x: date_converter(x))
s = s.set_index(['Date','TP','PNode']).unstack(level=2)
toc = clock()
print "Took %s seconds to retrieve and convert data from warehouse!" % str(toc-tic)
return s
def FP_getter(connection, q):
from time import clock
tic = clock()
s = sql.read_frame(q, connection, coerce_float=True)
if type(
s.Date[0]
) is unicode: #Need to test this as either different versions or operating systems appear to have an effect on the date parsing...
s['Date'] = s['Date'].map(lambda x: date_converter(x))
s = s.set_index(['Date', 'TP', 'PNode']).unstack(level=2)
toc = clock()
print "Took %s seconds to retrieve and convert data from warehouse!" % str(
toc - tic)
return s
def get_ramu_summary(connection, dateBeg, dateEnd):
'''island energy, reserve and hvdc summary'''
q = """Select
atomic.Atm_Spdsolved_Islands.DIM_DTTM_ID,
atomic.Atm_Spdsolved_Islands.DATA_DATE As 'Date',
atomic.Atm_Spdsolved_Islands.PERIOD As 'TP',
atomic.Atm_Spdsolved_Islands.island,
atomic.Atm_Spdsolved_Islands.reference_price,
atomic.Atm_Spdsolved_Islands.reserve_price_six_sec,
atomic.Atm_Spdsolved_Islands.reserve_price_sixty_sec,
atomic.Atm_Spdsolved_Islands.energy_offered,
atomic.Atm_Spdsolved_Islands.energy_cleared,
atomic.Atm_Spdsolved_Islands.[load],
atomic.Atm_Spdsolved_Islands.net_hvdc_interchange,
atomic.Atm_Spdsolved_Islands.max_nodal_price,
atomic.Atm_Spdsolved_Islands.min_nodal_price,
atomic.Atm_Spdsolved_Islands.max_offer_price,
atomic.Atm_Spdsolved_Islands.six_sec_risk_node,
atomic.Atm_Spdsolved_Islands.sixty_sec_risk_node,
atomic.Atm_Spdsolved_Islands.six_sec_risk,
atomic.Atm_Spdsolved_Islands.sixty_sec_risk,
atomic.Atm_Spdsolved_Islands.cleared_reserve_six_sec,
atomic.Atm_Spdsolved_Islands.cleared_reserve_sixty_sec
From
atomic.Atm_Spdsolved_Islands Inner Join
atomic.DIM_DATE_TIME On atomic.Atm_Spdsolved_Islands.DIM_DTTM_ID =
atomic.DIM_DATE_TIME.DIM_DATE_TIME_ID
Where
atomic.DIM_DATE_TIME.DIM_CIVIL_DATE >= '%s' And
atomic.DIM_DATE_TIME.DIM_CIVIL_DATE <= '%s' """ % (
dateBeg.strftime("%Y-%m-%d"), dateEnd.strftime("%Y-%m-%d"))
t = sql.read_frame(q, connection, coerce_float=True)
t['Date'] = t['Date'].map(lambda x: date_converter(x))
t = t.set_index(['Date', 'TP', 'island'])
del t['DIM_DTTM_ID']
return t
def get_ramu_summary(connection,dateBeg,dateEnd):
'''island energy, reserve and hvdc summary'''
q="""Select
atomic.Atm_Spdsolved_Islands.DIM_DTTM_ID,
atomic.Atm_Spdsolved_Islands.DATA_DATE As 'Date',
atomic.Atm_Spdsolved_Islands.PERIOD As 'TP',
atomic.Atm_Spdsolved_Islands.island,
atomic.Atm_Spdsolved_Islands.reference_price,
atomic.Atm_Spdsolved_Islands.reserve_price_six_sec,
atomic.Atm_Spdsolved_Islands.reserve_price_sixty_sec,
atomic.Atm_Spdsolved_Islands.energy_offered,
atomic.Atm_Spdsolved_Islands.energy_cleared,
atomic.Atm_Spdsolved_Islands.[load],
atomic.Atm_Spdsolved_Islands.net_hvdc_interchange,
atomic.Atm_Spdsolved_Islands.max_nodal_price,
atomic.Atm_Spdsolved_Islands.min_nodal_price,
atomic.Atm_Spdsolved_Islands.max_offer_price,
atomic.Atm_Spdsolved_Islands.six_sec_risk_node,
atomic.Atm_Spdsolved_Islands.sixty_sec_risk_node,
atomic.Atm_Spdsolved_Islands.six_sec_risk,
atomic.Atm_Spdsolved_Islands.sixty_sec_risk,
atomic.Atm_Spdsolved_Islands.cleared_reserve_six_sec,
atomic.Atm_Spdsolved_Islands.cleared_reserve_sixty_sec
From
atomic.Atm_Spdsolved_Islands Inner Join
atomic.DIM_DATE_TIME On atomic.Atm_Spdsolved_Islands.DIM_DTTM_ID =
atomic.DIM_DATE_TIME.DIM_DATE_TIME_ID
Where
atomic.DIM_DATE_TIME.DIM_CIVIL_DATE >= '%s' And
atomic.DIM_DATE_TIME.DIM_CIVIL_DATE <= '%s' """ % (dateBeg.strftime("%Y-%m-%d"),dateEnd.strftime("%Y-%m-%d"))
t = sql.read_frame(q,connection,coerce_float=True)
t['Date'] = t['Date'].map(lambda x: date_converter(x))
t = t.set_index(['Date','TP','island'])
del t['DIM_DTTM_ID']
return t
