1

Summary:

I have one particular client that requires us to deliver a few annual packages and letters to their partnership members. Say, roughly 500 members. The letters have paragraph text, line text, considerable numerical data and require some formatting. These letters change each year based on current tax law and the specifics of the tax year. This is semi-unique work in our firm and so a massive enterprise wide solution might be too expensive.

Current Solution:

Right now, we are using MS Excel for the letters. The advantages of this is: the numerical data is held in the same workbook as the letters so it's easy to do something like a sum formula in-letter (as opposed to needing a whole new data set), the letters are relatively easy to format and customize. The downside is, we need many different sheets for each eventual variation in the letters. At the moment, I'm just using a very easy VBA script to print the letters to PDF. To that note, that is about the extent of my coding ability and in general I need there not to be too much (if any) required scripting since I need CPA staffers to be able to handle the process.

Ideal Solution:

The perfect solution would allow me to use some sort of logic to dictate which variation of letter a member might receive. For example, a set of partners I might designate as type A, should get a letter with 3 paragraphs and 15 lines with numerical data, special formatting and a 2nd page. Type B would get something different, maybe only two paragraphs and 10 lines, no special formatting and no 2nd page.

Of course printing to PDF would also be needed.

Already Tried:

This past year we attempted to use a firm-proprietary software that integrated SSRS / Report Builder. This had the logic-part, but required the use of some SQL (meaning I needed a lot of help from IT - too much) and the formatting options were unwieldy (can't full justify text for example, only left, right, center). We decided we did not like this solution.

Looking at:

Right now, I'm taking wild internet stabs at other software but a lot of them seem either too advanced or too "enterprise-wide" (expensive). I really liked the sound of Windward Studios, but I fear it's too in-depth and I don't think we'll be able to put our data on a SQL server again (too techy for my people). Templafy seemed a bit too expensive starting at $800 a month. (I think I'd prefer the solution to stay below $3,000 a year or so).

I'm going to schedule a call with Webmerge by Formstack since they look to have smaller business solutions, but to be honest I'm not sure if I'm even really looking in the right direction.

If anyone could provide any other specific suggestions or advice, it would be greatly appreciated!

Thanks so much! Amesh

POTENTIAL RESOLUTION

Not 100% on this as of yet and I'm surprised it might be this simple, but I think we might just end up using MS Word's mail merge function. I honestly didn't know it had such an in-depth logic/conditional system. Additionally, the "code" is simple enough that I can teach several CPA's how to do it.

0

Can I suggest taking a look at the python eco-system which makes automating this sort of thing much easier. Specifically there is a book called Automate The Boring Stuff With Python by Al Sweigart which is available to read for free or to purchase and gives a nice gentle introduction to doing exactly the sort of things that you are asking for.

I am thinking that you could grow your solution quite rapidly with:

  • Have a template Word or pdf document, (with whatever formatting you need set), for each layout.
  • Initially use the Excel spreadsheet as the source of your data
  • Presumably there is either one worksheet for each type of member or one with all of the members that includes a column of the member type - either is good.
  • You could later move to using a different mechanism for storing the member data if desired.
  • Likewise the numeric data, and its processing could initially come from the spreadsheet(s) but could later be automatically generated you already have the SQL queries keep them as a starting point for this. You could even have an intermediate step of generating the spreadsheet from a database or other sources with python.

There are a number of plus points:

  1. Price - Python and most of it tools is completely free no purchase price and no monthly charges
  2. Incremental development as outlined above you can do this step wise
  3. Learning python is generally quick and there is lots of help out there
  4. Python development is usually rapid.
  5. There are modules available, free, for python to talk PDF, Excel, Word, SQL/other DB, etc.
  6. You can easily include graphical elements such as performance graphs, etc.
  7. Python skills are well worth acquiring all by themselves.
  8. There are additional tools that can make this whole thing a lot easier such as Pandas and Jupyter.
  9. Python is available to run on anything from a Raspberry Pi (or smaller) through desktop machines running Windows/Linux/OS-X up to super computer clusters, (and you can still access the Excel/Word format files on any of them).
  10. This would be a great project for a student/intern as a summer job and there are a lot who will have learnt python as a part of their course.
  • 1
    Thanks for your reply! I can definitely see the many advantages of Python, including the fact that it's inherently valuable. However, I don't think my team (or I) will have the time to learn the required skills (I'll check with out summer interns though I haven't heard of any such skills). This sounds like a potentially good solution for the 2020 tax year, however it sounds to me like I'll need something possibly more short-term or with less of a learning curve. – Amesh Jul 30 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.