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)