Beispiel #1
0
 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()
Beispiel #2
0
    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()
Beispiel #3
0
    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")