Is it possible to export W2 employer name?

[richiethetaxpro]

Hi All!

Is it possible to use the SDK to read and export information like:

• W2 | Employer Name
• 1099-INT | Name of Payer
• 1099-R | Name of Payer
• K1 | Name of Entity

I would like to use this information to compile a custom document request form from clients every year based on the documents they had in the previous year.

Is this possible?

Rich 🙂

[DatabaseRobert · Accepted solution]

Short answer: No.

Longer answer: Yes.

Somewhat involved answer: Not the way you are thinking.
  You canNOT just say, "Show me all Client# detail files that have 'Lockheed-Martin' in the page10/W-2 code800/Employer Name field."
  You *can* say, "show me all W-2s FOR THIS CURRENT CLIENT that I am looking at, which have 'Lockheed-Martin' in the Employer Name field."

Really involved answer: Using an external program, reach out and iteratively open every single detail file.
  Copy every line of detail.
  SAVE every line of detail into your separate, external repository.
  Move on to the next detail file.
  Once you have them all there, THEN the answer is, "Yes, you can now query against your external data, and arrive at all W-2s issued by CompanyX."

Anticipate this taking a while, and running to a really large file.
  (For years prior to TY2018, I was able to use Paradox (database program) and another user's very good binary file extraction process to do just such an extract.
  TY2017 we processed around 2250 returns (97+% IND, ~70 total FID, maybe 50 total across SCO + COR + PAR + GIF), and my extracted data file runs to 768,038 lines of detail, and is 133.5MB in size.)

Robert

[DatabaseRobert]

Having said all of that...

