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