Import Data into schedule D-John Hancock

[Guest raleightpa]

Do any of you import Schedule D data into Gov't Forms in the Schedule D? We are currently keying the amounts per fund and have to add new funds completely. Do any of you import this data? Is it possible? Or is there a way to export a file to do an ESUM of the amounts? We are also adding those amounts per fund by hand.

Thank you

[pmacduff]

I use the Schedule D conversion tool and I LOVE it! I have to make a minor modification to the spreadsheet file and add a line at the top in order to import properly, but other than that, it works well. I also add up the totals on the Schedule to be sure that they match the spreadsheet, I don't know of a way to sum them in Gov. Forms but it is still way faster to import and you can't miss any of the funds. I have found that the totals on the hard copy Schedule A include receivables whereas the totals on the breakdown of funds do not.

Hope this helps. Let me know if you have any questions and I'll do my best to assist. There is a Schedule D manual on the JHancock website (I think) for a couple different vendors including Relius.

[Guest raleightpa]

I use the Schedule D conversion tool and I LOVE it! I have to make a minor modification to the spreadsheet file and add a line at the top in order to import properly, but other than that, it works well. I also add up the totals on the Schedule to be sure that they match the spreadsheet, I don't know of a way to sum them in Gov. Forms but it is still way faster to import and you can't miss any of the funds. I have found that the totals on the hard copy Schedule A include receivables whereas the totals on the breakdown of funds do not.

Hope this helps. Let me know if you have any questions and I'll do my best to assist. There is a Schedule D manual on the JHancock website (I think) for a couple different vendors including Relius.

Do you have to wait until the 2007 Schedule D conversion tool is available? I've found so far that the file name in the first step is .3x6 and the 5500 program is asking for .3x7. I can change the file name extension and I follow the steps and it comes out blank. What exact modifications do you make to the post conversion spreadsheet? Maybe that's why it's not working.

[pmacduff]

I usually download the conversion tool each year, even if no changes are made. I haven't done any for '07 to let you know if that's working ok; sorry. I'll post on that as soon as I can...

The modification I make is to insert a blank line in the top of the post conversion spreadsheet and then resave it. I've had to do this for the last few years and not sure why, but once I do that, the import works.

let me know if this helps.

[Guest raleightpa]

I usually download the conversion tool each year, even if no changes are made. I haven't done any for '07 to let you know if that's working ok; sorry. I'll post on that as soon as I can...

The modification I make is to insert a blank line in the top of the post conversion spreadsheet and then resave it. I've had to do this for the last few years and not sure why, but once I do that, the import works.

let me know if this helps.

I added a line to the psot conversion sheet like you said and again, nothing happened, the page is still blank. Bummer.

[pmacduff]

Don't want to sound brash, but did you follow the manual exactly? I know there is a step in there where you have to click on the "sort" button, etc. or are you not even getting that far?

Which page is "still blank"?

[Guest raleightpa]

Don't want to sound brash, but did you follow the manual exactly? I know there is a step in there where you have to click on the "sort" button, etc. or are you not even getting that far?

Which page is "still blank"?

Yeah I saw that on the instructions about sorting. The last thing I do is hit "Append" then "ok". I get know warning about "This will save the current form when the sorting process has finished, would you like to continue?" Yes or No I do not get this box. I'm guessing something is not right. I should be putting the cursor on the 1st line of the second page right? That's what the instructions say.

[pmacduff]

Ye s- try the cursor on line 2 of the second page instead of line 1. I think that's what I do...sorry maybe this is another little change I do without thinking...

Good Luck!

[Guest raleightpa]

Ye s- try the cursor on line 2 of the second page instead of line 1. I think that's what I do...sorry maybe this is another little change I do without thinking...

Good Luck!

Do you think maybe when you start doing your 2007 Schedule D's you could send me a message with the exact steps you are doing? I know that's alot of work, but I really would appreciate it. Maybe it really makes a difference using the 2007 Schedule D conversion tool. I'm using the 2006 in the 2007 5500 program so maybe that's it. John Hancock said the 07 Schedule D conversion tool should be ready next week. Thanks!

