def import_prices(container):
guardian_alias = Attributes.objects.get(type='alias_type', short_name='GUARDIAN')
nav_value = Attributes.objects.get(identifier='NUM_TYPE_NAV', active=True)
final_status = Attributes.objects.get(identifier='NUM_STATUS_FINAL', active=True)
official_type = Attributes.objects.get(identifier='PRICE_TYPE_OFFICIAL', active=True)
daily = Attributes.objects.get(identifier='FREQ_DAILY', active=True)
data_provider = Attributes.objects.get(identifier='SCR_DP', active=True)
guardian = CompanyContainer.objects.get(short_name__icontains='GUARDIAN')
guardian_provider = RelatedCompany.objects.filter(company=guardian, role=data_provider)
if guardian_provider.exists():
guardian_provider = guardian_provider[0]
else:
guardian_provider = RelatedCompany()
guardian_provider.role = data_provider
guardian_provider.company = guardian
guardian_provider.save()
if container.aliases.filter(alias_type=guardian_alias).exists():
old_provider = container.associated_companies.filter(role=data_provider)
if old_provider.exists():
old_provider = old_provider[0]
container.associated_companies.remove(old_provider)
container.save()
container.associated_companies.add(guardian_provider)
container.save()
try:
track = TrackContainer.objects.get(
effective_container_id=container.id,
type__id=nav_value.id,
quality__id=official_type.id,
source__id=guardian.id,
frequency__id=daily.id,
status__id=final_status.id)
LOGGER.info("\tTrack already exists")
except:
track = TrackContainer()
track.effective_container = container
track.type = nav_value
track.quality = official_type
track.source = guardian
track.status = final_status
track.frequency = daily
track.frequency_reference = None
track.save()
all_prices = get_prices('guardian', container.aliases.get(alias_type=guardian_alias).alias_value)
all_tokens = []
for price in all_prices:
if price['prezzo']!=None:
all_tokens.append({'date': datetime.datetime.strptime(price['data_ins'], '%Y-%m-%d'), 'value': price['prezzo']})
set_track_content(track, all_tokens, True)
print all_tokens
def get_bloomberg_provider():
bloomberg_company = CompanyContainer.objects.get(name='Bloomberg LP')
data_provider = Attributes.objects.get(identifier='SCR_DP', active=True)
all_bloomberg_providers = RelatedCompany.objects.filter(company=bloomberg_company, role=data_provider)
if not all_bloomberg_providers.exists():
LOGGER.info("Creating Bloomberg LP as a data providing company.")
bloomberg_provider = RelatedCompany()
bloomberg_provider.company = bloomberg_company
bloomberg_provider.role = data_provider
bloomberg_provider.save()
elif len(all_bloomberg_providers)>1:
for index in range(1, len(all_bloomberg_providers)):
all_bloomberg_providers[index].delete()
bloomberg_provider = RelatedCompany.objects.get(company=bloomberg_company, role=data_provider)
return bloomberg_provider
def load_swm_map_file(path_to_file):
workbook = load_workbook(path_to_file)
sheet = workbook.get_sheet_by_name(name='SWM')
row_index = 1
sequoia_map = external_content.get_sequoia_map()
# Reading header
header = []
for column_index in range(1, sheet.get_highest_column() + 1):
value = sheet.cell(row = row_index, column=column_index).value
header.append(value if value!='' and value!=None else header[-1])
row_index = row_index + 1
LOGGER.info("Using the following header: " + str(header))
while row_index<=sheet.get_highest_row():
LOGGER.info("Working on " + sheet.cell(row = row_index, column=1).value)
current_name = sheet.cell(row = row_index, column=1).value
portfolio = PortfolioContainer.objects.filter(Q(name=current_name) | Q(short_name=current_name))
if portfolio.exists():
portfolio = portfolio[0]
else:
portfolio = PortfolioContainer()
portfolio.name = current_name
portfolio.short_name = current_name
portfolio.inception_date = sheet.cell(row = row_index, column=2).value
portfolio.currency = Attributes.objects.get(short_name__iexact=sheet.cell(row = row_index, column=3).value, type='currency', active=True)
portfolio.status = Attributes.objects.get(type='status', identifier='STATUS_ACTIVE', active=True)
portfolio.type = Attributes.objects.get(type='container_type', identifier='CONT_PORTFOLIO', active=True)
portfolio.save()
associated_bank = portfolio.associated_companies.filter(Q(company__name__iexact=sheet.cell(row = row_index, column=7).value) | Q(company__short_name__iexact=sheet.cell(row = row_index, column=7).value), role__identifier='SCR_BANK')
if not associated_bank.exists():
current_bank = portfolio.associated_companies.filter(role__identifier='SCR_BANK')
if current_bank.exists():
current_bank = current_bank[0]
portfolio.associated_companies.remove(current_bank)
associated_bank = CompanyContainer.objects.filter(Q(name__iexact=sheet.cell(row = row_index, column=7).value) | Q(short_name__iexact=sheet.cell(row = row_index, column=7).value))
if associated_bank.exists():
associated_bank = associated_bank[0]
else:
associated_bank = CompanyContainer()
associated_bank.name = sheet.cell(row = row_index, column=7).value
associated_bank.short_name = sheet.cell(row = row_index, column=7).value
associated_bank.status = Attributes.objects.get(type='status', identifier='STATUS_TO_BE_VALIDATED', active=True)
associated_bank.type = Attributes.objects.get(type='container_type', identifier='CONT_COMPANY', active=True)
associated_bank.save()
current_bank = RelatedCompany()
current_bank.role = Attributes.objects.get(identifier='SCR_BANK', active=True)
current_bank.company = associated_bank
current_bank.save()
portfolio.associated_companies.add(current_bank)
else:
associated_bank = associated_bank[0].company
portfolio_id = str(portfolio.id)
sequoia_map[portfolio_id] = external_content.create_sequoia_map_entry(portfolio)
strategy_profile = Attributes.objects.get(Q(short_name__iexact=sheet.cell(row = row_index, column=4).value) | Q(name__iexact=sheet.cell(row = row_index, column=4).value), Q(type='sequoia_strategy'), Q(active=True))
risk_profile = Attributes.objects.get(Q(short_name__iexact=sheet.cell(row = row_index, column=5).value) | Q(name__iexact=sheet.cell(row = row_index, column=5).value), Q(type='sequoia_risk'), Q(active=True))
jurisdiction = Attributes.objects.filter(Q(short_name__iexact=sheet.cell(row = row_index, column=6).value) | Q(name__iexact=sheet.cell(row = row_index, column=6).value), Q(type='country_iso2'), Q(active=True))
if jurisdiction.exists():
jurisdiction = jurisdiction[0]
else:
LOGGER.warn("\tJurisdiction not found [" + sheet.cell(row = row_index, column=6).value + "]")
jurisdiction = None
LOGGER.info("\tUsing key:" + portfolio_id)
sequoia_map[portfolio_id]['strategy_profile'] = strategy_profile.identifier
sequoia_map[portfolio_id]['risk_profile'] = risk_profile.identifier
sequoia_map[portfolio_id]['jurisdiction'] = jurisdiction.identifier if jurisdiction!=None else None
sequoia_map[portfolio_id]['bank'] = associated_bank.name
# Cleaning fees setup
for fee in fees_set:
sequoia_map[portfolio_id][fee] = {}
# Parsing ratios
structure_ratio = sheet.cell(row = row_index, column=8).value
sequoia_map[portfolio_id]['structure_ratio'] = structure_ratio
for index in xrange(10,sheet.get_highest_column() - 3,3):
if sheet.cell(row = row_index, column=index).value!=None and sheet.cell(row = row_index, column=index).value!='':
key = header[index-1].upper().split(' ')
charge = sequoia_dictionary[key[0]].identifier
fee = sequoia_dictionary[key[1]].identifier
if not sequoia_map[portfolio_id][fee].has_key(charge):
sequoia_map[portfolio_id][fee][charge] = []
bud = Attributes.objects.filter(active=True, type='sequoia_bud', short_name=sheet.cell(row = row_index, column=index).value)
if bud.exists():
bud = bud[0].identifier
sequoia_map[portfolio_id][fee][charge].append({'rate':sheet.cell(row = row_index, column=index + 1).value * 100.0, 'bud': bud})
else:
LOGGER.warn("\tBUD not found [" + str(sheet.cell(row = row_index, column=index).value) + "]")
row_index = row_index + 1
external_content.set_sequoia_map(sequoia_map)
