How automatic do you want it to be? And what operating system are you on?
Programs like Premiere Elements have InstantMovie features that you just throw the video into and it churns out a movie, complete with titles and effects. iMovie has a better version.
Premiere Elements also includes a Slideshow Creator that works with video also. You just arrange the video clips and it provides the titles and effects. And Premiere Elements, Vegas Movie Studio and CyberLink PowerDirector all include templates that you drop video into and that generate the movie for you.
It all depends on whether you want fully automatic, semi-automatic or guided storytelling.