def upload_benchmark(db, excel_file, sheet_name_or_idx):
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
modules = [4170, 4171, 4172, 4173]
modules = map(str, modules)
data = db.get_data(qry_get_modules(tt_name, modules))
ok = [db.execute(qry_update_expiry(row[0], row[-1])) for row in data if row[0] == row[1]]
def upload_benchmark(db, excel_file, sheet_name_or_idx, data_provider):
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
db.execute(qry_update_last_date_of_month(tt_name))
# back up ExternalData.dbo.tblGrowthSeries
backup_table(db, "ExternalData.dbo.tblGrowthSeries")
count = db.execute(qry_delete_codes_in_externaldata_tblGrowthSeries(tt_name, data_provider))
logger.info("{} deleted from ExternalData.dbo.tblGrowthSeries".format(count))
count = db.execute(qry_add_codes_in_externaldata_tblGrowthSeries(tt_name, data_provider))
logger.info("{} added into ExternalData.dbo.tblGrowthSeries".format(count))
assert count == len(tt_name)
logger.info("Before inserting data to tblBenchmarkGrowthSeries from ExternalData..tblGrowthSeries")
rows = db.get_data(
"""
select top 1 igs.*
from tblBenchmarkGrowthSeries igs
inner join tblBenchmarkCode ic on igs.benchmarkID = ic.benchmarkID
where ic.BenchmarkCode=(select top 1 code from {tt_name} order by newid())
and ic.isUsedForGrowthSeries= 1
order by igs.[date] desc
""".format(
tt_name=tt_name
)
)
logger.info(rows)
count = db.execute(qry_refresh_data_in_table("tblBenchmarkCode", tt_name, data_provider, "benchmarkCode"))
# assert count == len(tt_name)
try:
logger.info("After inserting data to tblBenchmarkGrowthSeries".format(rows[0][1]))
rows = db.get_data(
"""
select top 1 * from tblBenchmarkGrowthSeries
where benchmarkid=?
order by [date] desc
""",
rows[0][1],
)
logger.info(rows)
except:
logger.info("New or Error")
# now insert new codes if exist
to_be_added_benchmark_codes = db.get_data(qry_check_codes_not_exist_in_tblBenchmarkCode(tt_name, data_provider))
if to_be_added_benchmark_codes:
to_be_added_benchmark_codes = [row.code for row in to_be_added_benchmark_codes]
logger.info("{} do not exist in tblInvestmentCode, need to add".format(to_be_added_benchmark_codes))
logger.info("Need to be implemented")
else:
logger.info("There is no new benchmark codes")
def upload_investment(db, excel_file, sheet_name_or_idx, data_provider):
# Import excel file into temp table
def qry_insert_straight_to_tblInvestmentGrowthSeries(tt_name):
# always Lonsec data provider = 1
return """
insert into tblInvestmentGrowthSeries (InvestmentID, Date, Value, ValueExDiv, IsLonsecData)
select code, [Date], [Value], 0, 1
from {tt_name}
""".format(
tt_name=tt_name
)
def qry_delete_all_existing_data_this_month(tt_name):
return """
delete igs
from [tblInvestmentGrowthSeries] igs
join {tt_name} tt
on tt.Code = igs.InvestmentID
and igs.Date = tt.date
""".format(
tt_name=tt_name
)
def qry_check_all_existing_data_previous_month(tt_name):
return """
select count(*)
from [tblInvestmentGrowthSeries] igs
join {tt_name} tt
on tt.Code = igs.InvestmentID
and igs.Date = cast(DATEADD(MONTH, DATEDIFF(MONTH, -1, cast(tt.date as date))-1, -1) as date)
""".format(
tt_name=tt_name
)
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
db.execute(qry_update_last_date_of_month(tt_name))
count = db.get_one_value(qry_check_all_existing_data_previous_month(tt_name))
assert count == len(tt_name)
# back up ExternalData.dbo.tblGrowthSeries
backup_table(db, "tblInvestmentGrowthSeries")
count = db.execute(qry_delete_all_existing_data_this_month(tt_name))
logger.info("{} deleted from tblInvestmentGrowthSeries".format(count))
count = db.execute(qry_insert_straight_to_tblInvestmentGrowthSeries(tt_name))
logger.info("{} inserted into tblInvestmentGrowthSeries".format(count))
def upload_benchmark(db, excel_file, sheet_name_or_idx, data_provider):
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
db.execute(qry_update_last_date_of_month(tt_name))
# back up ExternalData.dbo.tblGrowthSeries
backup_table(db, 'ExternalData.dbo.tblGrowthSeries')
count = db.execute(qry_delete_codes_in_externaldata_tblGrowthSeries(tt_name, data_provider))
logger.info('{} deleted from ExternalData.dbo.tblGrowthSeries'.format(count))
count = db.execute(qry_add_codes_in_externaldata_tblGrowthSeries(tt_name, data_provider))
logger.info('{} added into ExternalData.dbo.tblGrowthSeries'.format(count))
assert count == len(tt_name)
logger.info('Before inserting data to tblBenchmarkGrowthSeries from ExternalData..tblGrowthSeries')
rows = db.get_data('''
select top 1 igs.*
from tblBenchmarkGrowthSeries igs
inner join tblBenchmarkCode ic on igs.benchmarkID = ic.benchmarkID
where ic.BenchmarkCode=(select top 1 code from {tt_name} order by newid())
and ic.isUsedForGrowthSeries= 1
order by igs.[date] desc
'''.format(tt_name=tt_name))
logger.info(rows)
count = db.execute(qry_refresh_data_in_table('tblBenchmarkCode', tt_name, data_provider, 'benchmarkCode'))
# assert count == len(tt_name)
logger.info('After inserting data to tblBenchmarkGrowthSeries'.format(rows[0][1]))
rows = db.get_data('''
select top 1 * from tblBenchmarkGrowthSeries
where benchmarkid=?
order by [date] desc
''', rows[0][1])
logger.info(rows)
# now insert new codes if exist
to_be_added_benchmark_codes = db.get_data(qry_check_codes_not_exist_in_tblBenchmarkCode(tt_name, data_provider))
if to_be_added_benchmark_codes:
to_be_added_benchmark_codes = [row.code for row in to_be_added_benchmark_codes]
logger.info('{} do not exist in tblInvestmentCode, need to add'.format(to_be_added_benchmark_codes))
logger.info('Need to be implemented')
else:
logger.info('There is no new benchmark codes')
def process(db, excel_file, sheet_name_or_idx):
helper.backup_table(db, 'tblInvestmentGrowthSeries')
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
unique_investmentids = db.get_one_value(qry_unique_investment_id(tt_name))
logger.info(unique_investmentids)
current_gs_tt_name = TempTable.create_from_query(db, qry_current_growth_series(tt_name))
qry_turn_off_gs = partial(qry_update_gs, 0, current_gs_tt_name)
qry_turn_on_gs = partial(qry_update_gs, 1, current_gs_tt_name)
assert unique_investmentids == len(current_gs_tt_name)
db.execute(qry_turn_off_gs()) # ignore count
db.execute(qry_turn_on_gs()) # ignore count
def upload_investment(db, excel_file, sheet_name_or_idx, data_provider):
# Import excel file into temp table
def qry_insert_straight_to_tblInvestmentGrowthSeries(tt_name):
# always Lonsec data provider = 1
return '''
insert into tblInvestmentGrowthSeries (InvestmentID, Date, Value, ValueExDiv, IsLonsecData)
select code, [Date], [Value], 0, 1
from {tt_name}
'''.format(tt_name=tt_name)
def qry_delete_all_existing_data_this_month(tt_name):
return '''
delete igs
from [tblInvestmentGrowthSeries] igs
join {tt_name} tt
on tt.Code = igs.InvestmentID
and igs.Date = tt.date
'''.format(tt_name=tt_name)
def qry_check_all_existing_data_previous_month(tt_name):
return '''
select count(*)
from [tblInvestmentGrowthSeries] igs
join {tt_name} tt
on tt.Code = igs.InvestmentID
and igs.Date = cast(DATEADD(MONTH, DATEDIFF(MONTH, -1, cast(tt.date as date))-1, -1) as date)
'''.format(tt_name=tt_name)
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
db.execute(qry_update_last_date_of_month(tt_name))
count = db.get_one_value(qry_check_all_existing_data_previous_month(tt_name))
assert count == len(tt_name)
# back up ExternalData.dbo.tblGrowthSeries
backup_table(db, 'tblInvestmentGrowthSeries')
count = db.execute(qry_delete_all_existing_data_this_month(tt_name))
logger.info('{} deleted from tblInvestmentGrowthSeries'.format(count))
count = db.execute(qry_insert_straight_to_tblInvestmentGrowthSeries(tt_name))
logger.info('{} inserted into tblInvestmentGrowthSeries'.format(count))
def upload_cost(db, excel_file, sheet_name_or_idx):
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
# create other quries for attribute 19
tt_name = TempTable.create_from_query(db, qry_clone_date_to_attribute_id_19(tt_name, "Fees & Indirect Costs Date"))
count = db.execute(qry_update_last_date_of_month(tt_name))
logger.info("{} updated to ensure [date] column is last day of the month\n".format(count))
cols = db.sp_columns("{}".format(tt_name))
attribute_lookup = {col: db.get_one_value(qry_get_attribute_id(), col) for col in cols}
# remove columns not matched tblAttribute
attribute_lookup = {k: v for k, v in attribute_lookup.items() if v is not None}
# back up tblInvestmentAttribute
backup_table(db, "tblInvestmentAttribute")
for attribute_name, attribute_id in attribute_lookup.items():
attribute_tt_name = TempTable.create_from_query(
db, qry_create_temp_table_for_attribute_name(tt_name, attribute_name, attribute_id)
)
logger.info(
"create table [{}] for attribute name [{}][{}] - Qty {}".format(
attribute_tt_name, attribute_name, attribute_id, len(attribute_tt_name)
)
)
count = db.execute(qry_update_DateTo_of_tbInvestmentAttribute(attribute_tt_name))
logger.info("{} updated by closing off current attributes".format(count))
count = db.execute(qry_insert_new_investment_attributes(attribute_tt_name))
logger.info("{} inserted into tblInvestmentAttribute".format(count))
assert count == len(attribute_tt_name)
logger.info("\n")
# cleaning up junk data due to re-run
count = db.execute(qry_delete_rerun_data("tblInvestmentAttribute"))
logger.info("{} deleted in tblInvestmentAttribute due to re-run".format(count))
count = db.execute(qry_regenerate_report(tt_name, code_field="code"))
logger.info("{} updated for regenerating report".format(count))
def extract(db, excel_file, sheet_name_or_idx, data_provider, output):
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
another_tt_name = TempTable.create_from_query(db, qry_get_apircode_from_citicode(tt_name))
orig_citi_codes = db.get_data('select citicode from {}'.format(tt_name))
orig_citi_codes = {code.citicode for code in orig_citi_codes}
logger.info(orig_citi_codes)
assert len(tt_name) == len(another_tt_name)
holding_tt_name = TempTable.create_from_query(db, qry_get_top_holdings(another_tt_name, data_provider))
rows = db.get_data('select * from {}'.format(holding_tt_name))
unique_citi_codes = {row[0] for row in rows}
logger.info(orig_citi_codes-unique_citi_codes)
csvwriter = csv.writer(output, lineterminator='\n')
header = 'FundProductCode,HoldingCode,HoldingName,Weight,DateFrom'
csvwriter.writerow(header.split(','))
csvwriter.writerows(rows)
def upload_top10(db, excel_file, sheet_name_or_idx, data_provider):
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
unique_tt_name = TempTable.create_from_query(db, qry_get_distinct_code_date(tt_name))
# back up ExternalData.dbo.tblUnderlyingFund
backup_table(db, 'ExternalData.dbo.tblUnderlyingFund')
count = db.execute(qry_update_fund_name_tblUnderlyingFund(unique_tt_name))
logger.info('{} fund name updated in tblUnderlyingFund'.format(count))
count = db.execute(qry_insert_new_fund_tblUnderlyingFund(unique_tt_name, data_provider))
logger.info('{} fund name inserted in tblUnderlyingFund'.format(count))
count = db.execute(qry_update_DateFrom_tblUnderlyingFund(unique_tt_name))
logger.info('{} fund name updated with latest date in tblUnderlyingFund'.format(count))
assert count == len(unique_tt_name)
# close off any open holdings for funds where changed
# back up ExternalData.dbo.tblTopHoldings
backup_table(db, 'ExternalData.dbo.tblTopHoldings')
count = db.execute(qry_update_DateTo_of_current_holdings(unique_tt_name))
logger.info('{} updated by closing off any open holdings for funds where changed'.format(count))
# add the new holding code
backup_table(db, 'ExternalData.dbo.tblHolding')
count = db.execute(qry_add_new_holding_name(tt_name, data_provider))
logger.info('{} updated by adding the new holding code'.format(count))
# add new holding
count = db.execute(qry_add_new_holdings(tt_name, data_provider))
logger.info('{} updated by adding the new holding'.format(count))
assert count == len(tt_name)
count = db.execute(qry_regenerate_report(tt_name))
logger.info('{} updated for regenerating report'.format(count))
# cleaning up junk data due to re-run
count = db.execute(qry_delete_rerun_data('ExternalData..tblTopHoldings'))
logger.info('{} deleted in ExternalData..tblTopHoldings due to re-run'.format(count))
def upload(db, excel_file, sheet_name_or_idx, data_provider, upload_type):
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
fy = helper.get_financial_year()
logger.info(tt_name)
if 'hybrids' in upload_type:
rename(db, tt_name, 'Code', 'StockCode')
rename(db, tt_name, 'Annual DPS', 'DPS')
rename(db, tt_name, '% FRANKED', 'PERCENT_FRANKED')
elif 'model' in upload_type:
rename(db, tt_name, 'Stock', 'StockCode')
rename(db, tt_name, 'FY16 DPS', 'DPS')
rename(db, tt_name, 'FY16 EPS', 'EPS')
rename(db, tt_name, 'FY16 EPS GROWTH', 'EPSGrowth')
rename(db, tt_name, '% FRANKED', 'PERCENT_FRANKED')
rename(db, tt_name, 'FY16 PER', 'PER')
elif 'ubs' in upload_type:
rename(db, tt_name, 'Franking', 'PERCENT_FRANKED')
count = db.get_one_value('''
select count(*)
from {tt_name}
where PERCENT_FRANKED > 1
'''.format(tt_name=tt_name))
if count > 0:
db.execute('''
update {tt_name}
set PERCENT_FRANKED = PERCENT_FRANKED / 100
'''.format(tt_name=tt_name))
# back up ExternalData.dbo.tblGrowthSeries
helper.backup_table(db, 'ExternalData.dbo.tblStockEarnings')
rows = db.get_data('''
select *
from ExternalData.dbo.tblStockEarnings
where investmentCode in (select top 2 stockCode from {tt_name}) and FinancialYear={fy}
'''.format(tt_name=tt_name, fy=fy))
for row in rows:
logger.info(row)
qry_update = qry_update_model_stock_code if 'model' in upload_type else qry_update_stock_code
qry_update = qry_update_ubs_stock_code if 'ubs' in upload_type else qry_update
logger.info('Data provider {}'.format(data_provider))
count = db.execute(qry_update(tt_name, fy, data_provider))
logger.info(count)
if upload_type in ['model']:
count = db.execute(qry_update(tt_name, fy, data_provider=1))
logger.info('{} updated with 1'.format(count))
count1 = db.execute(qry_delete_lonsec_if_ubs(tt_name, fy))
logger.info(str(count1)+' deleted')
logger.info('{} updated/inserted into External.dbo.tblStockEarnings'.format(count))
rows = db.get_data('''
select *
from ExternalData.dbo.tblStockEarnings
where investmentCode in (select top 2 stockCode from {tt_name}) and FinancialYear={fy}
'''.format(tt_name=tt_name, fy=fy))
for row in rows:
logger.info(row)
if count < len(tt_name):
logger.info('Some stock codes are not available on vewEquities, they are needed to added')
rows = db.get_data('''
select tt_name.StockCode
from {tt_name} tt_name
left join vewEquities ve on tt_name.StockCode = ve.StockCode
where ve.StockID is null
'''.format(tt_name=tt_name))
stocks = [row[0] for row in rows]
logger.info('They are: {}'.format(', '.join(stocks)))
def upload(db, excel_file, sheet_name_or_idx, data_provider, upload_type, enable_not_found_is_ok):
gs_table_name, table_name, key_field_code, key_field_id \
= 'tbl{upload_type}GrowthSeries tbl{upload_type}Code {upload_type}Code {upload_type}Id'\
.format(upload_type=upload_type).split(' ')
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
db.execute(qry_update_last_date_of_month(tt_name))
# back up ExternalData.dbo.tblGrowthSeries
backup_table(db, 'ExternalData.dbo.tblGrowthSeries')
count = db.execute(qry_delete_codes_in_externaldata_tblGrowthSeries(tt_name, data_provider))
logger.info('{} rows deleted in ExternalData..tblGrowthSeries'.format(count))
try:
count = db.execute(qry_add_codes_in_externaldata_tblGrowthSeries(tt_name, data_provider, value_ex_div=True))
logger.info('{} rows inserted in ExternalData..tblGrowthSeries'.format(count))
except:
count = db.execute(qry_add_codes_in_externaldata_tblGrowthSeries(tt_name, data_provider, value_ex_div=False))
logger.info('{} rows inserted in ExternalData..tblGrowthSeries'.format(count))
rows = db.get_data(qry_check_codes_not_exist_in_tblStock(tt_name))
if rows and 'investment' in upload_type:
for row in rows:
logger.info('{} does not exist in tblStock'.format(row.code))
if enable_not_found_is_ok:
logger.info('There are stock codes not exist in tblStock')
else:
raise Exception('There are stock codes not exist in tblStock')
logger.info('Before inserting data to {gs_table_name} from ExternalData..tblGrowthSeries'
.format(gs_table_name=gs_table_name))
rows = db.get_data('''
select top 1 igs.*
from {gs_table_name} igs
inner join {table_name} ic on igs.{upload_type}id = ic.{upload_type}id
where ic.{key_field}=(select top 1 code from {tt_name} order by newid())
and ic.isUsedForGrowthSeries= 1
order by igs.[date] desc
'''.format(tt_name=tt_name, table_name=table_name, key_field=key_field_code
, upload_type=upload_type, gs_table_name=gs_table_name))
logger.info(rows)
backup_table(db, gs_table_name)
db.execute(qry_refresh_data_table_GrowthSeries(table_name, tt_name, data_provider, key_field_code))
logger.info('After inserting data to {}'.format(gs_table_name))
try:
rows = db.get_data('''
select top 1 * from {gs_table_name}
where {upload_type}id=?
order by [date] desc
'''.format(upload_type=upload_type, gs_table_name=gs_table_name), rows[0][1])
logger.info(rows)
except IndexError:
logger.info("data should be All new (INSERT INSTEAD OF UPDATE)")
# now insert new codes if exist
to_be_added_stock_codes = db.get_data(qry_check_codes_not_exist_in_table(table_name, tt_name
, data_provider, key_field_code))
if to_be_added_stock_codes:
to_be_added_stock_codes = [row.code for row in to_be_added_stock_codes]
logger.info('{} do not exist in {}, need to add'.format(table_name, to_be_added_stock_codes, table_name))
count = db.execute(qry_add_new_data_table(table_name, to_be_added_stock_codes, data_provider, upload_type
, key_field_code, key_field_id))
logger.info("{} has been added in tbl{type}Code"
.format(count, type='stock' if 'investment' in upload_type else upload_type))
rows = db.get_data('''
select top 1 igs.*
from {gs_table_name} igs
inner join {table_name} ic on igs.{key_field_id} = ic.{key_field_id}
where ic.{key_field_code} = ?
and ic.isUsedForGrowthSeries= 1
order by igs.[date] desc
'''.format(gs_table_name=gs_table_name, key_field_code=key_field_code, table_name=table_name
, key_field_id=key_field_id), to_be_added_stock_codes[-1])
logger.info(rows)
# can not do asset because trigger return 0 :-(
# assert len(to_be_added_stock_codes) == count
# if 'investment' in upload_type:
# # dont need to refresh once switching over report controller from SSRS
# count = db.execute(qry_regenerate_report(tt_name))
# logger.info('{} updated for regenerating report'.format(count))
return tt_name
def upload(db, excel_file, sheet_name_or_idx, data_provider):
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
db.execute(qry_update_last_date_of_month(tt_name))
# back up ExternalData.dbo.tblGrowthSeries
backup_table(db, 'ExternalData.dbo.tblGrowthSeries')
count = db.execute(
qry_delete_codes_in_externaldata_tblGrowthSeries(
tt_name, data_provider))
logger.info(
'{} rows deleted in ExternalData..tblGrowthSeries'.format(count))
count = db.execute(
qry_add_codes_in_externaldata_tblGrowthSeries(tt_name, data_provider))
logger.info(
'{} rows inserted in ExternalData..tblGrowthSeries'.format(count))
rows = db.get_data(qry_check_codes_not_exist_in_tblStock(tt_name))
if rows:
for row in rows:
logger.info(
'{} does not exist in tblStock (due to trigger, capture only 1 message'
.format(row.code))
raise Exception('There are stock codes not exist in tblStock')
logger.info(
'Before inserting data to tblInvestmentGrowthSeries from ExternalData..tblGrowthSeries'
)
rows = db.get_data('''
select top 1 igs.*
from tblInvestmentGrowthSeries igs
inner join tblInvestmentCode ic on igs.investmentid = ic.investmentid
where ic.investmentcode=(select top 1 code from {tt_name} order by newid())
and ic.isUsedForGrowthSeries= 1
order by igs.[date] desc
'''.format(tt_name=tt_name))
logger.info(rows)
backup_table(db, 'tblInvestmentGrowthSeries')
db.execute(
qry_refresh_data_tblInvestmentGrowthSeries(tt_name, data_provider))
logger.info('After inserting data to tblInvestmentGrowthSeries'.format(
rows[0][1]))
rows = db.get_data(
'''
select top 1 * from tblInvestmentGrowthSeries
where investmentid=?
order by [date] desc
''', rows[0][1])
logger.info(rows)
# now insert new codes if exist
to_be_added_stock_codes = db.get_data(
qry_check_codes_not_exist_in_tblInvestmentCode(tt_name, data_provider))
if to_be_added_stock_codes:
to_be_added_stock_codes = [row.code for row in to_be_added_stock_codes]
logger.info('{} do not exist in tblInvestmentCode, need to add'.format(
to_be_added_stock_codes))
count = db.execute(
qry_add_new_data_tblInvestmentGrowthSeries(to_be_added_stock_codes,
data_provider))
logger.info("{} has been added in tblStockCode".format(count))
rows = db.get_data(
'''
select top 1 igs.*
from tblInvestmentGrowthSeries igs
inner join tblInvestmentCode ic on igs.investmentid = ic.investmentid
where ic.investmentcode = ?
and ic.isUsedForGrowthSeries= 1
order by igs.[date] desc
''', to_be_added_stock_codes[-1])
logger.info(rows)
# can not do asset because trigger return 0 :-(
# assert len(to_be_added_stock_codes) == count
count = db.execute(qry_regenerate_report(tt_name))
logger.info('{} updated for regenerating report'.format(count))
return tt_name