Completed Python Script - CSV to PDF with Pyfpdf

OTM-Options

OTM-Options

FYI

  • Do you regularly download your brokers transaction history or other CSV files?
  • Do you then perform the same mundane spreadsheet tasks manipulating that CSV file?
  • Do you then output to PDF and print a hard copy?
  • Would you like to output a formatted PDF file directly from the CSV file?
  • Do you have basic 101 knowledge of Python or would you like to learn Python?

If you have answered yes to the above questions then the Pyfpdf library for PDF document generation under Python might be for you. The feature I like is the HTML tables, you can create a very nice PDF layout directly from the CSV file and eliminate the spreadsheet step. The steps are: CSV in > Python CSV manipulation > Pyfpdf > PDF out

Link to Pyfpdf: Pyfpdf

The 200 line Python script below can output a 10,000 line 183 page PDF file from a raw CSV file in 15 seconds. I have also attached a 2-page PDF file that the script generated from a CSV file. Again the big feature of Pyfpdf is the use of HTML tables for layout.


Python code using Pyfpdf
Code: 
#!/usr/bin/env python
# Python version 2.7.6

from pyfpdf import FPDF, HTMLMixin
import datetime
import time

csv_in = "TransactionHistory_22523594.csv"
pdf_out = "TransactionHistory2015.pdf"
title = "Trading Summary - 2015"
subject = "Subject"
author = "Author"
keywords = "Keywords"
creator = "Creator"

def format_currency(value):
   if value < 0:
     results = '${:,.2f}'.format(value)
     value = "(" + results.replace("-","") + ")"
   else:
     value =  '${:,.2f}'.format(value)
   return value

header = """
<table align="center" width="100%">
<thead><tr>
<th width="5%">#</th><th width="9%">Date</th><th width="6%">Buy/Sell</th>
<th width="4%">QTY</th><th width="25%">Security</th><th width="6%">Price</th>
<th width="9%">Debit</th><th width="9%">Credit</th><th width="10%">Commission</th>
<th width="16%">Total Amount</th>
</tr></thead>
"""

footer_commission = 0
footer_debit = 0
footer_credit = 0
footer_total_amount = 0
counter = 1
split = ","
join_r = "</td><td align=\"right\">"
join = "</td><td>"
html_out = ""

for line in reversed(list(open(csv_in))):
   if len(line.strip()) != 0 :
     line = line.strip()
     column = line.split(split)
     if column[2] == "Buy" or column[2] == "Sell" or column[2] == "Expired":
       row_counter = '{0:04d}'.format(counter)
       transaction_date = column[0]
       buy_sell = column[2]
       qty = column[5]
       security = column[4]
       price = column[6]
       if price == "":
         price = "0"
       total_amount = column[8]
       currency = column[9]
       transaction_date = (datetime.datetime.strptime\
         (transaction_date, "%Y-%m-%d").strftime("%a %b %d"))
       qty = abs(int(qty))
       price = float(price)
       total_amount = float(total_amount)
       amount = qty * price * 100
       amount = int(amount)

       abs_total_amount = abs(float(total_amount))
       if column[8] >= "0" and  column[8] <= "1":
         commission = 0
       else:
         commission = abs(abs_total_amount - amount)
       if total_amount <= 1:
         debit = amount
         credit = 0
       else:
         debit = 0
         credit = amount
       if debit > abs_total_amount:
         credit = debit
         commission = debit
         debit = 0

       footer_debit = (footer_debit + debit)
       footer_credit = (footer_credit + credit)
       footer_commission = (footer_commission + commission)
       footer_total_amount = (footer_total_amount + total_amount)

       qty = str(abs(qty))
       price = format_currency(price)
       debit = format_currency(debit)
       credit = format_currency(credit)

       if debit == "$0.00":
         debit = " "
       if credit == "$0.00":
         credit = " "
       commission = format_currency(commission)
       if commission == "0":
         commission = ""
       total_amount = format_currency(total_amount)
       counter = counter + 1
       mod = counter % 2
       if mod == 1:
         bgcolor = "<tr bgcolor=\"#FFFFFF\"><td>"
         fcolor = "<tr bgcolor=\"#E1E1E1\"><td>"
       else:
         bgcolor = "<tr bgcolor=\"#E1E1E1\"><td>"
         fcolor = "<tr bgcolor=\"#FFFFFF\"><td>"

       if "(" in total_amount:
         neg_pos = join_r + "<red>" + total_amount + " " + currency + "<black>"
       else:
         neg_pos = join_r + total_amount + " " + currency

       if counter == 1000:
         over_flow = "<tr></tr>\n" * 10
       else:
         over_flow = ""

       row = (bgcolor + row_counter + join + transaction_date + join + buy_sell + join \
         + qty + join + security + join_r + price + join_r + debit + join_r + credit + join_r \
         + commission + neg_pos + "</td></tr>\n" + over_flow)

       print row_counter
       html_out = html_out + row

