John Hancock Reports

[Guest raleightpa]

Has anyone figured out how to convert JH's year end .pdf reports to excel? We have to key the gains by hand and we have alot of JH plans. We're trying to figure out a way to cut down on keying. I know they have a TED download but you have the set up over 150 funds (via an import) into Relius. We don't care about the funds. We also do not want to import beginning and ending balances. I was just wondering what everyone else is doing.

Thanks!

[pmacduff]

We use the Financial Interface. We set up one fund in Relius, ".John Hancock" - for each source in the plan. You can import totals and do not have to import each fund. We've been doing this for a long time and it works well.

We also do not import beginning or ending balances. Since you can choose what to import, we import the gains & losses and sometimes the contributions (depending on the client). We use the loan module and don't import any of the loan info but do all the loan transactions individually so our loan bals are per the amortization. I don't know how large your plans are, but I have a few that have over 200 participants, I'd go out of my mind if I had to hand key!

If you don't want to use the Financial Interface, I have other vendors where I copy the whole *.pdf file contents, convert to a text file and then convert to Word then Excel. You can go directly from text to Excel, but I like to look at the data in Word first to be sure that the data is as it should be. I've had success with this method, but have found that sometimes it can take just as long as hand keying, depending on the size of the client and the layout of the data.

[Guest raleightpa]

Boy the first option sounds wonderful. I wish I knew how to set it up in Relius. How do you format the TED file? That's what you use right?

[pmacduff]

Yes - we use the TED files for import - you don't have to do any formatting of the file if you use the Interface. If not, then I guess you could put the file into excel and import through a DER. I'm not sure how the funds would combine using that method, but maybe the TED files have "total" lines in them.

JHancock provides TED files quarterly. You download a *.zip file and, when you unzip that, there are 2 data files in *.txt format. [i keep them in separate directories by client.] Each quarter when you unzip the file there will be 2 files, one is a quarterly period file and one is a year-to-date file. If your client is annual, you need only the final year-to-date file to import. I rename the files when I save them using the quarter end date (i.e. 0307, 0607, 0907 and 1207).

In Relius, we set up an Investment in the Investment Table that is simply "John Hancock". Then under Account Definitions, we set up all of the source accounts using the John Hancock investment from the table.

When you are ready to import using the Financial Interface, there is a spot on the second page that you mark to "import to one fund". The system combines the contributions, gains, transfers, etc. and imports into the John Hancock investment by source for each participant.

I'm sure if you contact Corbel they will help you through the process.

Good luck!

[Guest raleightpa]

Okay so, I got as far as the Investment System Link Plan (Import) Screen and I checked boxes in the gain/loss column per source. So does this mean it will import at least the name and only the g/l? What about ending balances? it won't import contributions because I didn't check the boxes per source in that column correct? I want to do a "test" in an 07 or 08 plan year but I'm afraid it's not reversable? Do you know if it is? Will I be able to clear everything out if I need to? Thank you so much for your help pmscduff!

[pmacduff]

ok - it will import the total overall G/L into the one fund name you set up if you marked the "import to one fund" section under "processing options".

Anything that you check mark on the screen will NOT import, so it's the opposite of what you said. If you red checked the G/L, then it would import everything but the G/L. So be sure and mark only what you don't want to import. If you click on the help screen while you're there, it has a lot of good info.

Once you process the import, the transaction will show on your transaction screen. It will read "allocated link" and autmatically posts with the period end date from the JHancock file. You can then go in and run reports and they will reflect whatever you imported.

If you reverse that transaction, it will reverse your import and will remove the transaction completly from your transaction screen. So...yes - anytime you import through the interface, it is completely reversible by simply reversing the "allocated link" transaction. It disappears and you have to import again.

I think you'll get the hang of it pretty quickly, I did. It is a great timesaver!!

Let me know how you make out.

[Guest kbuehler@ingham.com]

If you need additional information, on the JH TPA website, under Toolkit - Other Guides and Tools, there is a TPA E-download (TED) - How it works guide.

[Guest raleightpa]

pmacduff- So I did it, but what imported doesn't match what is on the report. Why would that happen, what am I doing wrong?

[Guest raleightpa]

So I figured out why the gains were different on the report than what imported. JH had a 9/30/07 TED file yet the information in the file was for the 6/30/07 quarter. Woohoo! I love this! Thank you thank you for all of your help!

[pmacduff]

no problem, glad it worked out!