Add Cool Responsive Graphs to Your Stock Tracking Sheets

About this Tutorial

Create little graphs with SPARKLINE() super easy!

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

GOOGLEFINANCE() INDEX() SPARKLINE() TODAY()

Video Transcript

<div> Hello, welcome. This video is to go over how to add a cool graph to your stock tracking sheet. So what I've made here is I have a stock ticker here. I have all of the last seven days of close numbers and I have a graph here. But what is interesting about this graph is that as we change numbers, so depending on from the first date to last day, if it's going. 47 to 48, it's green, but if it was going down, it changes to red. And so you can add this cool chart . It gives a visual representation.

It really just cuts through all of the noise of the numbers on your charts and gives you a really fun little thing that you can quickly ascertain if it's going up or going down like the color and gives you a. Chart to show you at least the last seven days here. But as you'll see, you can extend this as much as you'd like. So I'm just gonna show you how to do all of these parts and we'll, we'll get going. So let's say we have some other let's pick out some other stocks. And we're just gonna write some stocks here. I'm actually gonna go over, I'm gonna find some stocks.

Let's say I'm gonna find like five more and I'll be. All right, so I have a variety of stocks here. And so we're gonna have a different amount of numbers here. How we get the last date of, sorry, last closed number is we use equal Google Finance. We're gonna use ticker, we're gonna do D six, and then our attribute is gonna be price. But actually we can do close. I think it's gonna be the same price. Or close. So close, sorry. And then the date is going to be F two. We're gonna end parenthesis and it's gonna go, guess the number and it didn't find it. Oh, because that's not Apple.

One second. Okay. Totally wrong here. That's an a.  What is going on in Google Finance if you're not aware of it? I'm gonna quickly go over this. It's guessing the, the stock ticker that you are putting in. If it is wrong, what I recommend is putting in the actual you can put in the ticker and the place the stock market. So we could have put Nasdaq here for some of these, but Knows very popular ones. It knows already, and I'm sort of guessing, leaving you not to have to add the, the market. Okay? But we get this four data points.

We get the two headers and we get the information here quickly. Let's just index in another video I talk about index and it helps us traverse sort of little data fields . And we're gonna do index and we're gonna use row two, column two, and that gets us the actual price. Let's double check this too. Let's see, let's just double check cuz it, it, it always helps to just double check that we got the ticker correct. They're very close. And actually this is from so what I did here is I just did today minus seven. And so these dates will change every single day.

This is date minus seven, minus six, minus five. So I got the last seven days. We're gonna copy this, this over, so we get all seven days, but as we copy it over, this D six is going to change to F six G six. We don't want that. So you might know already we're gonna use double we're gonna use these dollar signs to hold that. And F two, we definitely want that to change. We want it to change dates as it goes. So now I'm just gonna copy and paste and you'll see it loading. And now we get the. Ba, oh, sorry. The, the price based on this date or this date and this date.

And so now we have a list of all the last seven dates and the close. Now we're gonna create this spark line. We're gonna use Spark line, we're gonna do equal spark line, and we could just do f what row are we on? Actually, I'm just gonna do. We could do that and then we're going to use in curly brackets, sparkling always gets me, cuz it has this curly bracket notation here. Curly bracket chart type . We're going to use comma line. And now this is what it shows. It literally creates a tiny little line here for all of these numbers. And now it's going down. Let's copy. Oh.

So if we wanna copy and paste this all the way down, I think we have to go here and fix our. We want it to change columns across as we copy paste, but if we move it down, we don't want this two to change. So we actually move that sorry. Add a dollar sign to that too. Okay. So now we're gonna select this entire row and copy it here. And what, oh, I see it. This six needs to go. Might have seen that coming from a mile away. . Okay, so now that is definitely Facebook. So now we can copy and paste down here. And now we have a really cool little line chart .

Again, these are all black, so we need to add the red. And the last thing we're gonna do is add the red. But it's going to be a little tricky cuz it's not always red, right? All of these are going down. Let me find a stock that's going up so we can change it to green. Actually, one second. Okay, so I have a stock that is going up, definitely going up in the last seven days. G N C A biomedical. Alright, what we wanna do is we want this to turn red if it's going down over seven days and turn green. Turn green. Okay, let's turn it red first.

After this line we're gonna add a semicolon. We're gonna do color comma, and then in Red. Okay, so that's going to make it red, but we copy paste that all the way down, right? This is gonna stay red, so we want that to turn green. Well, we can't come in here and manually change it every single time. So what we're gonna do is put an if and if F six is less than L six. So this is a true false statement in saying, okay, if this is true, If F six is less than L six, we want green, right? So if that value is true, we do comma green and then comma red. So if it's false, it's red.

We're gonna definitely add this last parenthesis. If you do not, you'll get an error and it'll say formula plus error . We did that earlier, by the way, when I was doing that. So always remember that and it'll always add one at the end. So delete that. Okay, so it looks a little complicated here, but I'll go back through it. So spark line, we want our. . Then we just want chart type line. And then we want our color. If F six is less than L six, make it green. And if it's not right, any other thing, make it red.

We can also do we can also make it black if it's exact, but how often does that happen? Not really at all. So we'll copy paste this down and that should turn green. So now, now our chart has a visual representation.  physically, right? We have a literal chart , but we also have a color representation of knowing if it's up or down. And this is a pretty cool, a pretty darn cool thing you can add to your charts for tracking your stocks, tracking your Bitcoin, tracking, anything that you have, moving numbers sort of day to day, week to week, month to.

You can make these ranges much longer if you'd like. You can also, one extra thing you can make do to make this better is you can put these this table on a separate sheet. So then the only thing you do, you might want a little like visual summary of what your stocks are doing. And you might only wanna see is it up or down? Is it a line chart or an up or down? That might be what you want to only see and maybe you see today's date and.

Hopefully, hopefully this is a little fun and little little introduction or a continuation of your, of your learning in Spark Line. Spark Line is a really, really fun thing to know and, and understand. And if you have any other questions about the Spark line or any other thing, I also have another video about progress bars that I use Spark Line, but this one uses line charge and I think it's really cool. I also think is a really cool use case of line chart . Bye.</div>