pl = (footer_debit + footer_commission)
pl_percent = ((footer_credit - (pl)) / pl * 100)
pl_debit = format_currency(pl)
pl_debit = "(" + pl_debit + ")"
f_debit = format_currency(footer_debit)
f_credit = format_currency(footer_credit)
f_commission = format_currency(footer_commission)
f_total_amount = format_currency(footer_total_amount)
pl_percent = '{:.2f}'.format(pl_percent)

td = "<td> </td>"
tdr = "<td align=\"right\"><b>"
usd = " USD</b></td></tr>\n"
a = td + td + td + td + tdr
b = "</b></td>" + tdr
c = td * 10
d = td * 7

if "(" in f_total_amount:
   pl_percent = "(" + pl_percent + "%)</b></td>"
   n_s = "<red>" + pl_percent + tdr + f_total_amount + usd + "<black>"
   n_s = n_s.replace("-","")
   ns = "<red><td></td>" + tdr + f_total_amount + usd + "<black>"
else:
   n_s = pl_percent + "% </b></td>" + tdr + f_total_amount + usd
   ns = "<td></td>" + tdr + f_total_amount + usd

f1 = (fcolor + a + "Subtotal:</b></td>" + td + tdr + f_debit + b + f_credit + b \
   + f_commission + tdr + ns)
f2 = ("<tr>" + c + "</tr>\n")
f3 = ("<tr>" + d + tdr + "Total Debit:</b></td>" + td + tdr + "<red>" + pl_debit + usd + "<black>")
f4 = ("<tr>" + d + tdr + "Total Credit:</b></td>" + td + tdr + f_credit + usd)
f5 = ("<tr>" + d + tdr + "P/L:</b></td>" + tdr + n_s)

footer = f1 + f2 + f2 + f3 + f4 + f5 + f2 + f2 + "</tbody></table>"
html = header + html_out + footer

class MyFPDF(FPDF, HTMLMixin):
   def footer(this):
     this.set_y(-25)  #-25
     this.set_font('Arial','I',10)
     this.set_text_color(0,0,0)
     this.cell(0,10,'PAGE %s OF {nb}' % this.page_no(),0,0,'C')

pdf=MyFPDF('P','mm','letter')
pdf.set_top_margin(margin=18)  #18
pdf.set_auto_page_break(True, 27) #27
pdf.add_page()
pdf.alias_nb_pages()
pdf.set_font("Arial", style="B", size=14)
pdf.cell(200, 5,'Your Broker', ln=1)
pdf.set_font("Arial", size=12)
pdf.cell(200, 5,'Trading Summary for 2015', ln=1)
pdf.cell(200, 5,'Account No: ########', ln=1)
pdf.cell(200, 5,'SIN: ########', ln=1)
pdf.cell(200, 5,'Phone: 1 (888) ######', ln=1)
pdf.cell(200, 5,' ', ln=1)
pdf.set_font("Arial", style="B", size=14)
pdf.cell(200, 5,'John Doe Smith', ln=1)
pdf.set_font("Arial", size=12)
pdf.cell(200, 5,'1234 Main Street', ln=1)
pdf.cell(200, 5,'Sin City 55416', ln=1)
pdf.cell(200, 5,'Phone: ########', ln=1)
pdf.cell(200,5,'Email: ######@gmail.com',0,1)
pdf.set_title(title)
pdf.set_subject(subject)
pdf.set_author(author)
pdf.set_keywords(keywords)
pdf.set_creator(creator)
pdf.write_html(html)
pdf.output(pdf_out)

