Using Currencies

Notebooks for this code: python; R.

"""This example illustrates how to use the Currencies 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, Currencies
from datetime import date, datetime, timedelta

print("AlphaVantage end-of-day pairs:")

results = (RoutingSession.session.query(Currencies.AvEodTimeSeries.SYMBOL, 
                                        func.min(Currencies.AvEodTimeSeries.TIME_STAMP).label('start_dt'), 
                                        func.max(Currencies.AvEodTimeSeries.TIME_STAMP).label('end_dt'))
            .group_by(Currencies.AvEodTimeSeries.SYMBOL)
            .order_by(text('start_dt'))).all()

for instance in results:
    print(instance)

######################################################


# get traded futures pairs

print("traded futures pairs:")

results = (RoutingSession.session.query(Currencies.NseFuturesTimeSeries.SYMBOL, 
                                        func.min(Currencies.NseFuturesTimeSeries.TIME_STAMP).label('start_dt'), 
                                        func.max(Currencies.NseFuturesTimeSeries.TIME_STAMP).label('end_dt'))
            .group_by(Currencies.NseFuturesTimeSeries.SYMBOL)
            .order_by(text('start_dt'))
            .all())

for instance in results:
    print(instance)
    
# get the latest USDINR option chain for the nearest expiry    

print("latest USDINR option chain for the nearest expiry:")

end_dt = RoutingSession.session.query(func.max(Currencies.NseOptionsTimeSeries.TIME_STAMP)).scalar() 

expiry = (RoutingSession.session.query(func.min(Currencies.NseOptionsTimeSeries.EXPIRY))
          .filter(and_(Currencies.NseOptionsTimeSeries.SYMBOL == "USDINR",
                       Currencies.NseOptionsTimeSeries.TIME_STAMP == end_dt))
          .scalar())

results = (RoutingSession.session.query(Currencies.NseOptionsTimeSeries)
      .filter(and_(Currencies.NseOptionsTimeSeries.SYMBOL == "USDINR", 
                   Currencies.NseOptionsTimeSeries.EXPIRY == expiry,
                   Currencies.NseOptionsTimeSeries.TIME_STAMP == end_dt))
      .order_by(Currencies.NseOptionsTimeSeries.STRIKE, Currencies.NseOptionsTimeSeries.OTYPE)
      .all())

for instance in results:
    print(instance)
    
# get the currencies tracked by AlphaVantage end-of-day

print("AlphaVantage end-of-day pairs:")

results = (RoutingSession.session.query(Currencies.AvEodTimeSeries.SYMBOL, 
                                        func.min(Currencies.AvEodTimeSeries.TIME_STAMP).label('start_dt'), 
                                        func.max(Currencies.AvEodTimeSeries.TIME_STAMP).label('end_dt'))
            .group_by(Currencies.AvEodTimeSeries.SYMBOL)
            .order_by(text('start_dt'))
            .all())

for instance in results:
    print(instance)
    

# get the currencies tracked by AlphaVantage 30-min bars
    
print("AlphaVantage 30-min bars:")

results = (RoutingSession.session.query(Currencies.Av30minTimeSeries.SYMBOL, 
                                        func.min(Currencies.Av30minTimeSeries.TIME_STAMP).label('start_dt'), 
                                        func.max(Currencies.Av30minTimeSeries.TIME_STAMP).label('end_dt'),
                                        func.count(Currencies.Av30minTimeSeries.TIME_STAMP).label('count'))
            .group_by(Currencies.Av30minTimeSeries.SYMBOL)
            .order_by(text('count desc'))
            .all())

for instance in results:
    print(instance)