Reading your *actual* *question* a little bit more closely, something such as "create a custom feedback form for This Particular ClientX" is absolutey possible, and should be comparatively trivial to do.  (Because at any given time, you are only dealing with a single client's detail.)

Become familiar with the "Series #" information for the pages that you care about.  10/Wages is series11, 11/Interest is series12, 12/Dividends is series13, 13/Rental is series14, 16/business is series51...  but page3/Direct Deposit information is series5100, and the various 20.X (K-1 items) are each separate: 20.1 PAR is 55, 20.2 SCO is 56, 20.3 EST is 57, 20.4 REMIC is 58.

Essentially everything you will care about will use code800 for the name, so that's really easy.

Robert

[richiethetaxpro]

Hi Robert! Thank you so much for the information!

I'm glad to know it's possible 🙂

Do you freelance doing this kind of work? Would I be able to hire you to help me do this?

Thanks!

[DatabaseRobert]

I do, and you can; far be it from me to stop people throwing money at me.

However, as I was getting into the car last night I had the thought, "Lacerte already does this work and we pay to get it; it's called the Organizer."  The Wages & Pension pages have the column of just-the-names, the Business & Rental & Partnership pages each have the name (and/or location, for Rentals) at the top...
  ...why not just do a limited-print, "Partial Organizer", for just those pages?  10, 11, 12, 16, 18, (17 if you want Farm, too; we don't do too many), all 20.X, send to printer.

Heck, you could even do them up in advance (print the entire client list), save them to PDF instead of paper, and just print out the paper one when you started working on the client.  Easy to forward the PDF to a client in email, too, since it already exists.
  (My first thought was to use the input sheets, because you can limit those to JUST 10p1 and 20.1p1--the pages where the names are--rather than getting ALL the pages for that property (like the Organizer would do).  The advantage to using the Organizer version of the page is that for W-2 and 1099 pages, you can get several of them on a single sheet.)

.

I guess what I'm driving at is, "What kind of page are you looking for?"  What's it going to do for you?

Robert

[richiethetaxpro]

Thanks for taking the time to respond and provide such great information. I appreciate your honesty and transparency 🙂

The issue with the partial organizer is that it spreads the information across too many pages. It's easy for a client to get confused or miss something.

What kind of page are you looking for?

It would be better for our clients to recevie a single page PDF containing a bullet point list of what they need to gather.

Items included (if applicable to the client) would be:

Page 1

• TP Last Name
• TP First Name
• SP Last Name
• SP First Name

Page 10

• Employer(s) Name(s)

Page 11

• Name(s) of Payer(s)

Page 12

• Name(s) of Payer(s)

Page 13.1

• Name(s) of Payer(s)

Page 13.2

• Name(s) of Payer(s)

Page 14.1

• TP Social Security Income (Yes/No)
• SP Social Security Income (Yes/No)
• TP Alimony Income (Yes/No)
• SP Alimony Income (Yes/No)
• TP Misc Income Subject to SE Tax (Description(s)
• SP Misc Income Subject to SE Tax (Description(s)
• TP Misc Other Income (Description(s)
• SP Misc Other Income (Description(s), 

Page 14.2

• TP Unemployment Income (Yes/No)
• SP Unemployment Income (Yes/No)

Page 16

• Business Name(s)

Page 17

• Disposition Activity (Yes/No)

Page 18

• Street Address(es)

Page 20

• Name of Ptshp(s), Corp(s), Trust(s), Estate(s)

Page 24

• TP IRA Contribution (Yes/No)
• SP IRA Contribution (Yes/No)
• TP Roth IRA Contribution (Yes/No)
• SP Roth IRA Contribution (Yes/No)
• TP SEP IRA Contribution (Yes/No)
• SP SEP IRA Contribution (Yes/No)
• TP Student Loan Interest (Yes/No)
• SP Student Loan Interest (Yes/No)
• TP SE Health Insurance (Yes/No)
• SP SE Health Insurance (Yes/No)
• TP Alimony Paid (Yes/No)
• SP Alimony Paid (Yes/No)
• TP SEP IRA Contribution (Yes/No)

Page 25

• Real Estate Taxes (Yes/No)
• Personal Property Taxes (Yes/No)
• Mortgage Interest (Description(s)
• Cash Donations (Yes/No)
• Non-Cash Donations (Yes/No)

Page 33

• Dependent Care Expenses (Yes/No)

Page 38.1

• Tuition (Yes/No)

Page 39

• 1095-A (Yes/No)

Real World Example:

Client Name: Mickey Mouse & Minnie Mouse

Documents/Info Needed

1. W2 | Disneyland Park
2. W2 | Disneyworld Park
3. W2 | Disney Productions
4. 1099-INT | Bank of America
5. 1099-INT | IRS
6. 1099-INT | US Bank
7. 1099-DIV | TD Ameritrade
8. 1099-DIV | Scottrade
9. 1099-R | TD Ameritrade
10. 1099-R | Animated Characters Pension Plans
11. W2-G | Pechanga Resort
12. W2-G | Wynn Las Vegas
13. Social Security Income Statement
14. Alimony Received
15. 1099-MISC | DisneyLand
16. 1099-MISC | Pluto's Palace of Food
17. Unemployment Income Received
18. Mickey's Gardening Service | Business Income and Expenses
19. Minnie's Day Care Service | Business Income and Expenses
20. Stock Sales
21. 123 Main Street | Rental Income and Expenses
22. 222 Lake Ave | Rental Income and Expenses
23. K-1 | Disneyland Resorts
24. K-1 | Disney TV
25. IRA Contribution Amount
26. Roth IRA Contribution Amount
27. SEP IRA Contribution Amount
28. Student Loan Interest Paid
29. Health Insurance Paid
30. Alimony Paid
31. Real Estate Taxes Paid
32. Personal Property Taxes (DMV) Paid
33. Mortgage Interest Statement | Mr. Cooper
34. Mortgage Interest Statement | Chase
35. Cash Donations Made
36. Non-Cash Donations Made
37. Child Care Expenses
38. 1098-T | College Tuition Statement
39. 1095-A | Covered California Statement

What's it going to do for you?

As the preparers, this list is going to help us ensure we received all the applicable data from the client.

As the client, this list is going to make it easy for them to see all the information they need to send us.

I imagine this being a two part project.

One, export this data to an excel file.

Two, create PDF documents based on the excel columns.

Does that make sense? Any idea of the effort (hours) required to accomplish this?

Thanks again!

Rich

[DatabaseRobert]

Very clearly expressed, Richie, thanks!

(WAY too long/didn't read summary: YES, you can do everything you're looking for.  I already do some similar things, which I go on to describe in more detail than you likely care about.)

With that kind of breakdown, yes, you're going to need to know "this specific page [the Series #], these specific codes".  That's the bad news.  The good news is, once you figure out what they all are, you should be able to do fundamentally the identical thing every year with no change.
  (The process of "doing the extract" will need to be told, "look at 2018 IND" to set it up, and then next year, "look at 2019 IND", but that's just "where"; WHAT it's doing will be the same every year.)

Given that everything DOES have a Series # ('input page') associated with it, I think you gain comprehension on the part of clients by leaving it grouped (as you laid the information out for me) rather than "One Big List" (as Mickey & Minnie were shown).  However, that's more of a presentation issue than a functionality issue.
  Also, for page17/Dispositions, I highly recommend that you include the name, not just code100/Delete This Year.  ("Yep, 38 of them are gone!  Got no clue if they're Alcoa or IBM or Zillow..."  🙂
  (And correcting myself from a previous message: I realized that I listed 17 as Farm [which is actually dispositions] when I think that is 19.  That says just how few Farms we do.)

And I can see 100% exactly how I would do this...
  ...in Paradox.  (This is an off-the-shelf product, a relational database originally built by Borland back in the '80s.  They wound up purchasing Ashton-Tate, the makers of dBase, which explains why I have been able to read the Lacerte database files since they adopted that format: they are .DBF files, or 'dBase'.  So--including up through TY2017 (pre-ODBC specific connection)--I have been able to reach out and just read [and write, but the less said about that the better] the data, such as "what forms are in the return" or "what F4 status is the return at" and "what states are included [at all] and which are efiled.")

I pretty much stopped doing things in spreadsheets ~25 years ago, because I needed the ability to write code on the fly, use it for just-one-person or "the whole client list" or "test by criteria" or...  Just easier/more flexible to do that in a database layout, than in a spreadsheet.  I'm still perfectly willing to do things in a spreadsheet WHEN that is the correct tool for the current job, but I try to keep a firm eye on when that is.

In Paradox, I would do just like I mention above, for "just this one person."  I would design a user-interface that shows "this is the Data1/Client Info" database line for that person.  (I already did this, up through TY2017.)
  I would do this in the PREVIOUS year, so that I get "all the lines".
  I would put a push-button on that Form to say, "go read this client's data file," and store all of the outputs somewhere.
  I would have a stored Query somewhere that indicates "these are the codes to pull out" (series11/wages code800/name, series12/interest code800/name, and so on).
  And then I would generate a Report that shows the resulting Answer table, broken down by page (all Wages, then Interest, then Div, then Business, and so on).

.

As I said, I already had something set up, so for example when I go through and grab data I am storing the income sources at the same time.  (Basically, when I see that I have arrive at a new Input Page/Property combination [W-2 #4] rather than the one that I was just seeing [W-2 #3], I post the information thus far and create a new blank.)  Some of the information that I record is:
 - what ["Wage"/10 or "Business"/16 or "Dividend"/12]
 - what # in Lacerte [4, as above]
 - SSN it relates to (since we know which client's detail we're looking in, and we know whether this is client or spouse, it's easy to know which to use)
 - name/800 ("HEWLETT-PACKARD")
 - and if code100/Delete is used, record that: "[!DTY!] WELLS FARGO" [==1 delete this year], or "[*DNY*] TRI STATE INVESTORS" [==2 delete next year].  Note that I never adjusted for page13/Pensions code100/Delete ==3 [suppress this year but retain for Organizer/Pro Forma].
 - and additional information of use, such as page3/Miscellaneous code13/suppress:
"[SupOrg] Next year's organizer SUPPRESSED"
 - or page3/Miscellaneous, Direct Deposit information:
Bank, <SSSNshown>, <Bank Name>, <ABA#>, Account #: <number>, (Checking)

The advantage to doing it in this way is that it is always "live" every time you do it.  (Go to a current client, click the button, get fresh report.)  That's the problem with external data storage: anything that has changed inside Lacerte since that time...  is NOT shown; you have to go get it again.

.

As to how long it takes: when I cut the 2017 program loose it started at 1:32 in the afternoon, and it was done with ~2200 INDividual files at 4:55.  So about three and a half hours.  Any given file typically takes only 5-15 seconds to work with; our retired doctor with K-1 income in 6 different states is a lot beefier than the college kid with 2 summer W-2s and no interest.
  After that, I was able to do queries and abstracts (How many couples had Spouse wages >$75k?  How many where only the Spouse received Social Security?  How many with Dividends from company "Opp" (for Oppenheimer) of more than $3k?), and if need be I could re-process to get just the NEW data: I record when a file is processed and if the detail file has a newer date, then re-import; otherwise, nothing has changed.

My only real caveat would be, "Excel probably is not your program of choice."  (Except for things like "Viewing/sorting that big extract of all detail.  Maybe.  For some things.")
  I would use Excel more for working with:
 - the Data1/Client Info file (especially the 'Preparer' and 'Staff Preparer' and 'Current Status' fields), and
 - the Data5/F4 Status file (status #, date done, who by initials)
 - the Data6/Tax Summary [also, Data Export from Lacerte] fields (so "Total Wages" and so on), and
 - the Data7/Forms Used file.

One of the other ODBC adopters, Abraham Friedman up in NYC, has a REALLY GOOD extraction that he tied together across all of the Lacerte modules, to replace the now long-gone Lacerte Practice Manager screen.  It shows which returns in which modules are assigned to each Preparer and Staff Preparer.
  I need to reach back out to him and find out how his work on that is progressing.

Robert

[DatabaseRobert]

I am surprised that there is nowhere to store an email address in the user Profile here on the Community.

So Richie, if you still need to talk about any thoughts you have with implementing this, please reach me (in my consulting persona) at "[email address removed] ".

Robert

[Moneymasternow]

Mickey and Minnie Mouse are married wouldn't that change your real world example.

Teresa