input("\n\nPress the Enter key to exit.")





:)
 

Attachments

Last edited:
Did you make this?

Do you have something that does the reverse? PDF to CSV. I think it would be great to get data from Futures broker daily reports. There is an industry standard report.
 
how is this even remotely useful given that your code breaks the second one single column in your or others' broker statement differs from the one you hardwired into your code? This is what happens when you let people without much thought process capability lose on general purpose languages. Same issue with R. Tons of idiots running packages/scripts which underlying techniques and methodologies they do not understand in the slightest.

OTM-Options said:
FYI

  • Do you regularly download your brokers transaction history or other CSV files?
  • Do you then perform the same mundane spreadsheet tasks manipulating that CSV file?
  • Do you then output to PDF and print a hard copy?
  • Would you like to output a formatted PDF file directly from the CSV file?
  • Do you have basic 101 knowledge of Python or would you like to learn Python?

If you have answered yes to the above questions then the Pyfpdf library for PDF document generation under Python might be for you. The feature I like is the HTML tables, you can create a very nice PDF layout directly from the CSV file and eliminate the spreadsheet step. The steps are: CSV in > Python CSV manipulation > Pyfpdf > PDF out

Link to Pyfpdf: Pyfpdf

The 200 line Python script below can output a 10,000 line 183 page PDF file from a raw CSV file in 15 seconds. I have also attached a 2-page PDF file that the script generated from a CSV file. Again the big feature of Pyfpdf is the use of HTML tables for layout.


Python code using Pyfpdf
Code: 
#!/usr/bin/env python
# Python version 2.7.6

from pyfpdf import FPDF, HTMLMixin
import datetime
import time

csv_in = "TransactionHistory_22523594.csv"
pdf_out = "TransactionHistory2015.pdf"
title = "Trading Summary - 2015"
subject = "Subject"
author = "Author"
keywords = "Keywords"
creator = "Creator"

def format_currency(value):
   if value < 0:
     results = '${:,.2f}'.format(value)
     value = "(" + results.replace("-","") + ")"
   else:
     value =  '${:,.2f}'.format(value)
   return value

header = """
<table align="center" width="100%">
<thead><tr>
<th width="5%">#</th><th width="9%">Date</th><th width="6%">Buy/Sell</th>
<th width="4%">QTY</th><th width="25%">Security</th><th width="6%">Price</th>
<th width="9%">Debit</th><th width="9%">Credit</th><th width="10%">Commission</th>
<th width="16%">Total Amount</th>
</tr></thead>
"""

footer_commission = 0
footer_debit = 0
footer_credit = 0
footer_total_amount = 0
counter = 1
split = ","
join_r = "</td><td align=\"right\">"
join = "</td><td>"
html_out = ""

