Using MutualFundsIndia

Notebooks for this code: python; R.

"""This example illustrates how to use the MutualFundsIndia data-set"""

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

import pandas as pd
from sqlalchemy import func, or_, and_
from plutoPy.model import RoutingSession, MutualFundsIndia

# who are the biggest 5?

lastAumDate = RoutingSession.session.query(func.max(MutualFundsIndia.AumFundwise.PERIOD)).scalar()
print(f"{lastAumDate}")

aums = (RoutingSession.session.query(MutualFundsIndia.AumFundwise.FUND, MutualFundsIndia.AumFundwise.AVG_AUM_WO_FOFD + MutualFundsIndia.AumFundwise.AVG_AUM_FOFD)
        .filter(MutualFundsIndia.AumFundwise.PERIOD == lastAumDate)
        .order_by((MutualFundsIndia.AumFundwise.AVG_AUM_WO_FOFD + MutualFundsIndia.AumFundwise.AVG_AUM_FOFD).desc())).all()

aumDf = pd.DataFrame(aums, columns=['FUND', 'AUM']) #analyze this!
print(aumDf[0:5])

# deep dive: largest 10 mid-cap funds by AUM

lastMetaDate = RoutingSession.session.query(func.max(MutualFundsIndia.Meta.AS_OF)).scalar()
lastSwDate = RoutingSession.session.query(func.max(MutualFundsIndia.AumSchemewise.PERIOD)).scalar()
print(f"{lastMetaDate}/{lastSwDate}")

# Meta to filter for the 'Mid-Cap' category
# AumSchemewise to sort funds by AUM - largest first
# NavTimeSeries to get the start and end dates for which NAVs are available

midCaps = (RoutingSession.session.query(MutualFundsIndia.Meta.SCHEME_CODE, 
                                       MutualFundsIndia.AumSchemewise.SCHEME_NAME,
                                       MutualFundsIndia.Meta.CATEGORY, 
                                       MutualFundsIndia.AumSchemewise.AVG_AUM_WO_FOFD,
                                       func.min(MutualFundsIndia.NavTimeSeries.TIME_STAMP).label("start_dt"), 
                                       func.max(MutualFundsIndia.NavTimeSeries.TIME_STAMP).label("end_dt"))
        .join(MutualFundsIndia.AumSchemewise, MutualFundsIndia.Meta.SCHEME_CODE == MutualFundsIndia.AumSchemewise.SCHEME_CODE)
        .join(MutualFundsIndia.NavTimeSeries, MutualFundsIndia.NavTimeSeries.SCHEME_CODE == MutualFundsIndia.AumSchemewise.SCHEME_CODE)
        .filter(and_(MutualFundsIndia.AumSchemewise.PERIOD == lastSwDate,
                    MutualFundsIndia.Meta.AS_OF == lastMetaDate, 
                    MutualFundsIndia.Meta.CATEGORY == 'Mid-Cap'))
        .group_by(MutualFundsIndia.Meta.SCHEME_CODE, 
            MutualFundsIndia.AumSchemewise.SCHEME_NAME,
            MutualFundsIndia.Meta.CATEGORY, 
            MutualFundsIndia.AumSchemewise.AVG_AUM_WO_FOFD)
        .order_by(MutualFundsIndia.AumSchemewise.AVG_AUM_WO_FOFD.desc())).all()

print(f"fetched: {len(midCaps)}")

# get a pandas dataframe out of the list of tuples
mcDf = pd.DataFrame(midCaps)

pd.set_option('display.max_columns', None)
print(mcDf[0:10])

# get the NAV time-series
scode = int(mcDf.iloc[2]['SCHEME_CODE'])
navs = (RoutingSession.session.query(MutualFundsIndia.NavTimeSeries.TIME_STAMP, MutualFundsIndia.NavTimeSeries.NAV)
        .filter(MutualFundsIndia.NavTimeSeries.SCHEME_CODE == scode)
        .order_by(MutualFundsIndia.NavTimeSeries.TIME_STAMP)).all()

navDf = pd.DataFrame(navs) #analyze this!
print(navDf[0:10])