def loadStockMarketCap(self):
sql = "select symbol,marketcap from cycportfolio.[dbo].[SymbolFundamentals]"
self._dbi = MSSQLDBInterface('SFO-PROD-SQL603', 'cyccert', 'cyc',
'5432')
self._dbi.connectToDatabase()
self.symbol_marketcap_dict = self._dbi.getDictDataFromSQL(sql)
self._dbi.disconnectFromDatabase()
class StockPosition(object):
def __init__(self, symbol, shares, begindate) :
# for SHORT position: shares will be NEGATIVE number
self.symbol=symbol
self.shares=shares
self.begindate=begindate
#self.enddate=enddate
self.current_interval_id=0
self.current_positionvalue=0
self.loadSymbolDailyPricingdata(self.symbol, self.begindate)
def loadSymbolDailyPricingdata(self,symbol, begindate):
self._dbi=MSSQLDBInterface('SFO-PROD-SQL603','cyccert','cyc','5432')
self._dbi.connectToDatabase()
sql="exec cyccert.dbo.[sp_getSymbolDailyPriceDataForPortfolioTesting] '"+str(symbol)+"\',\'"+str(begindate)+"\'"
self.df_dailydata=self._dbi.getDataFrameFromSQL(sql)
self.df_dailydata.columns = ['interval_id','high','low','open','last','volume']
self.df_dailydata.index=self.df_dailydata['interval_id']
self._dbi.disconnectFromDatabase()
def getSymbolPrice(self,interval_id):
return float(self.df_dailydata.loc[([interval_id]),'last'])
def getPositionValue(self,interval_id):
self.current_interval_id=interval_id
if (interval_id in self.df_dailydata.index):
self.current_positionvalue=float(self.shares)*float(self.df_dailydata.loc[([interval_id]),'last'])
else:
#if we did not have records for the "interval_id", return prior day value
self.current_positionvalue=0
return self.current_positionvalue
def loadSymbolDailyPricingdata(self,symbol, begindate):
self._dbi=MSSQLDBInterface('SFO-PROD-SQL603','cyccert','cyc','5432')
self._dbi.connectToDatabase()
sql="exec cyccert.dbo.[sp_getSymbolDailyPriceDataForPortfolioTesting] '"+str(symbol)+"\',\'"+str(begindate)+"\'"
self.df_dailydata=self._dbi.getDataFrameFromSQL(sql)
self.df_dailydata.columns = ['interval_id','high','low','open','last','volume']
self.df_dailydata.index=self.df_dailydata['interval_id']
self._dbi.disconnectFromDatabase()
modelbegindate = (sys.argv[2])
if (len(sys.argv) >= 4):
modelenddate = (sys.argv[3])
if (len(sys.argv) >= 5):
strDataSavingDate = (sys.argv[4])
if (strDataSavingDate is not None):
datasavingdate = pd.Timestamp(
datetime.datetime.strptime(strDataSavingDate, '%Y-%m-%d').date())
#
ohlcAnalysis = CreptoOHLCAnalysis(symbol)
servername = "sfo-prod-sql603"
dbname = "cyccert"
username = "******"
userpassword = "******"
dbi = MSSQLDBInterface(servername, dbname, username, userpassword)
sql = ("exec cyccert.[dbo].[sp_getSymbolDailyData] '%s','%s','%s'") % (
symbol, modelbegindate, modelenddate)
nDataCount = ohlcAnalysis.loadDataFromMSSQL(sql, dbi)
if (nDataCount < 100):
print("Not enough data to model!")
exit(1)
print(("Succeeded to load %d data!") % (nDataCount))
#determine the dataSavingIntervalID
dataSavingIntervalID = 0
dfOHLCS = ohlcAnalysis.getOHLCS()
if (datasavingdate is None):
datasavingdate = dfOHLCS.iloc[nDataCount - 100].trade_date
class PortfolioCapitalAllocation(object):
def __init__(self, portfolioMaxExposure, stockMaxAllocation):
self.portfolioMaxExposure = portfolioMaxExposure
self.stockMaxAllocation = stockMaxAllocation
self.symbol_marketcap_dict = {}
def loadStockMarketCap(self):
sql = "select symbol,marketcap from cycportfolio.[dbo].[SymbolFundamentals]"
self._dbi = MSSQLDBInterface('SFO-PROD-SQL603', 'cyccert', 'cyc',
'5432')
self._dbi.connectToDatabase()
self.symbol_marketcap_dict = self._dbi.getDictDataFromSQL(sql)
self._dbi.disconnectFromDatabase()
# conn_dat603=pymssql.connect(server='SFO-PROD-SQL603',user='******',password='******',database='cyccert')
# cursor_dat603=conn_dat603.cursor()
# #load symbol fundamental info
# sql="select symbol,marketcap from cycportfolio.[dbo].[SymbolFundamentals]"
# cursor_dat603.execute(sql)
# symbol_marketcap=cursor_dat603.fetchall() #symbol_marketcap will be a dictonary ??
# cursor_dat603.close()
# conn_dat603.close()
# self.symbol_marketcap_dict = dict(symbol_marketcap)
# The function below will calculate the dollar investment for a stock
def __getInvestmentSizeForStock_MarketCapRuleOn2MillionPortfolio(
self, symbol):
"""get the dollar investment allocated for a stock according to the market cap rule on $M portfolio"""
allocation = 0 # 10k per billion
# if (symbol in self.symbol_marketcap_dict.keys()):
# marketcap = self.symbol_marketcap_dict[symbol]
# allocation = 1. * int(marketcap)*10000 # 10k per billion
if (symbol in self.symbol_marketcap_dict.keys()):
marketcap = self.symbol_marketcap_dict[symbol]
allocation = 1.0 * int(marketcap) * 10000 #10,000 per billion
return allocation
# The function below will calculate the dollar allocation for a particular stock for a particular portfolio
def getInvestmentSizeForStockOnPortfolio(self, symbol, portfoliovalue,
cashvalue):
"""get the dollar investment allocated for a stock for a particular portfolio """
# -- 1. apply market cap allocation rule
stockAllocation = self.__getInvestmentSizeForStock_MarketCapRuleOn2MillionPortfolio(
symbol)
# -- 1b. limit to only 15% of $2.0 Million portfolio
# select @allocation=case when @allocation>300000 then 300000 else @allocation end
if (stockAllocation > 300000):
stockAllocation = 300000
# -- 2. size up to the portfolio
# select @allocation=@allocation*@portfoliovalue/2000000.0
stockAllocation = stockAllocation * portfoliovalue / 2000000.0
if (stockAllocation > self.stockMaxAllocation * portfoliovalue):
stockAllocation = self.stockMaxAllocation * portfoliovalue
# -- 3. size down according to portfoli Max exposure rule: the allocation we calculated above is based on 200% investment
stockAllocation = stockAllocation * self.portfolioMaxExposure / 2.0
# -- 4. apply the @maxexposure rule
if (cashvalue - stockAllocation <
(1.0 - self.portfolioMaxExposure) * portfoliovalue):
stockAllocation = cashvalue - (
1.0 - self.portfolioMaxExposure) * portfoliovalue
#5 floor to 0
if (stockAllocation < 0):
stockAllocation = 0
return stockAllocation