Industry Discussions

A dedicated space to share resources, connect with like-minded data workers, and discuss industry specific analytic challenges + solutions.
Right now, the power to solve new global challenges across industries, is at your fingertips, no matter where you're working from. Create a new topic or reply to an existing thread to share your perspective.
SOLVED

Tax Exemption Certificates

Highlighted
6 - Meteoroid

I'll jump in and mention a method I've used to prepare a few PDF tax forms (think information only filings) with the help of Alteryx. First I created a PDF template using the "Prepare Forms" tool in Acrobat by converting the existing tax form into fill-able form PDF. When you do this initially, Acrobat will assign a name to each line/box where it thinks you will want to key data. You can rename, add your own lines, etc. Once I have the fields I need on the form, I export the PDF data as a txt file. This gives me what I need for to create the import template using Excel. An important note, this only works if you organize your data (entity name, FEIN, tax year, etc) all on a single row per entity. The goal is to create a spreadsheet (basically a table) with the PDF form field names (gathered from the PDF export) as headings on row 1, then beginning on row 2, entity1 and its data across the columns, then entity2 and its data, and so on. Once this is done, an Alteryx workflow is used to create a csv file for each row of data while maintaining the headings. So if you have 15 entities, you end up with 15 csv files ready to import into your PDF template you've created. (In Acrobat: Prepare Form > More Options > Import Data)

 

Obviously this isn't as automated as using a python script or similar but it is a way to automate part of the process for the lay person like myself. It's worth noting that this may not work well with complex tax forms so your mileage may vary. 

 

As far as the Alteryx workflow I use, I found it as a response on Community. If you are interested, let me know and I'll see if I can find the source.

 

TLDR: Create PDF template with existing tax form. Use Alteryx to create csv files to import into said PDF template.

Highlighted
6 - Meteoroid
That’s sounds like a good approach from a tax persons point of view. I’d be interested in the workflow you mentioned.

Thanks
John
Highlighted
6 - Meteoroid

All,

 

I've posted a solution using Python in order to populate the fields. I've seen many people suggest that as a solution, just curious to see if anybody has tried the solution I proposed? Attaching it here again.

 

Thanks,

 

Mauricio Pommier

Highlighted
10 - Fireball

I'm interested too!

Highlighted
8 - Asteroid

Hi Mauricio,

 

I tried your solution, but I was stuck in the first macro. How can we read a fillable PDF?

 

Thank you!

 

Highlighted
6 - Meteoroid

Hi @ultrarunner 

 

Both macros require the installation of a Python library, which has to be uncommented from the code. I've attached workflow here that you can run once (need to run with elevated rights so that you can install the library). If you had already installed the python library, then it might be a different error, in which case, can you copy the error along with your response?

 

Regarding the "how can we read a fillable PDF", is it so that you know what to populate? or what are you trying to read? The first macro in my workflow reads the fillable PDF to get the list of fillable items. The second one uses that list (that you have to populate) in order to insert the values into the PDF. Is that what you mean?

Highlighted
6 - Meteoroid

Thank you all for all the suggestions!  I ended up purchasing an auto mail merge plug in for Adobe Acrobat. It basically does the import & export as you suggested, Jonathan, but also generates the forms, saves copies and emails the tax certs to the vendors.

 

1. Using ALTERYX, I prepared the list of vendors to whom i needed to send certificates, including their email addresses. 

2. I got the fill-able PDF from each state's Department of Revenue (or made one from a pdf)  

3. The mail merge plug in took me through the mapping and email setup to certificate generation and distribution. 

 

Not 100% ALTERYX, but a powerful combination!

Highlighted
Alteryx Partner
I think that's an excellent way to handle it. In my mind I was wondering why this was not considered as its simple, straightforward and easy to share with others.
Highlighted
Alteryx
Alteryx

Just a quick FYI / cross-post in case you have not see the how to guide for filling out PDFs with alteryx.  https://community.alteryx.com/t5/Alteryx-Designer-Discussions/A-quick-tutorial-for-getting-Alteryx-d...

 

Highlighted
6 - Meteoroid

Thanks! Good to know...