Using InvestmentFlowsIndiaΒΆ

Notebooks for this code: python; R.

"""This example illustrates how to use the InvestmentFlowsIndia data-set."""

import sys
sys.path.append("..")

import pandas as pd    
from sqlalchemy import func, and_, or_, text, Integer
from sqlalchemy.orm import aliased
from sqlalchemy.sql.expression import cast
from plutoPy.model import RoutingSession, InvestmentFlowsIndia
from datetime import date, datetime, timedelta

# get daily DII and FII flows for the last 20 days for the cash market

print("daily DII and FII flows for the last 20 days for the cash market:")

results = (RoutingSession.session.query(InvestmentFlowsIndia.DiiCashMarket.TIME_STAMP, InvestmentFlowsIndia.DiiCashMarket.SECURITY_TYPE,
                                        func.sum(InvestmentFlowsIndia.DiiCashMarket.BUY_VALUE + InvestmentFlowsIndia.FiiCashMarket.BUY_VALUE).label('BUY'), 
                                        func.sum(InvestmentFlowsIndia.DiiCashMarket.SELL_VALUE + InvestmentFlowsIndia.FiiCashMarket.SELL_VALUE).label('SELL'),
                                        func.sum(InvestmentFlowsIndia.DiiCashMarket.BUY_VALUE + InvestmentFlowsIndia.FiiCashMarket.BUY_VALUE
                                         - InvestmentFlowsIndia.DiiCashMarket.SELL_VALUE - InvestmentFlowsIndia.FiiCashMarket.SELL_VALUE).label('NET'))
            .outerjoin(InvestmentFlowsIndia.FiiCashMarket, and_(InvestmentFlowsIndia.FiiCashMarket.TIME_STAMP == InvestmentFlowsIndia.DiiCashMarket.TIME_STAMP,
                                                                InvestmentFlowsIndia.FiiCashMarket.SECURITY_TYPE == InvestmentFlowsIndia.DiiCashMarket.SECURITY_TYPE))
            .group_by(InvestmentFlowsIndia.DiiCashMarket.TIME_STAMP, InvestmentFlowsIndia.DiiCashMarket.SECURITY_TYPE)
            .order_by(InvestmentFlowsIndia.DiiCashMarket.TIME_STAMP.desc())
            .limit(20)
            .all())

for instance in results:
    print(instance)
    
# get instruments traced for DIIs in the derivative market   

print("instruments traced for DIIs in the derivative market") 

results = (RoutingSession.session.query(InvestmentFlowsIndia.DiiDerivativesMarket.SECURITY_TYPE,
                                        func.min(InvestmentFlowsIndia.DiiDerivativesMarket.TIME_STAMP).label('start_dt'),
                                        func.max(InvestmentFlowsIndia.DiiDerivativesMarket.TIME_STAMP).label('end_dt'))
            .group_by(InvestmentFlowsIndia.DiiDerivativesMarket.SECURITY_TYPE)
            .order_by(text('start_dt'))
            .all())

for instance in results:
    print(instance)
    
# get instruments traced for FIIs in the derivative market   

print("instruments traced for FIIs in the derivative market") 

results = (RoutingSession.session.query(InvestmentFlowsIndia.FiiDerivativesMarket.SECURITY_TYPE,
                                        func.min(InvestmentFlowsIndia.FiiDerivativesMarket.TIME_STAMP).label('start_dt'),
                                        func.max(InvestmentFlowsIndia.FiiDerivativesMarket.TIME_STAMP).label('end_dt'))
            .group_by(InvestmentFlowsIndia.FiiDerivativesMarket.SECURITY_TYPE)
            .order_by(text('start_dt'))
            .all())

for instance in results:
    print(instance)