Industry Discussions

Welcome to the industry discussions forum — a dedicated space to share resources, connect with like minded data workers, and discuss industry specific analytic challenges + solutions. Please read the Welcome post pinned below to familiarize yourself with how to engage in this new forum.
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

 

Hi!  Is anyone using ALTERYX data to populate tax exemption certificates?  What's the best automation solution?  Do I need third party software to populate PDF or other forms?

I am a newbie - been using ALTERYX about 8 months.    

 

INSPIRE! was fantastic last week.  I am charged up for doing more...

 

Teri Staniec

Manager Sales and Use Tax

Covanta Energy   

Highlighted
12 - Quasar

Hey @TSTANIEC,

 

I am not familiar with Tax exemption certificates, but I am familiar with populating PDFs with Alteryx using the reporting tools. There is also a way to use Alteryx and R to populate PDFs. If you have any interest in learning more feel free to reach out and we can talk through the options.

 

Best,
Chris Lewis

Highlighted
6 - Meteoroid

Hi Chris, 

 

Thanks for getting back to me so quick!  So far I have been doing single and multi tab Excel reports with the output tool - no pdfs.  

 

I need to drop the data at a specific locations on the page.  I have a standardized form / template from each state that I need to populate with names, addresses, Tax ID numbers, etc.  Sometimes i need to check specific boxes on the forms.  I would like to be able to add an electronic signature.  Forms differ from state to state.  

 

I am thinking i need to scan in each states' form to use as templates, then output my data onto the form with one form produced for each selected record in the data base.  (like WORD mail merge)

 

Any suggestions would be appreciated!

 

Teri

 

 

Highlighted
10 - Fireball

@TSTANIEC @cplewis90 I would love to see how this gets resolved, we are also working on something similar. There's an output range where you select where you want your output to go. For example, if you already have a spreadsheet that has been formatted, then you can output starting at cell X and basically fill out your template. 

Highlighted
7 - Meteor

I'd also be very interested to hear how this is accomplished.  We have to complete US tax forms on behalf of clients and doing this manually is rather cumbersome.

 

 

Highlighted
8 - Asteroid

If you're outputting to an excel template, it's pretty easy to do. On the excel template, highlight the "output" target cells, right click, and define name (also accomplished under Formulas and Name Manager). Configure an output tool in Alteryx as usual and select the template file as the output - instead of giving it a "sheet name" use the name defined in the previous step. If you have the template set, you can also use the blob tool to create a copy to output to. If you only do the previous steps it will write directly into the template file.

 

Let me know if you have questions - I can put together an easy example.

Highlighted
6 - Meteoroid

I am also very interested in seeing some examples of the excel process and the tie in to Alteryx, since I'm only about 5 weeks into Alteryx.   

 

However, I also see this concept as having much broader application.  Seems to me that the broader topic  could be Using Alteryx to Populate forms in Excel, and then Using Alteryx to pull in data from Excel forms to deal with the responses.   The application in this situation is exemption certificates.   But it could also be other topics. 

 

In the exemption certificate application, it would seem that creating pick lists in the excel format could drive standardization of responses to certain specific questions which could then be pulled back into Alteryx for analysis and easily grouped by the standard responses.   This could leave less data in the free form fields that customers respond to that needs to be parsed out in Alteryx.    

 

One example I that comes to mind in our business relates to intercompany transactions.  For tax, we need to analyze inter company transactions for Beat tax purposes as well as for Sales/Use tax purposes.  Our finance function manually creates inter company billing documents using either excel or word.    If the appropriate fields could be placed at specific locations on a form in excel, then the process could be better automated.   Then standardized pick lists on the form could then allow transactions to be easily categorized, and leave less in free form text fields for analysis.

 

Inspire was awesome last week, and we are moving forward with our 2nd projects.  Interested in your ideas on this and other ideas for other use cases.  

 

Best regards, 

John

 

Highlighted
6 - Meteoroid

Hi Teri,

 

The short answer would be yes... to some degree... I wrote two quick macros that use Python and a 3rd party Python library (you need to install that library in order to run it, check the first macro and uncomment some of the code, should be easy to follow).

 

There are some caveats with the solution, though. There are some Fillable PDF forms that aren't as user-friendly as others, so they don't show what's written through code on the filled out form - you need to click on the field in order to see the typed text. The form I'm sharing does allow you to input fields through code (not sure what the exact differences are...)

 

Let me know your thoughts and if you'd like to connect further.

 

Mauricio Pommier

Highlighted
5 - Atom

Clearly, there isn't a ready-to-use out of the box feature in Alteryx for populating PDF forms.

So, third party tool(s) is probably inevitable. (or code it in Python or R in Alteryx).

 

One idea I had was:

  • fill out the PDF form using 'token values'
  • Use a pdf2ps converter utility to convert the PDF into PostScript (or EPS).
    • Favorable thing about PS is that they are plain text.
  • String-replace the token values of PS with actual values in Alteryx
  • Use the converter utility to convert PS back to PDF
    • Command tool could come in handy to batch up the ps2pdf
Highlighted
12 - Quasar

@TSTANIEC,

 

The way I was thinking about doing this is to "recreate" the tax form using reporting tools and then create a macro that would feed in the data to the appropriate places. It would be quite the undertaking, but would be the best way to ensure it works seamlessly.