[Guest raleightpa]

pmacduff- I went it and tried on a 2006 Schedule D and it worked but it only put the last fund listed and started on page 2. Does page 1 get filled in if it does it correctly?

[pmacduff]

Sure - I'll let you know when I do my 1st for 2007. (I'm actually having a carryforward issue that Relius told me may not be fixed until mid-February so I have to enter filer data all over again when I go to do a form!)

Anyway, after you hit "append" it will only look like there is one entry on page #2. This is where you want to use the "sort" option from the Edit menu and once that is done it will complete the form. I sort by the PSA name, but I suppose you could use any of the sort options (never tried.) Give this a try with the 2006 and let me know if it will work for you.

Also - when I went in to look at an '06 Sc D so that I was giving you the right information, I remembered even another step that seems as though it was unnessary for you, but I want to share for anyone who may need to know:

For my Sch Ds to be accurate I actually have to go in and "delete" the Sch D (if there is a carryforward from the year before) and start with a "fresh" one in order for my import to work.

Good luck, keep me posted and I'll let you know about the '07 as soon as I can...

[Guest raleightpa]

Sure - I'll let you know when I do my 1st for 2007. (I'm actually having a carryforward issue that Relius told me may not be fixed until mid-February so I have to enter filer data all over again when I go to do a form!)

Anyway, after you hit "append" it will only look like there is one entry on page #2. This is where you want to use the "sort" option from the Edit menu and once that is done it will complete the form. I sort by the PSA name, but I suppose you could use any of the sort options (never tried.) Give this a try with the 2006 and let me know if it will work for you.

Also - when I went in to look at an '06 Sc D so that I was giving you the right information, I remembered even another step that seems as though it was unnessary for you, but I want to share for anyone who may need to know:

For my Sch Ds to be accurate I actually have to go in and "delete" the Sch D (if there is a carryforward from the year before) and start with a "fresh" one in order for my import to work.

Good luck, keep me posted and I'll let you know about the '07 as soon as I can...

Thank you! You've been very helpful. I can't wait to try again on Monday.

[Guest raleightpa]

Sure - I'll let you know when I do my 1st for 2007. (I'm actually having a carryforward issue that Relius told me may not be fixed until mid-February so I have to enter filer data all over again when I go to do a form!)

Anyway, after you hit "append" it will only look like there is one entry on page #2. This is where you want to use the "sort" option from the Edit menu and once that is done it will complete the form. I sort by the PSA name, but I suppose you could use any of the sort options (never tried.) Give this a try with the 2006 and let me know if it will work for you.

Also - when I went in to look at an '06 Sc D so that I was giving you the right information, I remembered even another step that seems as though it was unnessary for you, but I want to share for anyone who may need to know:

For my Sch Ds to be accurate I actually have to go in and "delete" the Sch D (if there is a carryforward from the year before) and start with a "fresh" one in order for my import to work.

Good luck, keep me posted and I'll let you know about the '07 as soon as I can...

Thank you! You've been very helpful. I can't wait to try again on Monday.

I went and sorted and it worked like a charm! I can't believe how great it works! Thank you! Now let's hope there aren't any bugs in the '07 version.

[pmacduff]

Glad to be of help!

I think we have to know as much about software as administration so anytime I can help someone and feel a bit of satisfaction it works out for all !

[pmacduff]

UPDATE:

I wanted let raleightpa and others know that JHancock has posted the 2007 Sch D conversion tool in its TPA website. I ran through a quick import and it worked fine for me. I did not check in detail yet, but everything seems to be fine...

[Guest Sara H]

Make sure you read through the updated directions. Instead of "append" they now tell you to "replace". Also, take note that there are instructions to pull all funds (Activity View), which pulls funds with zero balances at the end of the plan year. I used to manually enter all of those funds.