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")