I have a somewhat large movie collection. Maybe not so large in comparison to some people, but definitely large compared to the average person. As with anything, the more you have of something the harder it is to keep track of or organize it all. I've been thinking about doing some sort of catalog system for a while now. Something like Discogs would be perfect. Discogs actually had a few sister sites for a while, one of them being filmogs, but they seem to have disappeared. I know there's a bunch of apps available, free and paid for, but I'm hesitant to use one. The initial setup will take a huge investment in time, and I'm just not willing to do that if I'm not absolutely sure of it. An app might be good now, but an update might cripple it. Or it might not be supported in the future and I might have to abandon it. Discogs was easy for me to set up because I was able to scan the barcodes to enter almost everything, and the site is popular enough that I'm not too concerned with it disappearing any time soon.
I've toyed with the idea of just making a simple spreadsheet for a while, but I really don't want to manually input all the data. I could just do movie title and format (VHS, DVD, BD) to get a simple list going, but I wouldn't mind having more than that. However, I'm certainly not about to manually get all that data for my entire collection. But I just found out about something awesome, and I can easily make a spreadsheet that'll auto populate most of the data for me!
IMDb has datasets that you can download in .tsv format! If you know Excel, then you'll know this means setting things up will be mostly trivial. Basically, I'm going to make a table where I input the movie title and it'll auto populate all the fields for me. I'll still have to manual input which format it is, but big deal. And I think I'll also have a comments field that I can note things in, like "part of box set" or something. I might also make a column to note if I've ever seen the movie or not. While not as bad as my Backloggery, I still own plenty of movies that I've never seen.
As for the fields that'll auto populate, I'm going to have release year, runtime and genre(s). I'm always curious to know the release year and runtime (and I hate when it's impossible to find on the back cover). As for genres, I really want them included so I can sort by genre to help find something to watch. Like, "I'm in the mood for a comedy, let me see what I have."
There's more than enough horizontal space to fit other columns, so I'm thinking about doing columns for director and top billed actors. It might be neat to sort by director or actor. Like, "I feel like watching something with Tom Hanks in it." Or, "I feel like watching a John Hughes movie." I'm not sure yet if I want to include this or not, sometimes having too much data makes it hard to look at.
If I don't include directors and actors, it wont matter much. I thought of a really nice touch. I'm going to have a Wikipedia link for every entry, and these will also auto populate simply by entering the movie title. Basically, if you add "(film)" to the end of the movie title and search that on Wikipedia, it almost always lands right on the correct article. I've tested a couple dozen so far and some of them do not go straight to the correct page, but instead it'll go to the search results page and the correct article is always the first or second link. You could easily do the same thing for a search with IMDb instead, but I don't know how to make IMDb jump straight to the correct result, just return the standard search query. Anyway, it's real easy, you just modify the search URL.
Here's the formula I'm using for it. Just replace "A2" with whatever column your movie title is in, if it isn't A. You can make "Search Wikipedia" say whatever you want, that's just what will display in the cell. You can even omit that last part of the formula and the hyperlink will just be the URL.
- Code: Select all
=HYPERLINK("https://en.wikipedia.org/w/index.php?search=" &A2 &" (film)", "Search Wikipedia")
You can use the same formula to search IMDb simply by changing the first part of the URL, like this...
- Code: Select all
=HYPERLINK("https://www.imdb.com/find?q="&A2&"&exact=true","IMDb")
edit: I found it better to add "exact=true" to the IMDb search URL.
IMDb has other datasets, like if you wanted to include ratings. That could be cool, but I usually don't care what ratings a movie gets. Here's the link for where you can download the datasets from IMDb.
https://www.imdb.com/interfaces/
Be warned though, they're huge! The basics one is 129 MB zipped, and 621 MB uncompressed. It literally has a million lines. Excel couldn't even load it all. I trimmed it up for my needs. I removed things like shorts, TV episodes (there also TV Shows, episodes is literally each individual episode from every show), video games, et cetera. After I took out those entries and converted it to an .xlsx it's now only 13 MB. I also made sure the movie titles are column A so vlookup works.
Which, by the way, is how you auto populate the data. Just use the VLOOKUP formula in Excel. It's very easy to do. One downside to this approach is for more complicated titles you really have to nail it. I'm using only exact matches because I've found that when I do partial matches for vlookups it's never accurate. So when you type in the movie title, it has to be exact. It's not so hard for most movies, but some get tricky. You just have to learn how IMDb formats things and then you can usually guess correctly. Some are still annoying, like Alien 3 because of the way it stylizes the 3. And then there's something like "Dracula" which returned the 1931 film, which is good because that's the only one I own. But there's TONS of movies that are titles simply "Dracula" so that's where it gets tricky. Still, it seems to work 99% of the time. I'm not afraid to have to do a manual entry here and there. Or just search the dataset to see how it was entered and then adjust my input to match it.
There's a few things I still have to work on, like converting the runtime format. Right now it just displays the runtime in minutes, but I'd prefer if it were hours and minutes. I know this can be done with formulas. I've converted time formats in Excel before, but not to/from the same formatting. It's the kind of thing that you figure out how to do with Google and then forget LOL. Does anyone know the easiest way to accomplish this? Right now the data is in just minutes, such as "134" for example. I would like to display 134 as "2 hrs 14 mins." Right now I just tacked on a "& "mins" so at least it'll display "134" as "134 mins".
I was thinking about figuring out a way to auto add movie posters, possibly using VBA. But it might be too much of a headache. And to get the image sized large enough to appreciate, it might be too large to have the table look nice. Besides, the Wiki or IMDb page always has a movie poster. So I might just forget about it.
After that, I'll just have to work on the formatting to make it look pretty. Right now I'm just messing around with the functionality of it first. So it's just a boring looking table. I was entering a few random movie titles to mess around with it, but I choose a lot of them specific to try and see how well it works. So things that are common words, like Eraser. Or tricky subtitles, things that are an ampersand instead of the word "and", and stuff like that.