Connecting Excel to After Effects 2018 to make pretty Animated Graphs | Daniel Scott | Skillshare

Connecting Excel to After Effects 2018 to make pretty Animated Graphs

Daniel Scott, Adobe Certified Trainer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
4 Lessons (23m)
    • 1. Introduction & Exercise files

      1:57
    • 2. Creating a .JSON file & Linking to After Effects

      7:19
    • 3. Drawing graph and writing expression

      9:40
    • 4. Completing chart animation

      4:13

About This Class

The long awaited dynamic graph creation is here! After Effects now supports the linking of data exported from software such as Microsoft Excel, where the data exported to a JSON format can now be linked and displayed in infinite scenarios within Adobe After Effects CC 2018.

I this short course, I’ll go over how we can animate a graph within Adobe After Effects CC 2018, we’ll go over exporting a graph to JSON format so we can work with it, we’ll also write an expression within After Effects to accurately display data from our JSON file. 

Jump in, teach yourself the newest After Effects abilities so you stay on top of your animation game.

Grab the exercise files here: Download

What are the requirements?

Transcripts

1. Introduction & Exercise files: Hi there, my name is Daniel Walter Scott and I am an Adobe certified instructor for Bring Your Own Laptop. It's time to talk about connecting data to After Effects to help drive your animation. This example, here's what we'll create, it's looking at the averages for temperatures in Dublin where I currently live. The cool thing about the animation, is that it is a chart driven by spreadsheet data. Why is that cool? Once I've done all my animation like I have here, I've got my style's going. I can easily reel into another file. I'll do that now. The moment we're showing Dublin watch here Dublin, it's got the year, and it's got this data. Let's have a little look. Super-quick, super easy replace with my home country, New Zealand, Auckland is where I'm from. Watch this, it's space bar and look, line chart changes. All the data changed, the name changed, the year I'm looking at is the same, so that stays the same. But super-quick, super easy, makes me super homesick because it's warmer all year around at home. It's okay, Dublin has all the Guinness that'll keep you warm. If you are newish to After Effects, check out my full course where we cover all sorts of data animation, not just as line chart, check that out on bringyourownlaptop.com. If you're experienced with After Effects, especially with expressions, you'd be flying after this tutorial pretty quick. First thing is you need to have Adobe After Effects CC 2018 or Above. We'll work on the earlier ones. If you want to play along exactly, download the exercise files that are free, there'll be a link down in the description where you can go and grab those, and play along with me on the download page. It'll be called something like data animation using After Effects and JSON. I feel like that's what I'll call it anyway. Let's go and make this thing. Let's go and make a new project, and I'll meet you over there. Let's get started. 2. Creating a .JSON file & Linking to After Effects: Okay, so I've got a new project open, and I am on the defaults, sitting for my workspace if you want to match it, I'm going to create a new composition and new button here. I'm going to call this comp for the charts. I'm going to pick from the drop-down here HDTV 1080 25, perfect. It's all make out five seconds long, and yet match all of that. Let's click "Okay". Let's bring in some graphics, if you're not following along with my examples you can build your own. I built mine and illustrate it because I'm quicker and faster in it but in the exercise files there is one called background outlined. Bring him in, and he should fit nicely onto a stage. What we'll do is we'll lock him, down the bottom here at the locking icon so that he doesn't get moved. Now the next thing we need to do is get out data. Let's have a look how to do that. Wherever you get your data from, you need to eventually change it into this thing called JSON. It's a data interchange format and that is the only file that after fixed is going to use. If it's like me, a CSV or an Excel sheet, it's pretty easy to convert, so you select all your data, copy it, and put it into something like this. This one is called csvjson.com and it's just a conversion tool and just tap over here. You can upload your CSV and delete that and paste that in there. That is what a JSON format looks like. The cool thing about it is that it's really user friendly to look at. It's not a big, ugly data file. Looks nice of JSON, copy it into something. We're going to make a JSON file. You can do it with a text document, I'm using Dreamweaver here. You can use anything you document JSON, create, paste it in, save it and that is the file that we can use. I've saved two files for us already, so you didn't have to if you're following along with the exercise files, but with your own data, you're going to have to do that. If it's not natively in JSON, you're going to have to convert it from the Excel or CSV, or if you've got a SQL data, it's easy enough to convert to JSON. You have to figure out with an online conversion tool. I've done that, saved it, let's go and have a look now and after fix. We import out JSON file just like we do any images or videos. I'm going to go to File Import, just double-click this gray area here. Let's go into data files, and let's have a look. We'll do Dublin to start with. I'm going to save my project. I'll save it into my desktop and I'll just call this one weather reports. The faster best and easiest way to understand it is, remember at the beginning we had the temperatures in Dublin, and then updated from our database. We'll do that bit of ticks connection first, and the people that understand expressions can go off from there and for the rest of us will do that connection, and then we'll look at the line chart. The first step grab the type tool click some up here put in some text, style it nicely. That's me styling it nicely. Actually I'm going to have two text boxes, so that's my first, but am going to leave that and I'm just as a simple but a text. I'm down here, I'm going to copy and paste it. So I've got two of them, and I'll move this across and double-click it and I'll call this one city. Not writing in Dublin because I'm going to replace the word city with all the cities potentially that I could use. I might just left-align actually character, left-aligned. So that spills this way and can help myself but I want to use a nice big thick font. Cool. So I want this to update and we do it, we're going to go into city, we're going to turn this down and we're going to find text. We're going to find source text, and we're going to turn this into an expression. If you've never done it before, hold down the Alt key on a PC or the Option key on the Mac, and click the little stopwatch, and weird stuff happens. This bit here, okay this is where we're going to start doing our typing. What I'm going to do is I'm going to make it a lot bigger because it has been a topic that goes on here, Don't need what's in it currently, and we're going to bring in our JSON file. First step, to bring it in all you do is use your pick whip here, this little thing here will steal our JSON file. So that's the first part of our footage from our Dublin Weather, I like to set it as Source Text. If you are new to expressions, you need to be spelling this all perfectly like I am here. I'm going to load it into a variable, this variable is going to be called Weather and that too is it. To make it all work and execute, we need to wrap it up in this thing, and this brackets here called eval, that will make it what we need to close it off with a semicolon. That's the magic that will bring in your JSON file, and load it into this variable that we can start using, cool. So in this case, the next thing I want to do is I want to look at my data, and load it printed on the screen, and I'm going to look at this, break here and what I want is the name. So there's 1, 2, 3, there's only three options and you can just keep the data small, and easy to look at, but you can have as many as you like. So I've got 1 , 2, 3 bits of data, and in this data set we've got city, that's the one I want first and this year, and the different months we need these, the average temperatures. So I want to find the city, I'm going to copy that. So now I need to use this so we've loaded all of my JSON file into weather. So inside weather, I would like to have a little look. So we're looking inside my JSON file that's now called weather, and I'd like to say, I'd like to look in the first record square brackets. So the first record actually in JSON or an expressions is zero. The first one is always zero. So we're looking at the first bit of data, my first option are the three, and I would like to look at dot, and I'd like to look at city. So that is the thing I'd like to look in, and close it off at the end with a semicolon, and hopefully now undefined would change to Dublin also, you might be using it just for this option. Here is the lots for my intro takes, for lots of my videos because I have a spreadsheet with all the ticks that needs to go into it, and you can set up your animations here and lots of comps in after effects, and just relink and change the text-based on your spreadsheet. Really handy and because I want the date down here or I'm going to do is the city lying here a going to copy and paste it. Get down here, reuse you, and this one here instead of the word city. We'll type we'll give it a name, and we'll call this one Date, and in the Source Text, we still got my expression, and all we want to do is instead of city we are going to look at year. I'm guessing, I'm pretty sure it's year. Where is that, I'm just going to move in that over there, awesome. So that text box there, and that text box there are actually coming from the database. So I can go over here, switch it and say actually let's look at using Auckland like that, awesome. That one there that has the same dataset. So it's the looking at 2016, but we could go in here, and say actually I want to look at the next year, please look at year or the second option which was 2015. Alright, so undo both of those and go back to Dublin, hit save and get onto our line chart. 3. Drawing graph and writing expression: Next up we need to grow the Pinto. Pick no fill. Click on fill, pick none and stroke. You can click on this, make sure you got a stroke going. I've picked a size of eight, I've picked the color of this kind of greeny color just because, and now we need 12 points. If you're terrible with window, you're going to love this one. Click once, click twice, three. We just need as many points as your chart needs. It doesn't have to be in any sort of order. I'm going to have to now go back and count them because I wasn't counting while I was talking to you. Alright, so spin up and yeah, we have 12. Let's rename it all now, let's give it a name and let's call this one line. I think it will update as well and make it look nicer. The next thing we need to do is a newest feature. We need to send these paths into nulls, which is really cool. So underline or any shape that you are trying to do this to, you might be doing this with bar charts. You could extrapolate from this or check out my own other course. But I'm going to go to line, go to contents, got shape, go to path, and then click on the word path. That's the key bit. Click on this, then go to window and go to this one here it says create nulls from paths, and it might be somewhere else. I've got to beat a copy of this. It's in a weird place here, they might have moved it to give it its own little panel in here, but who knows, it might still be there. Let's click on this, here's a little panel here, and his job here, we're going to click points, follow nulls. The cool thing about this is that, I'm going to close it down now, is that what it's done is it's attached all of these different points to these little things here. These are called nulls. All they are is these kind of like empty boxes. They don't print or you never have seen them. You can see here. But the cool thing about them is that, they're connected. So if I go to position here and I kind of roll this round can you see my line comes along for the ride. So the nice thing is we're going to connect these nulls to the different points in my json file and it's going to drag the line around, undo that. Now if yours doesn't do that, you either got to click on this path which you haven't done, or you might have to right-click it and there's an option in here that says " convicted busier path " I think that if you're using kind of like shapes drawn from this option, try that. Okay, I'm going go and rename all these nulls, Okay, so we'll call this one here. You right-click and head rename, or you can just hit Return keyboard and let's call this one January. I'll get to speed this up. Alright, all renamed and you'll see I couldn't work out how to do April. I don't know why. I've got May though. What we need to do now is get them in some sort of order. So just make sure that you're not on your pinto anymore, go back to your selection tool, grab this guy. Makes sure see this point here. That's kind of where his home bases, so make sure he's somewhere in January. Don't worry about the x-height at the moment, but January's is going to line up here and December, I need to line up with December. I'm going for the middle of them. See that little crosses and to somewhere up here. If those two are in the right place, all the rest will get divided out. What I need to do is I need to select January, hold Shift. Click on December. Got them all selected. Go to my over here Align panel. If you can't find it, it's under Windows. Align and [inaudible]. Make sure you're aligning to selection. I would like to do two things. I want to distribute centers horizontally, that just means they're all spaced evenly apart, which is going to line up with my months. Let's get them to line up vertically. So vertical center alignment, that doesn't really matter. It just makes me feel better when they're all lined up because the data is going to drive where they are at. Alright, let's save it. What we want to do is steal the code we've made already. So find date or city. City, and click on the exhibition and copy that, close the backup. Go and we're going to start with that now, my Layers panel is getting a bit unwieldy. So I'm going to hide some lens. See these little guys here, I love these guys, these peaking. Can you see they disappear behind the wall. These are the best icon ever made, I love them, I guess. So turn off the layers. I know they're not disappearing yet, but I want to keep January on and maybe the line in between all the rest of it off. Then turn the master switch for picking off. So if you're opening up my files and you can't find any of the things, you just click on this guy here. All the lines missing will appear back in. So I have my expression, I'm going to put it on January and under transform, and I'm working with position, right? So this first attribute, k, x, I don't want to mess with it, just leave it as is. It's the second option here is what we want to play around with, the up and the down. What I want to do is add my expression. Remember holding down option on a Mac hold on a PC. Click what's in there and let's hit paste. At the moment we're looking at the city name, which is not what we want. If I look at my Json file here, I'm looking for January, I'm looking for this 3.9. If I go into January, once it a city, I want January. Awesome. So that is my 3.9. I have that measurement now by looking in the Json file under weather, after the first result and looking in January. Now I'd like to load that into something. I'd like to load it into something called temperature so I can use that later on. So now the word temperature has 3.9 loaded into it. So what are we looking for? let's jump to the end pot, which means that I want to work out the x and the y. To do that, square brackets and the first value, is going to be zero. If I type in value and leave zero in the middle, it means this first entry, it's not going to change, it's the second ones, put a comma and instead of putting value again, we just give it a name. I want to find something called my y height. That'll do, so that's what we're trying to find. We're going to do a bit of math in here, a bit of expressions to find what my y height is and we'll leave value set to , which means don't change. Let's have a little look. First thing I need to do is I need to go through here and figure out a few things. I need to know what the maximum and minimum temperatures are going to be. I'm going to have a minimum of zero and a maximum of 30 degrees. Just because that's what my graph has and I'm using Celsius. Because probably you're like me. You have no idea how Celsius works. I have no idea how Fahrenheit works, cant do it. Next thing I want to do is figure out how much room it's allowed to move within, because I don't want my line ending up over the ticks or outside the com and below this line here so what we need to do is, I'm going to tool us up. Click off in the background here, grab the Rectangle tool. What I want do is click hold and drag, the kind of height that you want. I want about that height. That there, is the space I wanted to kind of work within. We can figure that out. Its height by going through a rectangle path here and you can see it's about 600 pixels, so I'm going delete that layer. That's my kind of my area for that it can work within. We're going to set something, I'll call it something like Max movement. I'll say "your are 600 pixels please". That's you're kind of play area. Next thing I need to do is work out with zeros. Actually, last thing before I go. Max movement actually needs to be a negative, why? Because you'll notice I've got my rulers on here and you can see zero is actually the top instead of the bottom, it's upside down world. So if you add a negative in front of it, it'll work. So that's just that. We need to work out with zeros because at the moment zeroes in the top left, so I want to give this kind of bottom of my graph, whatever location this is. We need to just grab our selection tool. If you go to info, I would like my zero to be about here, and you can see it's updating over there in the side k, x, and y, my info panel, and you can see y is about 883, so that's my new zero. I'll turn off my rulers, and down here my new zero is going the newBaseline. This newBaseline is going to be 883. Cool. So we've got some stuff. We've worked out our maximum, minimum heights, how much it's allowed to move and what our new zero is. We need to do a bit of expression calculations to then work out what member, I'll bring this up, what my y height, that's what we're looking for. What is my y height? It is the temperature minus even the minimum, and then wrap those up in brackets. I'd like to take all of that and I'd like to divide it by the maximum that gives me, divide by the maximum, that will give me a percentage and in this case, 3.9, all of our maximum, which is 30. So that'll give us our percentage. Then I'd like to take a little of that. Take my percentage and I'd like to times it by how much it's allowed to move, which is this one here I like to copy and paste. I'd like to times it all by the max movement. If I add our newBaseline to it, it should work. All right, you're ready, so we've figured out my height now and there it is there. It gets loaded into my x and y coordinates. Stand back, let's click off fingers crossed and it is 3.9 which is roughly about there. That is it for looking at January and yeah, really it's just a copy and paste for all the other months. So we'll go through and do that now. But sit back, relax. You have made a reasonably complex little expression if you're new to it. Alright, so let's go and start repeating this for the other months. 4. Completing chart animation: Let's copy that, let's twirl it up, and we're going to have to turn off peeking, and we can see all of the months now. We're going to go through, click on February, and you can twirl this down and find transform and find position. A shortcut, if you don't know it, just click on February. Hit P; it will bring up just position. Cool. So I'll do the first one with us, and then we'll speed it up and you can do the other ones on your own. So I'm going to hold down option on my Mac, alt on a PC, delete what's in there, paste it. I'm using my arrow keys just go to the top there, let's see, and about the second line down. Instead of January, so it's going to be February. Cool. Hopefully now, when we click off, it goes to the right measurement. Let's have a little look. It should be something under five. Yeah, 4.4. Awesome. So I'm going to go through now and try and do this all in one big go. So I'm going to go ahead now and copy and paste and change them all. So yeah, time for speed. All right. we're back. yeah, by the looks of it, they're all in there. You can see summer in the middle here. Only 15 degrees. Yeah, here is the year stuff. So it's just like lumped in there now, which is cool. there's what you need. But let's animate this. Just add a little bit of simple animation. To do that, what we'll do is we'll turn on peeking again just so I can see my line. Under line here, we are going to use this one that says add. Line, just where it says contents, click on add. There's one called trim paths. Trim paths is what we write down. You can use end. So make sure your cursor or your playhead is at the beginning here. You can drag it left and right between a 100 percent, you can kind of see it animating there on the screen. So I'm going to start it at zero and I'm going to start this off. After some time, maybe after a second in a bit, maybe that will stick it to a 100%. So I tend to stop watch on at the beginning there, set it to 0 and now I move my playhead up, set it to a 100% and hopefully should animate between the two. Look at that sexy line. A couple of things I want to do just to make it look a little nicer is I'm going to select both of these guys, right click one of them, go to key frame velocity. Here I'll set it to 75 on both sides. We'll have a bit of easing going on now, watch that, just a bit of a nice and movement in the middle there. Up to you, you might turn motion blur on as well. Up the top here, turn motion blur on for the project and then on for the layer. It might look a little nice. Again, it might take a little longer to preview. It is going to take a little while to render. Give it a second. Hopefully now it will loop. All righty. We're awesome. My friends, so that is how to connect a spreadsheet to After Effects. Now why is this so magic, is magic because up here where I've got my JSON file here, I can right-click it and I can go to replace footage, grab file, find some other data. I got Auckland weather, and hey, presto. Look at that, all updated. I'll turn motion there off so it renders a bit faster for you. But yeah, super quick, super easy. So if you are doing repetitive chats, okay, you've been stuck with that job of showing profit and loss and sales and quarterly sales stuff. You want to sex it up using After Effects, we can really quickly and easily once you've got that baseline set up, use JSON to pull in the data and super easy to replace it. So that should get you going if you already know a little bit of After Effects. If you want to know how to do bar charts and pie charts and all those other types of things, I've got a course specifically on this at bringyourownlaptop.com. So go check that out, sign up if you can there is a ton of other courses there. I'm Daniel Scott, [inaudible] like subscribe to my channel if you enjoy it and, I'll see you later. I'm waving. You never can see me waving, but that's all right. I wave anyway. Bye.