def execute(self, symbols_and_signs_dataframe, startdate, enddate,
permutations, annualized_or_cumulative):
import config
import mytools
import datetime
import os
import outputefficientfrontier as oef
a = list([
symbols_and_signs_dataframe.index.tolist(),
symbols_and_signs_dataframe['longshort'].tolist()
])
symbols_and_signs_list = zip(*a)
#print symbols_and_signs_list
#stop
#symbols = [i[0] for i in symbols_and_signs_list]
#longshortlist = [i[1] for i in symbols_and_signs_list]
#symbols_and_signs_list = xxx
o = oef.output(symbols_and_signs_list, startdate, enddate,
permutations, annualized_or_cumulative)
d = o.DictionaryOfOutputFiles
for k, v in d.items():
#print k, v
(f_path, filename) = os.path.split(v)
date14 = filename.split(' ')[0]
print date14
mycachefolder = config.mycachefolder
mytools.general().make_sure_path_exists(mycachefolder)
#date14 = str(datetime.datetime.now().strftime("%Y%m%d%H%M%S"))
cachedfilepathname = mycachefolder
cachedfilepathname = os.path.join(cachedfilepathname,
date14 + ' compiled.xlsx')
import xlsxwriter
import glob
import csv
import numbers
workbook = xlsxwriter.Workbook(cachedfilepathname,
{'strings_to_numbers': True})
#for filename in glob.glob("*.csv"):
for k, v in d.items():
#print k, v
(f_path, filename) = os.path.split(v)
worksheetname = str(filename.split('.')[0])
worksheetname = worksheetname.split(' ')[1]
ws = workbook.add_worksheet(worksheetname)
spamReader = csv.reader(open(v, 'rb'), delimiter=',')
row_count = 0
#print filename
for row in spamReader:
for col in range(len(row)):
n = row[col]
ws.write(row_count, col, n)
row_count += 1
workbook.close()
self.PathnameToCompiledWorkbook = cachedfilepathname
print 'you can find your compileclass file here: ', cachedfilepathname
"""
Created on Wed Jul 29 17:31:47 2015
@author: justin.malinchak
"""
# -*- coding: utf-8 -*-
"""
Created on Wed Jul 29 17:18:11 2015
@author: justin.malinchak
"""
import outputefficientfrontier as oef
# # Use this one to test Yahoo
# o = output( list_of_symbols = ['WMT','NKE','T','MCD','JPM','^RUT']
# , startdate_string = '2014-12-31'
# , enddate_string = '2016-03-31'
# , period = 'monthly'
# , pctchangeorlogreturn = 'pctchange'
# , source = 'Yahoo'
# )
o = oef.output(['Mellon Capital Large Cap Core','Boston Co US Mid Cap Growt','Harding Loevner Glob Eq ADR','Logan Capital Concentrated Val']
, startdate_string = '2012-12-31'
, enddate_string = '2016-03-31' #'2013-12-31'
, period='Monthly'
, pctchangeorlogreturn = 'pctchange'
, source='local'
)
def tryit(self
, list_of_symbols
, startdatestring
, enddate_string
, returnsmonthlyordaily
, pctchangeorlogreturn
, numberofpermutations , optimalpctasdecimal
):
import xlsxwriter
# ##########
# Date setup
import datetime
today_datetime = datetime.datetime.today()
#today_date = datetime.date.today()
#print today_date
today_datetime_string_forfilename = today_datetime.strftime('%Y%m%d%H%M%S')
print today_datetime_string_forfilename
finalfilename = today_datetime_string_forfilename+'.xlsx'
inputfilename = today_datetime_string_forfilename+'-temp.xlsx'
#self.createworkbook(config.myoutputpath+'\\'+inputfilename)
# Create an new Excel file and add a worksheet.
#if os.path.isfile(config.myoutputpath+'\\'+inputfilename):
# ------------------------------------
# Get the data first
import outputefficientfrontier as oef
o = oef.output( list_of_symbols
, startdatestring
, enddate_string
, returnsmonthlyordaily
, pctchangeorlogreturn
)
# for index, row in df.iterrows():
# try:
# fartomidpricechangedelta = (row['priceDaysBackMid'] - row['priceDaysBackFar']) / row['priceDaysBackFar']
# --------------------------------
# Then open the workbook here
#from win32com.client import Dispatch
#xlApp = Dispatch("Excel.Application")
#xlApp.Visible = 1
#workbook = xlApp.Workbooks.Open(config.myoutputpath+'\\'+inputfilename)
workbook = xlsxwriter.Workbook(config.myoutputpath+'\\'+inputfilename)
formatdate = workbook.add_format()
formatcolumnheader = workbook.add_format()
formatdate.set_num_format('yyyy-mm-dd') # Format string.
formatcolumnheader.set_num_format(0x0F) # Format index.
# --------------------------------
# Then populate the worksheet here - returns
worksheet = workbook.add_worksheet()
worksheet.name = 'pricehistory'
df = o.EfficientFrontierObject.PriceHistoryDataframe
#print df
titlesrow = 3
titlescolumn = 3
rowid = 0
colid = 0
for cname in df.columns:
#print 'column',cname
colid = colid + 1
worksheet.write(titlesrow,titlescolumn+colid,cname)
#worksheet.Cells(titlesrow,titlescolumn+colid).Value = cname
for index, row in df.iterrows():
rowid = rowid + 1
worksheet.write(titlesrow + rowid,titlescolumn,index,formatdate)
#worksheet.Cells(titlesrow + rowid,titlescolumn).Value = index
#print 'x'
colid = 0
#for cl in row:
for cname in df.columns:
colid = colid + 1
#print rowid,colid,cl
#print str(df.loc[index][cname]) == str('nan')
if not str(df.loc[index][cname]) == str('nan'):
worksheet.write(titlesrow+rowid,titlescolumn+colid,df.loc[index][cname])
#worksheet.Cells(titlesrow+rowid,titlescolumn+colid).Value = df.loc[index][cname]
# --------------------------------
# Then populate the worksheet here - returns
worksheet = workbook.add_worksheet()
worksheet.name = 'returns'
df = o.EfficientFrontierObject.ReturnsDataframe
#print df
titlesrow = 3
titlescolumn = 3
rowid = 0
colid = 0
for cname in df.columns:
#print 'column',cname
colid = colid + 1
worksheet.write(titlesrow,titlescolumn+colid,cname)
#worksheet.Cells(titlesrow,titlescolumn+colid).Value = cname
for index, row in df.iterrows():
rowid = rowid + 1
worksheet.write(titlesrow + rowid,titlescolumn,index,formatdate)
#worksheet.Cells(titlesrow + rowid,titlescolumn).Value = index
#print 'x'
colid = 0
#for cl in row:
for cname in df.columns:
colid = colid + 1
#print rowid,colid,cl
#print str(df.loc[index][cname]) == str('nan')
if not str(df.loc[index][cname]) == str('nan'):
worksheet.write(titlesrow+rowid,titlescolumn+colid,df.loc[index][cname])
#worksheet.Cells(titlesrow+rowid,titlescolumn+colid).Value = df.loc[index][cname]
# --------------------------------
# Then populate the worksheet here - annualizedreturnseries
worksheet = workbook.add_worksheet()
worksheet.name = 'annualizedreturns'
#print df
titlesrow = 3
titlescolumn = 3
rowid = 0
colid = 0
worksheet.write(titlesrow+1,titlescolumn,'annualizedreturns')
for cname in o.EfficientFrontierObject.ReturnsDataframe:
#print 'column',cname
colid = colid + 1
worksheet.write(titlesrow,titlescolumn+colid,cname)
annualizedreturnvalue = o.EfficientFrontierObject.AnnualizedReturnsSeries[cname]
worksheet.write(titlesrow+1,titlescolumn+colid,annualizedreturnvalue)
#worksheet.Cells(titlesrow,titlescolumn+colid).Value = cname
# ---------------------------------------------------
# Then populate the worksheet here - covariancematrix
worksheet = workbook.add_worksheet()
worksheet.name = 'covariancematrix'
df = o.EfficientFrontierObject.covariancematrix()
titlesrow = 3
titlescolumn = 3
rowid = 0
colid = 0
for cname in df.columns:
#print 'column',cname
colid = colid + 1
worksheet.write(titlesrow,titlescolumn+colid, cname)
#worksheet.Cells(titlesrow,titlescolumn+colid).Value = cname
for index, row in df.iterrows():
rowid = rowid + 1
worksheet.write(titlesrow + rowid,titlescolumn,index)
#worksheet.Cells(titlesrow + rowid,titlescolumn).Value = index
#print 'x'
colid = 0
for cl in row:
colid = colid + 1
#print rowid,colid,cl
worksheet.write(titlesrow+rowid,titlescolumn+colid,cl)
#worksheet.Cells(titlesrow+rowid,titlescolumn+colid).Value = cl
# ----------------------------------------------------
# Then populate the worksheet here - correlationmatrix
worksheet = workbook.add_worksheet()
worksheet.name = 'correlationmatrix'
df = o.EfficientFrontierObject.correlationmatrix()
titlesrow = 3
titlescolumn = 3
rowid = 0
colid = 0
for cname in df.columns:
#print 'column',cname
colid = colid + 1
worksheet.write(titlesrow,titlescolumn+colid, cname)
#worksheet.Cells(titlesrow,titlescolumn+colid).Value = cname
for index, row in df.iterrows():
rowid = rowid + 1
worksheet.write(titlesrow + rowid,titlescolumn,index)
#worksheet.Cells(titlesrow + rowid,titlescolumn).Value = index
#print 'x'
colid = 0
for cl in row:
colid = colid + 1
#print rowid,colid,cl
worksheet.write(titlesrow+rowid,titlescolumn+colid,cl)
#worksheet.Cells(titlesrow+rowid,titlescolumn+colid).Value = cl
# ----------------------------------------------------
# Build the drawsail object
drawsailfilepath = o.drawsail(numberofpermutations , optimalpctasdecimal )
# ----------------------------------------------------
# Then populate the worksheet here - PermutationsDataframe
worksheet = workbook.add_worksheet()
worksheet.name = 'permutations'
df = o.PermutationsDataframe
#print df
titlesrow = 3
titlescolumn = 3
rowid = 0
colid = 0
for cname in df.columns:
#print 'column',cname
colid = colid + 1
worksheet.write(titlesrow,titlescolumn+colid, cname)
#worksheet.Cells(titlesrow,titlescolumn+colid).Value = cname
for index, row in df.iterrows():
rowid = rowid + 1
worksheet.write(titlesrow + rowid,titlescolumn,index)
#worksheet.Cells(titlesrow + rowid,titlescolumn).Value = index
#print 'x'
colid = 0
for cl in row:
colid = colid + 1
#print rowid,colid,cl
worksheet.write(titlesrow+rowid,titlescolumn+colid,cl)
#worksheet.Cells(titlesrow+rowid,titlescolumn+colid).Value = cl
# ----------------------------------------------------
# Then populate the worksheet here - drawsail
worksheet = workbook.add_worksheet()
worksheet.name = 'drawsail'
#print '---- drawsail filepath -----'
#print drawsailfilepath
# To set column width
#worksheet.set_column('B:B', 30)
# Insert an image.
worksheet.insert_image('D4', drawsailfilepath)
# ----------------------------------------------------
# Close the workbook and rename it properly
workbook.close() # see note 1
#xlApp.Quit()
#xlApp.Visible = 0 # see note 2
#del xlApp
os.rename(config.myoutputpath+'\\'+inputfilename,config.myoutputpath+'\\'+finalfilename)
def tryit(self, list_of_symbols, startdatestring, returnsmonthlyordaily):
# ##########
# Date setup
import datetime
today_datetime = datetime.datetime.today()
#today_date = datetime.date.today()
#print today_date
today_datetime_string = today_datetime.strftime('%Y%m%d%H%M%S')
print today_datetime_string
finalfilename = today_datetime_string + '.xls'
inputfilename = today_datetime_string + '-temp.xls'
self.createworkbook(config.myoutputpath + '\\' + inputfilename)
if os.path.isfile(config.myoutputpath + '\\' + inputfilename):
# ------------------------------------
# Get the data first
import outputefficientfrontier as oef
o = oef.output(list_of_symbols, startdatestring,
returnsmonthlyordaily)
# for index, row in df.iterrows():
# try:
# fartomidpricechangedelta = (row['priceDaysBackMid'] - row['priceDaysBackFar']) / row['priceDaysBackFar']
# --------------------------------
# Then open the workbook here
from win32com.client import Dispatch
xlApp = Dispatch("Excel.Application")
xlApp.Visible = 1
wb = xlApp.Workbooks.Open(config.myoutputpath + '\\' +
inputfilename)
# --------------------------------
# Then populate the worksheet here
df = o.EfficientFrontierObject.ReturnsDataframe
#print df
titlesrow = 3
titlescolumn = 3
rowid = 0
colid = 0
for cname in df.columns:
#print 'column',cname
colid = colid + 1
wb.Sheets('returns').Cells(titlesrow,
titlescolumn + colid).Value = cname
for index, row in df.iterrows():
rowid = rowid + 1
wb.Sheets('returns').Cells(titlesrow + rowid,
titlescolumn).Value = index
#print 'x'
colid = 0
#for cl in row:
for cname in df.columns:
colid = colid + 1
#print rowid,colid,cl
#print str(df.loc[index][cname]) == str('nan')
if not str(df.loc[index][cname]) == str('nan'):
wb.Sheets('returns').Cells(
titlesrow + rowid,
titlescolumn + colid).Value = df.loc[index][cname]
# --------------------------------
# Then populate the worksheet here - covariancematrix
df = o.EfficientFrontierObject.covariancematrix()
titlesrow = 3
titlescolumn = 3
rowid = 0
colid = 0
for cname in df.columns:
#print 'column',cname
colid = colid + 1
wb.Sheets('covariancematrix').Cells(titlesrow, titlescolumn +
colid).Value = cname
for index, row in df.iterrows():
rowid = rowid + 1
wb.Sheets('covariancematrix').Cells(titlesrow + rowid,
titlescolumn).Value = index
#print 'x'
colid = 0
for cl in row:
colid = colid + 1
#print rowid,colid,cl
wb.Sheets('covariancematrix').Cells(
titlesrow + rowid, titlescolumn + colid).Value = cl
wb.Close(SaveChanges=1) # see note 1
xlApp.Quit()
xlApp.Visible = 0 # see note 2
del xlApp
os.rename(config.myoutputpath + '\\' + inputfilename,
config.myoutputpath + '\\' + finalfilename)
"""
import outputefficientfrontier as oef
# # Use this one to test Yahoo
# o = output( list_of_symbols = ['WMT','NKE','T','MCD','JPM','^RUT']
# , startdate_string = '2014-12-31'
# , enddate_string = '2016-03-31'
# , period = 'monthly'
# , pctchangeorlogreturn = 'pctchange'
# , source = 'Yahoo'
# )
o = oef.output(
['Hilton Capital YP', 'Blue Shores Cap GLSE'],
startdate_string='2012-12-31',
enddate_string='2016-03-31' #'2013-12-31'
,
period='Monthly',
pctchangeorlogreturn='pctchange',
source='local')
print o.EfficientFrontierObject.correlationmatrix()
print o.EfficientFrontierObject.covariancematrix
print o.EfficientFrontierObject.ReturnsDataframe
#print ' ---- here is just one random weight Series... ----'
#print o.EfficientFrontierObject.portfolioriskreturnrandomweight()
print o.drawsail(5000, 0.90)
'''
1919 Inv Csl Institutional Eq
1919 Inv Csl SRI SRI Equity
1919 Inv Csl SRI SRI Growth Eq
1919 Inv Csl SRI SRI Intl ADR EQ
print 'enddate_string', enddate_string
period = sys.argv[4]
print 'period', period
pctchangeorlogreturn = sys.argv[5]
print 'pctchangeorlogreturn', pctchangeorlogreturn
source = sys.argv[6]
print 'source', source
permutations = sys.argv[7]
print 'permutations', permutations
import outputefficientfrontier as oef
oef.output(
json_string_of_symbols_and_weights=json_string_of_symbols_and_weights,
startdate_string=startdate_string,
enddate_string=enddate_string,
period=period,
pctchangeorlogreturn=pctchangeorlogreturn,
source=source)
print oef.drawsail(permutations, 0.92)
#
# json_string = '{"Granite Partners Small Core Plus":[0.5,0.2],"Harding Loevner Glob Eq ADR":[0.5,0.1],"Logan Capital Concentrated Val":[0.5,0.1],"Hilton Capital YP":[0.4,0.05]}'
# #mySymbolsDict = json.loads(json_string) # obj now contains a dict of the data
# #print mySymbolsDict
# import outputefficientfrontier
# o = outputefficientfrontier.output( json_string
# , startdate_string = '2013-12-31'
# , enddate_string = '2016-03-31' #'2013-12-31'
# , period='Monthly'
# , pctchangeorlogreturn = 'pctchange'