for line in reversed(list(open(csv_in))):
   if len(line.strip()) != 0 :
     line = line.strip()
     column = line.split(split)
     if column[2] == "Buy" or column[2] == "Sell" or column[2] == "Expired":
       row_counter = '{0:04d}'.format(counter)
       transaction_date = column[0]
       buy_sell = column[2]
       qty = column[5]
       security = column[4]
       price = column[6]
       if price == "":
         price = "0"
       total_amount = column[8]
       currency = column[9]
       transaction_date = (datetime.datetime.strptime\
         (transaction_date, "%Y-%m-%d").strftime("%a %b %d"))
       qty = abs(int(qty))
       price = float(price)
       total_amount = float(total_amount)
       amount = qty * price * 100
       amount = int(amount)

       abs_total_amount = abs(float(total_amount))
       if column[8] >= "0" and  column[8] <= "1":
         commission = 0
       else:
         commission = abs(abs_total_amount - amount)
       if total_amount <= 1:
         debit = amount
         credit = 0
       else:
         debit = 0
         credit = amount
       if debit > abs_total_amount:
         credit = debit
         commission = debit
         debit = 0

       footer_debit = (footer_debit + debit)
       footer_credit = (footer_credit + credit)
       footer_commission = (footer_commission + commission)
       footer_total_amount = (footer_total_amount + total_amount)

       qty = str(abs(qty))
       price = format_currency(price)
       debit = format_currency(debit)
       credit = format_currency(credit)

       if debit == "$0.00":
         debit = " "
       if credit == "$0.00":
         credit = " "
       commission = format_currency(commission)
       if commission == "0":
         commission = ""
       total_amount = format_currency(total_amount)
       counter = counter + 1
       mod = counter % 2
       if mod == 1:
         bgcolor = "<tr bgcolor=\"#FFFFFF\"><td>"
         fcolor = "<tr bgcolor=\"#E1E1E1\"><td>"
       else:
         bgcolor = "<tr bgcolor=\"#E1E1E1\"><td>"
         fcolor = "<tr bgcolor=\"#FFFFFF\"><td>"

       if "(" in total_amount:
         neg_pos = join_r + "<red>" + total_amount + " " + currency + "<black>"
       else:
         neg_pos = join_r + total_amount + " " + currency

       if counter == 1000:
         over_flow = "<tr></tr>\n" * 10
       else:
         over_flow = ""

       row = (bgcolor + row_counter + join + transaction_date + join + buy_sell + join \
         + qty + join + security + join_r + price + join_r + debit + join_r + credit + join_r \
         + commission + neg_pos + "</td></tr>\n" + over_flow)

       print row_counter
       html_out = html_out + row

pl = (footer_debit + footer_commission)
pl_percent = ((footer_credit - (pl)) / pl * 100)
pl_debit = format_currency(pl)
pl_debit = "(" + pl_debit + ")"
f_debit = format_currency(footer_debit)
f_credit = format_currency(footer_credit)
f_commission = format_currency(footer_commission)
f_total_amount = format_currency(footer_total_amount)
pl_percent = '{:.2f}'.format(pl_percent)

td = "<td> </td>"
tdr = "<td align=\"right\"><b>"
usd = " USD</b></td></tr>\n"
a = td + td + td + td + tdr
b = "</b></td>" + tdr
c = td * 10
d = td * 7

if "(" in f_total_amount:
   pl_percent = "(" + pl_percent + "%)</b></td>"
   n_s = "<red>" + pl_percent + tdr + f_total_amount + usd + "<black>"
   n_s = n_s.replace("-","")
   ns = "<red><td></td>" + tdr + f_total_amount + usd + "<black>"
else:
   n_s = pl_percent + "% </b></td>" + tdr + f_total_amount + usd
   ns = "<td></td>" + tdr + f_total_amount + usd

f1 = (fcolor + a + "Subtotal:</b></td>" + td + tdr + f_debit + b + f_credit + b \
   + f_commission + tdr + ns)
f2 = ("<tr>" + c + "</tr>\n")
f3 = ("<tr>" + d + tdr + "Total Debit:</b></td>" + td + tdr + "<red>" + pl_debit + usd + "<black>")
f4 = ("<tr>" + d + tdr + "Total Credit:</b></td>" + td + tdr + f_credit + usd)
f5 = ("<tr>" + d + tdr + "P/L:</b></td>" + tdr + n_s)

footer = f1 + f2 + f2 + f3 + f4 + f5 + f2 + f2 + "</tbody></table>"
html = header + html_out + footer

