def toExcel(self):
#Instantiate win32com client excel object and create common objects
xl = Dispatch("Excel.Application")
xl.Visible = True
xl.Workbooks.Add()
wb = xl.ActiveWorkbook
shts = wb.Sheets
#Check for file with current path and name, change it if need be, save it
checker = CheckFilename(self.o_path, self.filename)
filename = checker.checkName()
wb.SaveAs(filename)
shts("Sheet1").Delete
shts("Sheet2").Delete
shts("Sheet3").Delete
keys = self.output.keys()
for sheet in keys:
xl.Worksheets.Add().Name = sheet
sht = shts(sheet)
r,c = 1,1
export = self.output[sheet]
for row in export:
c = 1
for cell in row:
sht.Cells(r,c).Value = cell
c+=1
r+=1
xl.ActiveWorkbook.Save()
def toExcel(self):
#Instantiate win32com client excel object and create common objects
xl = Dispatch("Excel.Application")
xl.Visible = True
xl.Workbooks.Add()
wb = xl.ActiveWorkbook
shts = wb.Sheets
#Check for file with current path and name, change it if need be, save it
checker = CheckFilename(self.o_path, self.filename)
filename = checker.checkName()
wb.SaveAs(filename)
shts("Sheet1").Delete
shts("Sheet2").Delete
shts("Sheet3").Delete
keys = self.output.keys()
for sheet in keys:
xl.Worksheets.Add().Name = sheet
sht = shts(sheet)
r, c = 1, 1
export = self.output[sheet]
for row in export:
c = 1
for cell in row:
sht.Cells(r, c).Value = cell
c += 1
r += 1
xl.ActiveWorkbook.Save()
def generatePGReport(self,pg_fund_ids,pg_name,as_of_date=date.today(),date_ranges="DEFAULT",only_complete_data=1,include_indices=False,calculate_rankings=False,backstop_only=False):
mcpfai_funds = []
target_funds = []
funds_period_all = [[],[],[]]
funds_period_complete = [[],[],[]]
funds_period_1mo = [[],[],[]]
funds_period_2mo = [[],[],[]]
funds_period_other = [[],[],[]]
if backstop_only:
self.refreshFundInfo("PeerGroup",pg_fund_ids,include_indices)
pg_name = pg_name.replace("/","-")
as_of_date = self.setMiddleMonth(as_of_date)
custom_dates = True
if date_ranges == "DEFAULT":
custom_dates = False
end_date = self.setMiddleMonth(as_of_date)
report_dates = [[self.trailingYearDate(end_date,1),end_date],[self.trailingYearDate(end_date,3),end_date],[self.trailingYearDate(end_date,5),end_date]]
else:
report_dates = date_ranges
for id in pg_fund_ids:
if id in self.fund_index.keys():
target_funds.append(self.fund_index[id])
elif str(id) in self.fund_index.keys():
target_funds.append(self.fund_index[str(id)])
elif unicode(str(id)) in self.fund_index.keys():
target_funds.append(self.fund_index[unicode(str(id))])
if include_indices:
mcpfai_funds = self.MCPFAI_funds
for fund in mcpfai_funds:
if not self.equalOrAfter(fund.DMRR, as_of_date) and fund.Signal != "-":
fund.DMRR = as_of_date
target_funds.extend(mcpfai_funds)
for fund in target_funds:
for p_num in range(0,3):
if self.equalOrBefore(fund.incept_date, report_dates[p_num][0]):
if self.equalOrAfter(fund.DMRR, report_dates[p_num][1]):
funds_period_all[p_num].append(fund)
funds_period_complete[p_num].append(fund)
elif self.equalOrAfter(fund.DMRR, self.getMonthBefore(report_dates[p_num][1])):
funds_period_all[p_num].append(fund)
funds_period_1mo[p_num].append(fund)
elif self.equalOrAfter(fund.DMRR, self.getMonthBefore(self.getMonthBefore(report_dates[p_num][1]))):
funds_period_all[p_num].append(fund)
funds_period_2mo[p_num].append(fund)
else:
funds_period_other[p_num].append(fund)
for p_num in range(0,3):
self.loadStats(funds_period_complete[p_num],p_num,report_dates[p_num][0],report_dates[p_num][1],backstop_only)
if only_complete_data == 2:
self.loadStats(funds_period_1mo[p_num],p_num,report_dates[p_num][0],report_dates[p_num][1],backstop_only)
self.loadStats(funds_period_2mo[p_num],p_num,report_dates[p_num][0],report_dates[p_num][1],backstop_only)
classes = ["Peer Group"]
if include_indices:
classes.append("MCP Fund or Index")
output_funds = {}
output = []
for cls in classes:
output_funds[cls] = [[],[],[]]
fund_group = []
if only_complete_data == 1:
fund_group = funds_period_complete
elif only_complete_data == 2:
fund_group = funds_period_all
if include_indices:
for p_num in range(0,3):
for fund in fund_group[p_num]:
if fund.Class != "MCP Fund or Index":
output_funds["Peer Group"][p_num].append(fund)
else:
output_funds["MCP Fund or Index"][p_num].append(fund)
else:
for p_num in range(0,3):
for fund in fund_group[p_num]:
output_funds["Peer Group"][p_num].append(fund)
if calculate_rankings:
for cls in output_funds.keys():
for p_num in range(0,3):
if len(output_funds[cls][p_num]) > 0:
self.calcRankings(output_funds[cls][p_num],p_num)
border_cols = [6,8,10,12,14,16,18,22,26,30,36,40,42,44,46]
hidden_cols = [5,6,8,10,12,14,16,18,19,20,21,22,24,26,28,30,31,32,33,34,35,36,37,38,39,40,42,44,46,47,48,49]
trailing_tags = ["1 Yr", "3 Yr", "5 Yr"]
trailing_headings = ["Trailing 1-Year","Trailing 3-Year","Trailing 5-Year"]
sect_size = []
sect_corners = []
for cls_var in range(0,len(classes)):
cls = classes[cls_var]
sect_size.append([0,0,0])
output.append([[[],[]],[[],[]],[[],[]]])
for p_num in range(0,3):
output_funds[cls][p_num] = sorted(output_funds[cls][p_num], key=attrgetter('Name'))
sect_size[cls_var][p_num] = len(output_funds[cls][p_num])
for fund in output_funds[cls][p_num]:
tmp_row = []
disp_att = []
tmp_row.append(fund.Name)
if cls == "MCP Fund or Index":
tmp_row.append(" ")
elif cls == "B Low Vol":
tmp_row.append("BLV")
else:
tmp_row.append(fund.Class)
if cls == "MCP Fund or Index":
tmp_row.append(" ")
else:
tmp_row.append(fund.StrengthRating)
tmp_row.append(fund.incept_date)
mmr_str = self.months[fund.DMRR.month%12] + "'" + str(fund.DMRR.year - 2000)
tmp_row.append(mmr_str)
if custom_dates:
tmp_row.append("Custom")
else:
tmp_row.append(trailing_tags[p_num])
for s_var in range(0,len(fund.stats[p_num])):
tmp_row.append(fund.stats[p_num][s_var])
if calculate_rankings:
tmp_row.append(fund.stat_ranks[p_num][s_var])
else:
tmp_row.append("")
if fund.IL == "Invested":
disp_att.append(True)
else:
disp_att.append(False)
if only_complete_data == 2 and not self.compareMMRandPeriodEnd(mmr_str, report_dates[p_num][1]):
disp_att.append(True)
else:
disp_att.append(False)
output[cls_var][p_num][0].append(tuple(tmp_row))
output[cls_var][p_num][1].append(disp_att)
if len(output_funds[cls][p_num]) == 0:
output[cls_var][p_num][0].append(tuple([" "," "]))
gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 5)
xl = Dispatch("Excel.Application")
xl.Visible = True
if include_indices:
xl.Workbooks.Open(os.getcwd() + "\\macros\\templates\\template_pg_wmcpfai.xls")
else:
xl.Workbooks.Open(os.getcwd() + "\\macros\\templates\\template_pg.xls")
ex_writer = ExcelWriter(xl)
wb = xl.ActiveWorkbook
shts = wb.Sheets
cur_dir = os.getcwd()
xl.VBE.ActiveVBProject.VBComponents.Import(cur_dir + "\macros\StatsReportFormatting.bas")
xl.VBE.ActiveVBProject.VBComponents.Import(cur_dir + "\macros\SortUF.frm")
file_date = self.months[as_of_date.month%12] + "'" + str(as_of_date.year - 2000)
filename = "Statistics Report " + file_date + " - " + pg_name
checker = CheckFilename(self.dir_path, filename)
filename = checker.checkName()
xl.ActiveWorkbook.SaveAs(filename)
sht = shts("Notes")
sht.Activate()
non_current_funds = {}
if only_complete_data == 1:
sht.Cells(1,1).Value = "The following funds did not have returns for the current month. Consequently, they are not included in this report."
for p_num in range(0,3):
for f in funds_period_1mo[p_num]:
non_current_funds[f.ID] = f
for f in funds_period_2mo[p_num]:
non_current_funds[f.ID] = f
for f in funds_period_other[p_num]:
non_current_funds[f.ID] = f
elif only_complete_data == 2:
sht.Cells(1,1).Value = "The following funds did not have returns for the past three months. Consequently, they are not included in this report."
for p_num in range(0,3):
for f in funds_period_other[p_num]:
non_current_funds[f.ID] = f
non_current_funds = sorted(non_current_funds.values(), key=attrgetter('Name'))
for fund_num in range(0,len(non_current_funds)):
fund = non_current_funds[fund_num]
sht.Cells(3 + fund_num,1).Value = fund.Name
sht = shts("Report")
sht.Name = pg_name
title_str = self.months[as_of_date.month%12] + "'" + str(as_of_date.year - 2000) + " " + pg_name + " Funds"
sht.Cells(2,45).Value = title_str
comment_str = ""
if only_complete_data == 1:
comment_str = "*Only funds with complete returns are included"
elif only_complete_data == 2:
comment_str = "*All funds, despite having complete returns or not, are included"
t = datetime.now()
time_str = t.strftime("Generated on %m/%d/%Y at %I:%M:%S %p")
cur_offset = 7
for sect_num in range(0,len(classes)):
for p_num in range(0,3):
if custom_dates:
sht.Cells(cur_offset-2,1).Value = self.periodName(report_dates[p_num][0], report_dates[p_num][1])
else:
sht.Cells(cur_offset-2,1).Value = trailing_headings[p_num]
if sect_size[sect_num][p_num] == 0:
cur_offset += 1
elif sect_size[sect_num][p_num] > 1:
p_str = str(cur_offset)+":"+str(cur_offset+sect_size[sect_num][p_num]-2)
sht.Rows(p_str).Insert()
ex_writer.addMultipleRows(cur_offset-1,output[sect_num][p_num][0],1,sht)
for row_var in range(0,len(output[sect_num][p_num][1])):
atts = output[sect_num][p_num][1][row_var]
row_num = (cur_offset-1) + row_var
if atts[0]:
sht.Cells(row_num,1).Font.ColorIndex = 11
if atts[1]:
sht.Rows(row_num).Font.Italic = True
cur_offset += (sect_size[sect_num][p_num]+1)
if calculate_rankings:
for col in border_cols:
tmp_range = ex_writer.getRangeByCells(((cur_offset-sect_size[sect_num][0]-sect_size[sect_num][1]-sect_size[sect_num][2]-7),col),((cur_offset-3),col),sht)
ex_writer.formatRange(tmp_range,"style_border_right")
sht.Cells(cur_offset-2,1).Value = comment_str
sht.Cells(cur_offset-2,45).Value = time_str
cur_offset += 3
big_col_range = sht.Range(sht.Columns(3),sht.Columns(4))
big_col_range = sht.Range(big_col_range,sht.Columns(5)).EntireColumn.AutoFit()
xl.Run("FinalFormatting")
big_range = sht.Columns(hidden_cols[0])
for col in hidden_cols[1:]:
big_range = xl.Union(big_range,sht.Columns(col))
big_range.EntireColumn.Hidden = True
if calculate_rankings:
xl.Run("Hotkeys_wR")
else:
xl.Run("Hotkeys_woR")
xl.ActiveWorkbook.Save()
xl.Run("UserFormTimer")
