def WriteLookupWorksheet(self, workbook, formats, startRow=0, startCol=0): myRow = startRow myColumn = startCol worksheet = workbook.add_worksheet("Lookups") keyName = "Lookup Key" ciKey = common_xls_formats.ColumnInfo(worksheet, keyName, myColumn, 1, 1, 100) ciKey.columnWrite(myRow, myColumn, keyName, "text", formats.headerFormat()) valueName = "Lookup Value" ciValue = common_xls_formats.ColumnInfo(worksheet, valueName, myColumn + 1, 1, 1, 100) ciValue.columnWrite(myRow, myColumn + 1, valueName, "text", formats.headerFormat(), True) myRow = myRow + 1 myKeys = self.lookups.keys() for k in sorted(myKeys): v = self.lookups[k] ciKey.columnWrite(myRow, myColumn, k, "text", formats.textFormat(myRow)) ciValue.columnWrite(myRow, myColumn + 1, v, "text", formats.textFormat(myRow)) myRow = myRow + 1 ciKey.columnSetSize(1.4) ciValue.columnSetSize(1.4)
def writeTransactionWorksheetHeaders(self, worksheet, startRow=0, startColumn=0): self.transColumns.clear() myCol = startColumn # headers = TransactionHeaders() for h in self.headers: ci = common_xls_formats.ColumnInfo(worksheet, h, myCol) self.transColumns[myCol] = ci ci.columnWrite(startRow, myCol, h, "text", self.formats.headerFormat(), True) myCol = myCol + 1
def printEntries(worksheet, entries, formats, startRow=0, startColumn=0): entryColumnInfo = bdata.CreateEntryTypes() myColumn = startColumn myRow = startRow for ec in entryColumnInfo: ec.ColumnInfo = common_xls_formats.ColumnInfo(worksheet, ec.Header, myColumn, 1, 1) ec.ColumnInfo.columnWrite(myRow, myColumn, ec.Header, "text", formats.headerFormat(), True) myColumn = myColumn + 1 myRow = myRow + 1 for e in entries: myColumn = startColumn for ec in entryColumnInfo: ci = ec.ColumnInfo tag = ec.Tag value = e.get(tag) if ec.Header == "Sold Lots": value = len(value) # should be a list of the lots if ec.Type == "currency": ci.columnWrite(myRow, myColumn, value, ec.Type, formats.currencyFormat(myRow)) elif ec.Type == "numeric": ci.columnWrite(myRow, myColumn, value, ec.Type, formats.numberFormat(myRow)) elif ec.Type == "date": ci.columnWrite(myRow, myColumn, value, ec.Type, formats.dateFormat(myRow)) dateCol = xlsxwriter.utility.xl_col_to_name(myColumn) elif ec.Type == "formula": if ec.Header == "Month": fmt = "=MONTH(" + dateCol + str(myRow + 1) + ")" elif ec.Header == "Year": fmt = "=YEAR(" + dateCol + str(myRow + 1) + ")" ci.columnWrite(myRow, myColumn, fmt, ec.Type, formats.formulaFormat(myRow)) else: ci.columnWrite(myRow, myColumn, value, ec.Type, formats.formulaFormat(myRow)) myColumn = myColumn + 1 myRow = myRow + 1 # for ec in entryColumnInfo: ec.ColumnInfo.columnSetSize(1.4)
def writeMatrixWorksheet(self, type="Quantity", startRow=0, startColumn=0, worksheet=None): if worksheet == None: if self.workbook == None: return None myWorksheet = self.workbook.add_worksheet(type + " Matrix") else: myWorksheet = worksheet symbolList = [] for s, hRow in self.symbolMatrix.items(): if s == "FCASH": continue if hRow.AnyShares(): # print("{}:{}".format(s,hRow)) symbolList.append(s) symbolList = sorted(symbolList) row = startRow column = startColumn i = 0 # 1st Column is the Month ci = common_xls_formats.ColumnInfo(myWorksheet, "Month", column) self.columns[i] = ci ci.columnWrite(row, column, "Symbol", "text", self.formats.headerFormat(), True) column = column + 1 # now write out the symbol column header for s in symbolList: ci = common_xls_formats.ColumnInfo(myWorksheet, s, column) self.columns[i] = ci ci.columnWrite(row, column, s, "text", self.formats.headerFormat(), True) column = column + 1 i = i + 1 # now write out the months on each row column = startColumn row = row + 1 ci = self.columns[0] # for i in range(0, self.unitNumber): for i in range(self.unitNumber, -1, -1): rowDateT = transaction.monthdelta(datetime.date.today(), -i) # print(colDateT) if calendar.month_abbr[rowDateT.month] == "Jan": rowHeader = (calendar.month_abbr[rowDateT.month] + "/" + str(rowDateT.year)) else: rowHeader = calendar.month_abbr[rowDateT.month] ci.columnWrite(row, column, rowHeader, "text", self.formats.textFormat(row), True) row = row + 1 column = startColumn + 1 for i in range(0, len(self.columns)): ci = self.columns[i] # print("{}:{}".format(i,ci.name)) row = startRow + 1 hdata = self.symbolMatrix.get(ci.name) if hdata == None: print("ERROR: No history data for {}".format(ci.name)) for i in range(self.unitNumber, -1, -1): # for i in range(0, self.unitNumber): # print("{}:{}".format(i,hdata.months[i])) if type == "Quantity": fValue = hdata.Quantity(i) elif type == "Price": fValue = hdata.Price(i) elif type == "Value": fValue = hdata.Value(i) else: print("WARNING: Invalid Type [{}]", type) raise if type == "Quantity": ci.columnWrite(row, column, fValue, "number", self.formats.numberFormat(row)) else: ci.columnWrite(row, column, fValue, "number", self.formats.currencyFormat(row)) row = row + 1 column = column + 1 # go if type == "Value": # add a total formula ci = common_xls_formats.ColumnInfo(myWorksheet, "Total", column) self.columns[column] = ci row = startRow ci.columnWrite(row, column, "Total", "text", self.formats.headerFormat(), True) row = row + 1 for i in range(0, self.unitNumber + 1): startSumColumn = xlsxwriter.utility.xl_col_to_name( startColumn + 1) + str(row + 1) endSumColumn = xlsxwriter.utility.xl_col_to_name(column - 1) + str(row + 1) formula = "=SUM(" + startSumColumn + ":" + endSumColumn + ")" ci.columnWrite(row, column, formula, "formula", self.formats.currencyFormat(row)) row = row + 1 for myCol in self.columns: ci = self.columns[myCol] ci.columnSetSize(1.3)
def WriteValuesWorksheet(self, workbook, formats): worksheet = workbook.add_worksheet("Portfolio") myKeys = self.data.keys() columnInfo = [] myRow = 0 myColumn = 0 for h in self.headers: ci = common_xls_formats.ColumnInfo(worksheet, h, myColumn) ci.columnWrite(myRow, myColumn, h, "text", formats.headerFormat(), True) columnInfo.append(ci) myColumn = myColumn + 1 myRow = 1 for k in myKeys: v = self.data[k] myColumn = 0 for l in self.labels: ci = columnInfo[myColumn] if l == "shares": ci.columnWrite(myRow, myColumn, v[l], "number", formats.numberFormat(myRow)) elif (l == "quote" or l == "price_day_change" or l == "market_value" or l == "gain_loss" or l == "avg_cost_per_share" or l == "gain_loss_last_12m" or l == "cost_basis"): ci.columnWrite(myRow, myColumn, v[l], "currency", formats.currencyFormat(myRow)) elif l == "price_day_change_pct" or l == "gain_loss_pct": # since I'm already reading in a percent, it needs to be converted back to a float fValue = ci.convertFloat(v[l]) if fValue != None: fValue = fValue / 100 ci.columnWrite( myRow, myColumn, fValue, "percent", formats.percentFormat(myRow), ) else: ci.columnWrite(myRow, myColumn, v[l], "text", formats.textFormat(myRow)) else: ci.columnWrite(myRow, myColumn, v[l], "text", formats.textFormat(myRow)) myColumn = myColumn + 1 myRow = myRow + 1 for ci in columnInfo: ci.columnSetSize(1.3)
def getDividends(self, monthsAgo=36, startRow=0, startColumn=0): # These are the types of transactions that are needed to fill out # the dividend sheet # tTypes = ["Dividend Income", "Reinvest Dividend", "Interest Income"] tTypes = [ "Dividend Income", "Reinvest Dividend", "Interest Income", "Long-term Capital Gain", "Short-term Capital Gain", "Reinvest Long-term Capital Gain", "Reinvest Short-term Capital Gain", ] pickList = dict() pickList["type"] = tTypes pickList["months"] = monthsAgo # pickList["symbol"] = ["AAPL", "HD","USAIX","T"] matchingTrans = [] for t in self.transactions: if pickByFields(t, pickList): p = copy.deepcopy(t) matchingTrans.append(p) # Now we have all the transactions required to make the # Dividend sheet. # print(len(matchingTrans)) # Need a list of the symbols symbolList = [] self.dividendData.clear() # Dont change this value: endDate = datetime.datetime.today() for m in matchingTrans: sym = m.get_value("symbol") try: symbolList.index(sym) except ValueError: symbolList.append(sym) sDiv = self.dividendData.get(sym) if sDiv == None: sDiv = {} sDiv["symbol"] = sym sDiv["dividends"] = numpy.zeros((monthsAgo + 1)) divSheet = sDiv["dividends"] self.dividendData[sym] = sDiv else: divSheet = sDiv["dividends"] transDate = m.getDateTimeDate() tranMonthsAgo = (endDate.year - transDate.year) * 12 + ( endDate.month - transDate.month) divSheet[tranMonthsAgo] += float(m.getAmount()) # symbolList.sort() if self.workbook == None: return None self.divColumns.clear() # for Dividend sheet myWorksheetName = "Dividends_" + str(monthsAgo) myWorksheet = self.workbook.add_worksheet(myWorksheetName) myRow = startRow myCol = startColumn # headers = TransactionHeaders() ci = common_xls_formats.ColumnInfo(myWorksheet, "Symbol", myCol) self.divColumns[myCol] = ci ci.columnWrite(startRow, myCol, "Symbol", "text", self.formats.headerFormat(), True) myCol += 1 for i in range(0, monthsAgo + 1): colDateT = monthdelta(datetime.date.today(), -i) colHeader = calendar.month_abbr[ colDateT.month] # + "/" + str(colDateT.year) ci = common_xls_formats.ColumnInfo(myWorksheet, colHeader, myCol) self.divColumns[myCol] = ci ci.columnWrite(startRow, myCol, colHeader, "text", self.formats.headerFormat(), True) myCol = myCol + 1 myRow += 1 for s in symbolList: myCol = 0 ci.columnWrite(myRow, myCol, s, "text", self.formats.textFormat(myRow), True) myCol += 1 sDiv = self.dividendData[s] mDivs = sDiv["dividends"] for i in range(0, len(mDivs)): ci.columnWrite( myRow, myCol, mDivs[i], "accounting", self.formats.accountingFormat(myRow), ) myCol += 1 myRow += 1 ci = self.divColumns[0] ci.columnWrite(myRow, 0, "Total", "text", self.formats.textFormat(myRow)) ci.columnSetSize(1.3) for myCol in range(1, monthsAgo + 2): ci = self.divColumns[myCol] cName = xlsxwriter.utility.xl_col_to_name(myCol) myFormula = ("=SUM(" + cName + str(startRow + 2) + ":" + cName + str(myRow) + ")") ci.columnWrite(myRow, myCol, myFormula, "formula", self.formats.accountingFormat(myRow)) ci.columnSetSize(1.3) # set up the chart basics: myChart = self.workbook.add_chart({"type": "column"}) myChart.set_title({"name": "Dividend Chart"}) myChart.set_size({"width": 1000, "height": 700}) # colors = [ "#FF9900", "#00FF00","#0000FF"] colors = ["#4DA6FF", "#88FF4B", "#B30059", "#FF9900"] numSeries = int(monthsAgo / 12) # print("numSeries:", numSeries) for i in range(0, numSeries): seriesStartCol = 1 + (i * 12) columnStart = xlsxwriter.utility.xl_col_to_name(seriesStartCol) seriesStopCol = 12 + (i * 12) columnEnd = xlsxwriter.utility.xl_col_to_name(seriesStopCol) myValues = ("=" + myWorksheetName + "!$" + columnStart + "$" + str(myRow + 1) + ":$" + columnEnd + "$" + str(myRow + 1)) myColValues = ("=" + myWorksheetName + "!$" + columnStart + "$" + str(startRow + 1) + ":$" + columnEnd + "$" + str(startRow + 1)) # print(myValues) myYear = endDate.year - i myChart.add_series({ "name": str(myYear), "values": myValues, "categories": myColValues, "fill": { "color": colors[i] }, }) # Insert the chart into the worksheet. colName = xlsxwriter.utility.xl_col_to_name(startColumn + 1) myWorksheet.insert_chart(colName + str(startRow + 2), myChart)
def iexDataSheet(worksheet, iexData, data_type, symbols, formats): qdColumnInfo = [] myColumn = 0 for qd in iexData: ci = common_xls_formats.ColumnInfo(worksheet, qd.label, myColumn, 1, 1, 40) ci.columnWrite(0, myColumn, qd.label, "text", formats.headerFormat(), True) qdColumnInfo.append(ci) myColumn = myColumn + 1 myRow = 1 for key, value in sorted(symbols.items()): t = symbols[key] if t.numShares() <= 0: continue myColumn = 0 dataSet = t.get_data(data_type) # print("qdata:",dataSet) if dataSet == None: continue for qd in iexData: myTag = qd.tag # print("tag:",myTag) dataElement = dataSet.get(myTag) ci = qdColumnInfo[myColumn] if dataElement != None and (qd.type == "currency" or qd.type == "percentage" or qd.type == "number"): try: fValue = float(dataElement) ci.columnWrite(myRow, myColumn, fValue, qd.type, qd.format(myRow)) except: ci.columnWrite(myRow, myColumn, dataElement, qd.type, qd.format(myRow)) elif dataElement != None and qd.type == "timestamp": tsInt = int(dataElement) if tsInt > 0: tsDt = datetime.datetime.fromtimestamp(tsInt / 1000.0) # print(ci.name,":",tsDt) ci.columnWrite(myRow, myColumn, tsDt, "timestamp", qd.format(myRow)) else: print("Error: Field ", qd.tag, " has no timestime value:", dataElement) ci.columnWrite(myRow, myColumn, dataElement, "text", qd.format(myRow)) else: ci.columnWrite(myRow, myColumn, dataElement, qd.type, qd.format(myRow)) myColumn = myColumn + 1 myRow = myRow + 1 # for ci in qdColumnInfo: # print(ci) ci.columnSetSize(1.4)
def printMatrix( workbook, worksheetName, worksheet, formats, ticker, histData, divData, startRow=0, startColumn=0, ): # print(str(histData)) # print("printMatrix: {}".format(divData)) # Create the column info hdrs = ["Stock", "Price", "Value", "Quantity", "Dividends"] jTags = ["stock", "price", "value", "qty", "divs"] mTypes = ["text", "current", "currency", "number", "currency"] myColumn = startColumn myRow = startRow # Create the column data for the Stock, Price, Value, and Quantity section entryTypes = [] columns = [] for i in range(0, len(hdrs)): et = bdata.EntryType(hdrs[i], jTags[i], mTypes[i]) entryTypes.append(et) ci = common_xls_formats.ColumnInfo(worksheet, hdrs[i], i) # Write the headers for ec in entryTypes: columnInfo = common_xls_formats.ColumnInfo(worksheet, ec.Header, myColumn, 1, 1) columnInfo.columnWrite(myRow, myColumn, ec.Header, "text", formats.headerFormat(), True) columns.append(columnInfo) myColumn = myColumn + 1 myColumn = startColumn # + 1 myRow = myRow + 1 chartStartRow = myRow ci = columns[0] hasValue = False # History data is stored from the current to the oldest. # Let's print the for i in range(len(histData.unitQuantity) - 1, -1, -1): if hasValue == False and histData.Quantity(i) <= 0: continue if hasValue == False: hasValue = True startIndex = i rowDateT = transaction.monthdelta(datetime.date.today(), -i) # print(colDateT) if (calendar.month_abbr[rowDateT.month] == "Jan" or i == startIndex): # len(histData.unitQuantity)-1: rowHeader = calendar.month_abbr[rowDateT.month] + "/" + str( rowDateT.year) else: rowHeader = calendar.month_abbr[rowDateT.month] ci.columnWrite(myRow, myColumn, rowHeader, "text", formats.textFormat(myRow), True) # print(">{}:({}.{}):{}".format(i,myRow,myColumn,rowHeader)) myRow = myRow + 1 chartEndRow = myRow myRow = startRow + 1 myColumn = startColumn for i in range(startIndex, -1, -1): for c in range(1, len(columns)): # print("{}:{}".format(i,c)) ci = columns[c] if hdrs[c] == "Price": fmt = formats.currencyFormat(myRow) value = histData.Price(i) elif hdrs[c] == "Quantity": fmt = formats.numberFormat(myRow) value = histData.Quantity(i) elif hdrs[c] == "Value": value = histData.Value(i) fmt = formats.currencyFormat(myRow) elif hdrs[c] == "Dividends": value = divData[i] fmt = formats.currencyFormat(myRow) else: print("Invalid colunm {}".format(hdrs[c])) raise ci.columnWrite(myRow, myColumn + c, value, mTypes[c], fmt) myRow = myRow + 1 for c in columns: c.columnSetSize(1.4) # chartTypes = [ "Price", "Quantity", "Value"] colors = ["#4DA6FF", "#88FF4B", "#B30059", "#FF9900", "#FF9911"] # print(">{}".format(ticker.name)) for i in range(1, len(hdrs)): if hdrs[i] == "Dividends": myChart = workbook.add_chart({"type": "column"}) else: myChart = workbook.add_chart({"type": "line"}) myChart.set_title({"name": "{} {} Chart".format(ticker.name, hdrs[i])}) myChart.set_size({"width": 900, "height": 300}) columnStart = xlsxwriter.utility.xl_col_to_name( startColumn) # this is where the months are myLegendValues = ("='" + worksheetName + "'!$" + columnStart + "$" + str(chartStartRow + 1) + ":$" + columnStart + "$" + str(chartEndRow)) # print("myLegendValues:{}".format(myLegendValues)) columnStart = xlsxwriter.utility.xl_col_to_name(startColumn + i) myValues = ("='" + worksheetName + "'!$" + columnStart + "$" + str(chartStartRow + 1) + ":$" + columnStart + "$" + str(chartEndRow)) myChart.add_series({ "name": hdrs[i], "values": myValues, "categories": myLegendValues, "fill": { "color": colors[i] }, }) myChart.set_x_axis({ "date_axis": True, "minor_unit": 4, "minor_unit_type": "months", }) # Insert the chart into the worksheet. r = int((i - 1) % 2) c = int((i - 1) / 2) colName = xlsxwriter.utility.xl_col_to_name(startColumn + 5 + (c * 7)) worksheet.insert_chart(colName + str(startRow + 2 + (r * 17)), myChart) # for ci in columns: ci.columnSetSize(1.4)
) bdata.ProcessEntry(e, symbols, unique_accounts) unique_accounts.sort() # details are the rows of the Stock Analysis worksheet. details = [] bdata.createSheet(symbols, unique_accounts, details) worksheet = workbook.add_worksheet("Stock Analysis") # First 2 columns are Name and Symbol ColumnInfo = [] # Name Column ci = common_xls_formats.ColumnInfo(worksheet, "Name", 0, 1, 40) ci.columnWrite(0, 0, "Name", "text", formats.headerFormat()) ColumnInfo.append(ci) # Symbol Column ci = common_xls_formats.ColumnInfo(worksheet, "Symbol", 1, 1, 10) ci.columnWrite(0, 1, "Symbol", "text", formats.headerFormat()) ColumnInfo.append(ci) myColumn = 2 # Add in the individual accounts next # for acct in unique_accounts: # myColumn = xlsxwriter.utility.xl_col_to_name(myColumn) + "1" # print("myColumn:",myColumn) ci = common_xls_formats.ColumnInfo(worksheet, acct, myColumn, 1, 10)