onetime_script.py

# -*- coding: utf-8 -*-
# Boilerplate code to import settings w/o setting DJANGO_SETTINGS_MODULE
import logging
import django
import sys
import os


sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), "..", "..", "..")))
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'ycharts.settings.active')
django.setup()

from apps.companies.models import Company
from apps.fund_attributes.models import CompanyFundT25Holding, MutualFundT25Holding
from apps.mutual_funds.models import MutualFund
from ycharts.decorators import profileit, query_count

logger = logging.getLogger('apps')
logger.setLevel(logging.INFO)

logger.info("Starting onetime script {0}".format(__file__))

aapl_id = Company.objects.get(exchange_symbol='AAPL').id
msft_id = Company.objects.get(exchange_symbol='MSFT').id

@profileit
@query_count
def get_funds_with_exposure(security_id, weighting, comparitor):
    filter_params = {
        'held_company_id': security_id,
        'weighting__gte': weighting
    }

    if(comparitor == 'lte'):
        mutual_fund_exclude_ids = MutualFundT25Holding.objects.filter(**filter_params)\
            .values_list('mutual_fund_id', flat=True)
        company_fund_exclude_ids = CompanyFundT25Holding.objects.filter(**filter_params)\
            .values_list('company_id', flat=True)

        mutual_fund_securitylist_ids = MutualFund.objects.exclude(id__in=mutual_fund_exclude_ids)\
            .extra(select={'securitylist_security_id': "CONCAT('M',mutual_funds_mutualfund.security_id)"})\
            .order_by().values_list('securitylist_security_id', flat=True)
        company_fund_securitylist_ids = Company.objects.exclude(id__in=company_fund_exclude_ids)\
            .extra(select={'securitylist_security_id': "CONCAT('C',companies_company.security_id)"})\
            .order_by().values_list('securitylist_security_id', flat=True)

    else:
        mutual_fund_securitylist_ids = MutualFundT25Holding.objects.filter(**filter_params)\
            .extra(select={'securitylist_security_id': "CONCAT('M',mutual_fund_id)"})\
            .values_list('securitylist_security_id', flat=True)
        company_fund_securitylist_ids = CompanyFundT25Holding.objects.filter(**filter_params)\
            .extra(select={'securitylist_security_id': "CONCAT('C',company_id)"})\
            .values_list('securitylist_security_id', flat=True)

    return list(company_fund_securitylist_ids) + list(mutual_fund_securitylist_ids)


get_funds_with_exposure(aapl_id, 3.0, 'gte')
get_funds_with_exposure(msft_id, 3.0, 'lte')

logger.info("Finished Running onetime script {0}".format(__file__))

Security Exposure Screener Filter

### Overview
Below is an example onetime script on how we are doing the querying for Holding for the security exposure filter. 

Gotta Fix 2 Issues first though:

1. Holdings as of now are linked on only `Company` objects and not able to link to `MutualFund`.
2. We match on `exchange_symbol` rather than cusip cuz we couldn't as of now and we gotta change that.