class MyFPDF(FPDF, HTMLMixin):
   def footer(this):
     this.set_y(-25)  #-25
     this.set_font('Arial','I',10)
     this.set_text_color(0,0,0)
     this.cell(0,10,'PAGE %s OF {nb}' % this.page_no(),0,0,'C')

pdf=MyFPDF('P','mm','letter')
pdf.set_top_margin(margin=18)  #18
pdf.set_auto_page_break(True, 27) #27
pdf.add_page()
pdf.alias_nb_pages()
pdf.set_font("Arial", style="B", size=14)
pdf.cell(200, 5,'Your Broker', ln=1)
pdf.set_font("Arial", size=12)
pdf.cell(200, 5,'Trading Summary for 2015', ln=1)
pdf.cell(200, 5,'Account No: ########', ln=1)
pdf.cell(200, 5,'SIN: ########', ln=1)
pdf.cell(200, 5,'Phone: 1 (888) ######', ln=1)
pdf.cell(200, 5,' ', ln=1)
pdf.set_font("Arial", style="B", size=14)
pdf.cell(200, 5,'John Doe Smith', ln=1)
pdf.set_font("Arial", size=12)
pdf.cell(200, 5,'1234 Main Street', ln=1)
pdf.cell(200, 5,'Sin City 55416', ln=1)
pdf.cell(200, 5,'Phone: ########', ln=1)
pdf.cell(200,5,'Email: ######@gmail.com',0,1)
pdf.set_title(title)
pdf.set_subject(subject)
pdf.set_author(author)
pdf.set_keywords(keywords)
pdf.set_creator(creator)
pdf.write_html(html)
pdf.output(pdf_out)

input("\n\nPress the Enter key to exit.")





:)
More...
 
LibreOffice does already this to convert any document from and to any LibreOffice supported format including csv to pdf. Such as "unoconv -f pdf my.csv", you get my.pdf.
 
OTM-Options said:
FYI
The 200 line Python script below can output a 10,000 line 183 page PDF file from a raw CSV file in 15 seconds. I have also attached a 2-page PDF file that the script generated from a CSV file. Again the big feature of Pyfpdf is the use of HTML tables for layout.
More...

I've also written such comment-less abracadabra code, however maintaining it is always a nightmare. Please, comment every block of code.

If you really have an allergy to writing documentation, put large blocks into reasonably named functions. At least in this way, people can see what fmt_csv_to_table(data) does.

Try to avoid writing literal strings alongside variable assignment and modification.
A different way to make the same script would have been to use a function that builds up a buffer from strings.

Code: 
buffer_line("<tr>" + c + "</tr>\n")
buffer_line("<tr>" + d + tdr + "Total Debit:</b></td>" + td + tdr + "<red>" + pl_debit + usd + "<black>")

You could build on this further by writing a wrapper:

Code: 
def buffer_row(string):
    buffer_line("<tr> " + string + "</tr>")

def string_bold(string):
    return "<b>" + string + "</b>"

buffer_row(string_bold("Total Debit:"))
 
xandman said:
Did you make this?
More...


Yes ...... Using the Pyfpdf library.

xandman said:
Do you have something that does the reverse? PDF to CSV. I think it would be great to get data from Futures broker daily reports. There is an industry standard report.
More...


I have no use for PDF to CSV, but I'm sure there are solutions out there for PDF to CSV conversions.

Tavurth said:
I've also written such comment-less abracadabra code, however maintaining it is always a nightmare. Please, comment every block of code.

If you really have an allergy to writing documentation, put large blocks into reasonably named functions. At least in this way, people can see what fmt_csv_to_table(data) does.

Try to avoid writing literal strings alongside variable assignment and modification.
A different way to make the same script would have been to use a function that builds up a buffer from strings.
More...



  • The purpose of my thread is to bring attention to the Python pyfpdf library. It is not a Python tutorial.
  • I personally don't care about comment blocks in the code. Different strokes for different folks.
  • The code is easy to modify/maintain and suites my needs.


:)
 
You must log in or register to reply here.
Back
Top