Re: Is it possible to export W2 employer name?
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.