Build a Data Visualization Dashboard with Dogecoin Crypto Market Data | Micah | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Build a Data Visualization Dashboard with Dogecoin Crypto Market Data

teacher avatar Micah, Helping you learn to utilize software

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

12 Lessons (1h 31m)
    • 1. Dogecoin Dashboard Course Introduction

      1:18
    • 2. How does this all work? Quick Explanation

      1:22
    • 3. How to sign up for CoinMarketCap API

      1:37
    • 4. Crafting your API query link

      5:15
    • 5. Logging data into our google sheets databas

      8:53
    • 6. Google Data Studio dashboard basic setup

      8:21
    • 7. Data integrity and conditioning

      7:38
    • 8. Building our first chart in the dashboard

      14:33
    • 9. Scorecard and volume time series chart

      12:50
    • 10. Formula to find previous market cap

      11:13
    • 11. Market cap gain visualized on dashboard second page

      8:58
    • 12. Calculating fields and final charts, sharing instructions

      8:58
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

14

Students

--

Projects

About This Class

In this course you will learn..

- How to make an API request for Dogecoin cryptocurrency data to CoinMarketCap

- How to structure a living database that updates live in Google Sheets

- How to filter, clean and condition the data in your database

- How to build a data visualization of your Dogecoin data using Google Data Studio

This course will teach you the basic concepts behind cloud databasing, data analysis and data visualization.

These skills are useful in almost every single career field and organization that uses computers for any aspect of it's work. These days data is king, and that isn't changing anytime soon.

You will need a google account and access to the internet to complete this course. All software is free and open access.

If you have any experience with excel or data analysis software like Power BI, Tableau or other similar tools this will give you a boost. You do not need any prior experience, however this course will feel much more natural for those of you who have used those tools before.

Meet Your Teacher

Teacher Profile Image

Micah

Helping you learn to utilize software

Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Dogecoin Dashboard Course Introduction: Hey everybody, I'm like, and I'm the instructor of this course. And I'm going to tell you a little bit about what you can expect if you choose to watch the rest of the videos. So what you're going to learn is you can learn how to make an API request. An API request is just a request for data from basically a repository of data which is called an API. You're going to learn how to take that data, clean it, condition it, get it ready to be used when we make a Dogecoin market data dashboard. So we're going to be focused on Dogecoin. We're going to all of our data from coin market cap. We're going to use all free software. We're going to use Google Sheets and we're going to use google Data Studio. So a little bit about me. I've been making dashboards like this for a pretty long time. I've made a ton of these and I think that I can provide a lot of value to you. So this will just teach you some basic data concepts a little bit about kind of the back behind the scenes, about data requests and manipulating data and how that all works. And then a little bit about data visualization, charts and data analysis. So I hope you take the course. It's going to be a fun time. We have one main project and it's not going to be that long for your investment in. So I can't wait to talk to you, hear your questions, so see you later. 2. How does this all work? Quick Explanation: So let me explain real quick how all of this works. So we're going to make a data visualization as our end product, something that's useful, we're going to make. You can see it appear in the top left-hand side. This is the picture of it is going to have all the dogecoin market data. And what we're gonna do first off is we're going to ask an API for that market data. Then when we get that market data, we're going to structure it in Google sheets. And you can see that over here, this is the structure and the ad on the does API requests structure it run some formulas on it so that we can get the insights that we want to see from the data and then we're going to visualize it. So we asked for data structuring and clean it and then we visualize it all out. So at the end of it, this whole course is based around a project where you build the same dashboard with me. So at the end of it, please share it in the core section here where you can share your, your, your courses whenever you're done or your class projects, right? Share that out so that we can see it. You can also share your Google Sheet so that I can see that too. So if anyone needs help along the way, please just share that out in just ask a question under this video and I'll go ahead and I'll help you. It'll be no problem. So this is how everything here works. Just wanted to cover that real quick for an explanation so that we can kind of consolidate some of these bigger concepts. So we're about to go into. 3. How to sign up for CoinMarketCap API: So what we're looking at right now is coin market caps Dogecoin data visualization from their database. And what we're gonna do is we're going to ask for permission to coin market cap to get access to their API, which is just going to let us get this data so that we can turn it into our own visualization. So let me show you where you sign up for that. So this son coin market cap.com, and if you go over to pro dot coin market cap.com, you will be able to sign up for their API. So go ahead and put in this information. And then this will allow you to go ahead and get your API access token. And that token, all that means is that's a password that allows you to get the data from the API. So nothing fancy, nothing complicated. Sign-up for this count. And then we'll move on to the next step. Also for more clarity on where to go, you can go to coin market cap.com slash API and then click on this blue button right here in the middle of the screen. And after you sign up, all you should have to do is go to your email and just verify my account to verify your account so that that way you can get in. And then when you get in, it should look like this. And the interface you should be able to just log in. And this is when you know that you're at the place where you can then start to get a key right here, which my key is now copied, but it's going to be shown during the course, so don't worry about it. I'm going to not have this key after this course, but you're gonna have as key here. And then you're going to use this key to go ahead and do your request. 4. Crafting your API query link: So we have the account, we have our key here. Let's figure out how to use this key to get the data that we want. So let's go over to API documentation and let's open this up. So this API documentation is going to be super overwhelming at first when you're looking at it. But there's a lot that you can gain from it. I'm going to take you to exactly where you need to go to get the query that you need. So that you can just get the data needed for this project. But feel free to poke through here and look at all of the data you can potentially get. Now we're just going to be using the data that we can get through our free access to this API. But there are tears and pieces of data that are paid that coin market capital that you have if you pay, I'm not endorsing as you pay anything to them. I'm just saying that there's some things that you're going to maybe want to get for other projects as you won't be able to get unless you cough up some of the sweet moon law. So what we're gonna do is we're going to focus on just what we can get right now for the sake of this project. So you can just use this as a roadmap to build out other stuff in the future when you want to apply more resources to it. So on the left-hand side here we see this cryptocurrency tab. So let's click on that. And then we're gonna go over two listings latest. And you can see right here, I'm going to move myself over a little bit. So we can see on the right-hand side that this right here, this is called JSON. Json is just JSON data and this is a strict structured top to bottom. So it's not going to look like an Excel sheet. But this is what you're going to get when you submit an API request or call. And so you do that through a link. So you're going to take this link right here. And then you're going to add your past your key, your password onto the end of that link. And then you are going to put that into, I'll show you here in a second. We're going to put that into the browser. And we're actually going to see the output of that data. And then I'm gonna show you how we're going to take that, put that in a Google Sheet, Request that data through this link that we're going to craft. And we're going to take that link, grab the data from it, and put it in our dataset or Google Sheet, which is going to be our database. And then we're going to proceed from there. So here this just shows you the columns or the pieces of data that you're going to get. So we just see it will get names, symbol, price, total supply, max, apply 24 hour volume, all this different stuff for the currency that we're getting. So we're going to just at this point in time, pretty much get all the data. I haven't decided yet if we're going to filter down to Dogecoin because if I let if we set this up, it'll be smart because we'll be able to get all the data. And then you can make dashboards on a lot of other coins, not just DOJ. So I think we're going to proceed with you being able to build out for all of coin market caps data. It's not a big deal to do that. So we're just going to probably do this. So and by doing this, I mean, we're gonna get all up and market data for all the crypto is on coin market cap. And then I'll show you how to filter that down. Which is a great just to just Dogecoin in the end. So it'll be a great little exercise and like data conditioning and cleaning. Okay, So where we had this documentation right here, this pro dot-product api dot coin market cap.com slash v1 slash cryptocurrency slash listening slash latest. We take that and then we copy and paste it in our URL. Tap box, whatever that's called, we paste it in there, hit Go, right? We will get this output. Okay? Now, what you're gonna wanna do is on the end of that, you're going to want to put this right here. So we're gonna put CMC underscore pro, underscore API underscore key, right? Then you're going to cut and paste your key in there. So let me show you what it looks like a mind. So this is going to be the full piece right here. I'm going to put that in and that is going to with an approved account activated and everything. This is going to let you get through and get to this data. So if you see this, I have Bitcoin, the date added. I have all of the current market data at this very moment for Bitcoin itself. And this is all available to me. So this is the API requests. This is the link that we need. So you probably thought that this part was going to be super tricky. Didn't really know, like how are you going to deal with it? Oh my gosh, I have to make an API request. Guess. It is like super daunting at first, especially looking all at the documentation. But here's the link. And I'm going to show you in the next couple of videos how we just take that link and we use it to, with the Google Sheets add on. And we just connect to that API, grab the data, put it in Google Sheets and we're good to go. 5. Logging data into our google sheets databas: All right, so let's move on to the next step where we go to Google Sheets. You're just going to create a new sheet that's just docs dot google.com. You get in your login with your Google account and then you're here. We're just going to hit New blank spreadsheet, right? We're not going to really do much of anything to this at all. We're just gonna go ahead and name it, and we'll say crypto data, right? And then let's go over to add ons because the first thing we need to do is we need to add API connector. So we have API Connector. And if you don't have that, you just go to Get add-ons, right? And there's all this different stuff you can use to basically mod the capabilities within Google Sheets. So super awesome in here. So I'll just do API Connector. And over here, API connected, right? So you click on this and then you're just going to install it. It'll have some guides. And then I'm going to show you everything that you need so you don't have to dig through this, but you'll just hit Install and then you'll have it available to you. So let's go ahead into add-ons after you install that and pause the video right now, make sure that you've installed it. And then when you get in here, just go to open, right? And well, this is opening. We're going to have it come up on the right-hand side. Right? And so no API requests have been saved yet. Great. So what we're gonna do is we're going to add new. So let's add New and we're going to do a get request. And it has this kind of fake API URL path, which is not linked that we made earlier. So that path, that URL that we made, we're going to use that here in just a minute. And write here headers. We don't need to worry about those. Authentication. We don't need to worry about those. And destination sheet. We're just going to say set current. And that's going to just tag us to Sheet 1, which is just our default. And for the purposes of this, we don't have to worry about changing what that Xi is called really. So just leave it as Sheet 1 consists. What we're gonna do is have this run by itself. Okay, so let's go ahead and get that link for our requests that we had just a minute ago. And I have it right here. So I'm gonna copy this. And then I'm gonna go back over to my Google Sheet and then I'm going to paste that in. So once you have that ready to go, just put that in and then check us out. We are, we're ready to go. So I'm gonna save it as Though data batch, right? And I'm going to hit Save. Okay? And then let's us go back over here to requests. And then we're actually going to see that right here. So if I go down here and hit run, this is going to run and then we're actually going to see something really cool app and it's going to go out and it's going to grab that data and it's going to bring it in. We can actually see that it just happened here. So let's look at what we have. We have this first row, these are the columns are the headers of each piece of data. So if I run this over and over, it would just stack all the data down the line here, as you see, I've got every request gives me about a 101 coins because there's about a 101 inside of coin market cap. So every time a request that I'm gonna get a 100 and one rose and or excuse me, I'm going to get 100 rows. And when I get that back, I'm just going to stack it up. And what we really care about is DOJ. So we're just gonna go in here and I'm actually going to do a cool little filter, real fast. Data create a filter, will click on this. Clear those coin. And then I'm just gonna say filter to that and then see I have only the dogecoin data, so you don't have to do this in the Google Sheet. You can just leave it unfiltered, but we're going to isolate the dogecoin data later on when we build our dashboard. So I just want to show you that that is also possible here. But for the time being, we can just leave this off, we'll just hit clear. So that way we just have all, all the data or select all. Okay, boom, now we're back to everything. Yeah, don't worry about about filtering that this right here is going to create a system and where you're going to have all sorts of crypto data you're going to have access to. And you can go and request it as often as you want. Or you can set it on a scheduler, which is what I'm about to talk about next. So there's two ways to kinda go at this. So if I go ahead and I say Run again, let me show you how that works. So you saw at the bottom here that it was blank, right? And it just update it. So actually, let's go into Edit. And what we're gonna do for our output options that were gonna say append. This is one setting that I forgot to apply, but you'll see this now. So we did a second API requests and it just replaced all the data, right? It just overrode it. But we want it to do an append. And then we'll do add timestamp. So that, that way we have that. So that's perfect. We'll just make sure we save that. And then let's just hit Run again. So now we just did a request and we should be doing another request and should be appending onto the bottom here. And you see that it just happened right? So now we have a situation in which all of our data is coming through and appending. So at the end of every request, we should have like Raven coin, and then we should start over with Bitcoin. And then if we come down to the bottom, should be raving coin again. If I run it third time. You don't want to tap out this API by running it too much. But the speed that I'm running it, this is just fine. But you see now boom, we've got now up to three hundred three hundred lines because I've done 300 requests, right? So this is what we're gonna do. So think about this happening every day. And if we want this to happen every day, we can go to Schedule. So scheduling is a pro feature inside of API connector. So you would have to actually pay them a small monthly fee to get this to work. However, you can just go and hit this button to update your database as often as you need data, which is really reasonable. So if you are a crypto day trader or a market researcher or something like that. And you just need to get this data in your custom Visualization every day. You just go here, run your request. It will get the data in. It's completely free and you don't need to do anything. And that's how I will suggest you, you move forward in the course, but if you want to pay, you can pay. I pay. So that I can get a request that happens at midnight on the dot every day. So my crypto database just updates, like clockwork every single day. And I just get new market data every single day. So every day my graphs and pie charts, they grow over time. So it's super, super cool. So let's cover what we just did real quick because that was kind of a lot. So what we did initially was we created a successful GET request to the coin market cap API and we got the data that we wanted. And later we're going to filter it down to dosage for the sake of this project. But we are all the crypto data and we appended our data. We added it to a cloud database, right? Some people are going to fight me on this, but Google Sheets, the way we are using it is now serving as our Cloud database where we are putting everything somewhere that it's accessible by other applications that are hosted on the web. And the other application that we're going to ingest is going to be our front end or something you can see and interact with. And that's going to be our Google Data Studio, data Studio dashboard that we're going to make later. We're going to say, Hey, here's our Google sheet, that's my database. Here's the data that request. We'll feed that automatically if you have it scheduled or you're clicking that button every day and then it will ingest it, and then we will display it for other people or yourself. So that's what we kinda just did. And yeah, we're gonna move on to the next steps here. But I just want to get a little recap for you all. 6. Google Data Studio dashboard basic setup: All right, so let's start building out our dashboard. Let's go to Google Data Studio. Data Studio dot google.com, login with your Google account and just like in sheets and then just hit New Blank Report and it'll give you a perennial report. And then it'll ask you where you, where's your data? And there's tons of options for data, but we're going to click on Google Sheets and then we're going to select our crypto data. So the options here, everything is falling, just hit add that should load by default, and then you can just press in. So as soon as we get in, we just have this nasty little table here that we don't want to keep and we have no name. So let's say this is a DOJ. I'll see you all be Dogecoin market data dashboard. And then let's go ahead and work on getting some basic stuff set up here. So bear with me. I'm gonna go try to go pretty slow here as we progress through the dashboard because arguably the most button ology is in here. As opposed to when we're mess around in Google Sheets, there wasn't a lot going on. So over here on the top, we just have a couple of different options for like mainly adding charts. This'll be like the main area. You mess with this button right here. And then here on the side, there's an option for pretty much the entire interface. Like if I click on this chart, stuff will come up in here and the side, and I can mess with that. If I click off of it, then it just gives me the settings for the entire dashboard. I can do things like set theme, that layout. We'll have you go to Insert. I can add pretty much anything that includes like links, images, text, what have you. So we're going to, we're going to take this dashboard and we're going to make it exactly how we want it. And it's going to show us what we want to see of Dogecoin. So let's go ahead and just dive into some basics. I'm gonna take it super step-by-step. So first things first, now that we have this basic theme and layout settings here, I'm gonna go ahead and pick a dark theme just because I like that. To start out with, that means pretty much everything is going to be in dark. Actually, you know what we're gonna do. I'm going to take my theme from image or something you can do called Extract Theme from image. And I'm going to take the DOJ logo. I'm going to do this before I pick dark theme and we take the DOJ logo and use it to extract the image sustained by y. I get that. I'm going to click on this and then we're gonna get the logo in there. So if you click that button and then upload the DOJ logo, what I got was these color schemes. So this is really cool. And it kind of go with this one. This looks nice. Not as must be sort of a ax and a mustard, right? But that's pretty cool. So let's go over to this tab for layout. And what I'm gonna do is I'm going to say header visibility. So that's like the little bar in the top that allows you to see basic details about the app. So you can say like, hey, initially hidden, always show whatever this right here in this chart. This right here is the header view. Sometimes in, well all the time in Google Data Studio you're not going to be all that really see things that the user would see unless you hit View. So edit and review, be sure to toggling between those. Utilize this button a lot when you're working. We'll just say always show navigation type. Navigation type is, let me show you some examples that so we have left. That's going to be if I have more than one tab, so hit Add a page. Right now I have two pages, so I'll just say like this would be like main trends for data and I'll be like changes in volume. Right? That's where I'll kinda do. So now that I have two tabs, I can see on the left-hand sides and now I can toggle between those pages. And just to illustrate even better, I'm going to go to page two. I'm going to work on this page. I'm just going to add some random chart. So you can see the difference here. So that's that sort of navigation for the left. And if I want a tab at the top, there'll be like this. This is why I like to go with, so we'll go ahead and choose this one. I think it's the clearest navigation style. And what we'll do is we'll say display mode fit to width because we want kind of this sort of looks that way, kind of any device. It will just fit to the width and not be a static size. Other than that, we don't have to really worry about much in here. There's some other subtle settings that we could kinda get to, but for now we'll just sort a weed that alone. Okay, next thing I wanna do is I want to actually bring in the DOJ logo. And I think we're going to have like a nice little header right here. And then we're going to kind of put like just a simple like Dogecoin explanation would like a link to maybe some liquid dosage communities right here. And then maybe below, we'll start to do some charts. I'm just going to drag and drop it in. So I have a right here in this folder, I'm just going to drop it in there. That's exactly what I wanted to do. And I think yes, since this is not a PNG, That's where we got all the black from. Because as that black background, I think I think as a PNG, Yeah. It's not a transparent around the edges, but we'll do that. And then let's do insert in this time I swear it will work. I don't know why the image section was messed up, but Dogecoin do like do something like that. And you see I'm working in this textbox and it gives me the option to change the font size, pretty simple editing capability, game and change the font around, right? So it will take it something like that, just some kinda funky little font right there. And maybe crank that up a little bit more. Narrowly great with font sizes, but think this looks pretty good. Let's do that. And then I'm going to go ahead and grab some Dogecoin links real fast. All right, so what I did was I just typed in here, formatted the text, and then just hit this Insert Link. And when you select the text and you say Insert Link, and they'll give you the option to be able to well, I have to take some texts that say I'll do this one. Yes, you put that in open link in new tab as well. I like to set it at, and then you're good to go. So now I have these three links here. So that way if people want to trade Dogecoin, they want to see the DOJ subreddit or the DOJ website. Those are the three main areas that I know that DOJ people exist or like information about DOJ exists. And this will give us like a nice little header for us to be able to just give some people some information about kinda what our dashboards, a bow just hit them right there. And so we're just going to format this super simply. And I'm probably going to even remove this and actually make this larger. Now that I'm looking at the formatting, I kind of want, I'm going to make this wider. There we go. So this right here is looking pretty good except for this nasty little chart. So this is like a great page one. So in the next video is we're going to move forward into the charts and everything kinda build those out. 7. Data integrity and conditioning: So let's do a little bit of housekeeping with our data. First thing we're going to focus on is making sure that all the data has upended correctly in all the columns that it's supposed to. And I ran into this a little bit using the API connector. Usually your first request may or may not have this setting right here at timestamp checked or not. So if you don't have it checked, then you won't get a timestamp in your request and it'll show up with this status code error 0. But if you do request a timestamp will shut you down here. You do get one and you get the error code and it shifts all of the data to the right because it inserts this extra column. And as you see, I kinda ran into that when I was testing, building out some of the data. So we had something in this series to look at. If you go all the way down here to the bottom, you can see right here there's a disconnect right where I've now have the name and the IDs and the symbols like all jacked up, right? So what we need is for this to either be on or off. I'm gonna take it off and then I'm going to save it. I just, I don't need it, but this is going to force me now to make sure that I have all of the data. That's only the non timestamped data. So I'm going to go through here and there's a couple of 100 rows that have timestamp associated data and I'm just gonna make sure I take those out. So let's see, you delete those rows and come up here. And this is really good to just go through and just look at everything. That's what we're gonna do here to make sure that that way when we feed the data in to our dashboard, that we're not running into anything where there's a date in like a name column or something like that. So all right, it looks like we need to take care of this first bit here. Okay, cool. So plus c. And this is still off a bit. Yep, see we had a Timestamp requests right here. And I think that's how this initial header populate. So what we can actually do to just to make sure that we're on the right page, 100 percentage, just go and delete all the data that we've brought it here for test because it doesn't matter. We can run this not as much as we want, but kind of a lot before the limit on the API is reached. So I just run this again. So repopulate all the headers, then repopulate the data. Now we'll only have one set here actually let me do the overwrite. We go. And then from here on out we'll do a pen. So the next request I do should be an append. This should match up. And we just want to make sure that that is, is good to go. So that way every single additional requests this inbound comes in and as all good to go. So here's the line. And let's look through that, see what happened. And it looks like everything is lining all the way across the board. Cool. And that's what we want. We want to make sure that that's consistent. And initially when I had started this off, I kinda ran into that. So I just want to show you how to debug this this issue here. So the other thing that we're gonna do for data that's going to be a little bit tedious, but we're gonna go through and all of these, all these headers, we're gonna give them a better name. So then what they have instead of like data, symbol, Symbol ID, we're just going to do like ID, name, symbol, and so on and so forth. So you can go through here and do this. This will get a little bit frustrating because you can't delete any columns when you're doing this type of data ingest because it doesn't match to the name of the header, like this row right here. When the data comes in, it just goes 1234567. It doesn't match and see like, Oh, did I put the number of pairs in the correct column? It just, I could call this anything and it would just fill Beta in this column right here and l, right? So naming these won't make it so that it's a little bit more readable and easier to use when we move on to the data visualization. So that, that way everything's pretty good. So I suggest going through, there's a couple of, a couple of columns in this. But I strongly suggest to you you can get by, without doing this by strongest just go through and change all these to something that looks something more like this. So if I say like this is USD price, those would be like price underscore USD, easy. Alright. 24 hours, right? Something like that. So I'm going to go through and do that. Pause the video, run through it. I'm going to do the same and I'm gonna change all mine. And I'll show you a sweet little Google Sheets hack that you can use to kind of get all of these done it at once. So if you just want to take these down to a quicker, you just want to form this up at a different speed, right? We can go ahead and copy this initial thing that all of the columns share. Data symbol platform symbol space, right? And we can do Control F. We can find that we found five of those. Hit this More Options button and we'll just replace it with nothing and we'll just do Replace All and then everything will be good to go in there. The only thing we'll have to watch out for is duplicates. So like ID is used a couple times in the schema. So maybe tell us like id2. And with this, we can go ahead and do the same thing, Control F. And this is data dash. So we don't, we don't care about that. We don't want that will replace all those. You see now, everything is looking a lot more like English, right? So that's awesome, right? We can leave these tags here so we don't really care about the tags. But status, let's get rid of that. Get rid of that status bit there. And we're just sort of automating away that manual set of clicks we would've had to have done. So we'll leave all the tags. And we got all of these other nice English. Just straight up text fields that we don't, we don't have to worry about all of that mess in front of them before. So now this is like a more clean, manageable data structures can be easier to work with. So this is really the best practice you can go forward with all that messy data. But if you don't check that your series is appending correctly down the line and you don't make sure your columns are, are readable so you can kind of get through easily. You're going to be kind of just putting a little bit of a, a rock in your path to success. There. 8. Building our first chart in the dashboard: So if you remember all those headers that we just changed, if you had already added your data to your dashboard, know that now you need to remove that data, re-add it again and I'm going to show you real quick, let's go in here at our data back. And then, OK, So you see I have duplicated sheet headers were found in the following connection fields, name and symbol. So that's easy. I just need to go back in here and make sure that this is like name one and then symbol one because that is, that removes a duplication issue. It's going to hit back, see if I'm going to add the data again and see if it'll ignore that. Okay, yep, so when I say add that in, we're going to go, you've got the data in here now. I'm gonna go ahead and resources and have managed added data sources. Just to check on what I've got, make sure those headers are up-to-date. So yes, you see I have those names here that are looking a little bit better than what they were. But the only thing I want to make sure that I have going on is that I have the correct types right here, especially with my big it's so last updated as date and time. They added a date and time. That's good. Let's see. Market cap is a number. Great. I just want to make sure that all of these match up with what I'm thinking. I don't want anything to be like, I don't want the price to be like text. I want it to be a number, right? Yep. There we go. Last updated. Perfect. Okay. And sometimes if you are not the same thing, screwed up with your data, understand that that may be something that you want to go check out inside of the resource section here. So back out of here. And also too, if you ever need a refresher data, go to View and then just do a refresh data and you should be good to go. Now this chart here is almost up because we don't have any dimensions, but we can quickly put one in. Actually, let's just delete that and then we'll just go to Add Chart. And we'll just do time series chart. Put it right below here, expanded out. And let's see, this is going off date added. So this is like when the cryptocurrency was added to coin market cap, not when the value was added. So like last updated would be a good date. Formula for us are here. So let's do last appeal. Let's put it in here as their dimension of our day range. So the only thing we really have this July 31st, right? That's when we actually have that in here. But let's go ahead and get our, our price as our metric. And now let's do this. What we really need to have happen is we need to apply a filter. And we'll have to make a filter that only shows dosage. So we're going to say DOJ filter and we'll say include symbol that equals to DOJ, right? So this is going to filter down all the data down to one specific thing. And let's see where this gets us. So we have our time series here, but we only have one day and we're going to fix that eventually. But let's go ahead and go over to style on this chart. And let's do this. Let's turn it into a bar. This is fine. Let's do show data labels. So this is the price is 0 for one, but pretty sure that's not correct and pretty sure that DOJ data is going to be, I think the price is a lot less than that. So let's do filter. I've seen they do right-click and filter view. Okay, cool, yeah, and we'll go through here and search for DOJ on that column. Let's clear all and only select DOJ. And then let's see what it gives us. There you go. I don't know why it wasn't filtering the first two times. So let's look through here. Let's look at the price. So the price is 20.6. But because we have its cycle to one day, we have it. Adding these two together to get 4.1. That's because in the fairness of this chart, we're asking it, what's the, if you look at how this is configured again, you say, Hey, what's the sum of the price USD for this date? Then we do get this figure. So what we can do is we can say, instead of sum, we can just say max. And then that should give us a rounded up accurate count. So now we put max. We can do as many poles is we want every day and it's only going to give us the max value for that day. So that will kind of be like the market high. So if we pull it in some other days data, then we would eventually have something that represents just the high instead of the top price. Ideally, what you could do is just pull the data once per day only and then you would have the true figure for the time when you did the actual, the actual poll. But this is nice too because we can say max and then we can also do like Min. So you can do the high and the low for the day if you want to do market data that way. And for the purposes of this chart, we could actually do is turn this into just a column chart where we change the style to say, give us only 15 days or 15 bars. So one day on each bar, right? And that way we'll just have 15 days at time represented. And then what we can do, eventually when we have 15 days worth of data is we'll insert a text box. And right here we'll say market price or say we'll say DOJ, price high for the past 15 days. So that way people know what this is whenever they're here. If this is for someone else, then this is really good practice to label all your charts. And then on top of that, this is disgusting over here, this little thing right here. Price USD, that label. So we can change that actually was by going in here and just change the name. And we'll just say rice D. So we'll do that and we can actually go ahead and specify to on that metric. Hit the little edit button, will say type number, currency, and then actually go pick USD out of it. So it's all the way at the bottom, even though it's one of the most common currency she used. I wish that the list and sort like that. But so here we go. So we have our date and we're going to have 15 bars here. When this comes in, I'm actually going to go on and make some dummy data, some, some like a fake entries of like other prices. So that way we can have at least 15 pieces of data or 15 days worth of data. It's going to be like fake, just made up numbers, but eventually it'll show you what your data is eventually going to come to. And actually the way I'm going to make that dummy data is when I'm in here and I'm filtered down to just DOJ. What I'm gonna do is just select a column, or sorry, a row of Dogecoin data. I'm just going to add some rows to the bottom. This is good and we'll actually add a couple thousand rows here, so that way we don't have to worry about our data filling up for a long time. This would be like 62 requests. So 62 days worth of data with it in here. And Google Sheets doesn't really tend to care too much about how little comes in. So what we'll do is we'll take this whole column and we'll just select that. And then I think what I'm gonna do is take the filter off. And we'll just say clear. So Select. All right, And then at the top, I'm going to actually just do insert one below. Perfect. So I'm just going to insert a couple of, of rows here. And then I'm going to fill these up with Dogecoin data and change the date and the price. So let's see, let me get a couple in here. So once I do this and then I'll show you real fast, right? So once I have everything in here, let me paste my one row that I have. And I'll go over here and see that this is all good to go. Let's, let's paste it in for all these other rows as well. So you just have a bunch of Dogecoin entries here. And then we'll go through and trace the prices and the updated dates. All right, so last updated is our measure here. So I'm just going to backtrack this and I'm just gonna make these progressively back dated manually. So I'm just going to say like 28 and so on and so forth, all the way through here. And then I'm gonna do a similar thing with the prices. I'm just going to change the price a little bit. Okay, So I went through and I change the price and I try to make them match as close to possible as the actual price it's there. But once again, it's just for dummy data. It's just so that you can have something to work with in your data visualization. So sometimes it's just really useful, especially when we're doing something like this. We're, we're limited and we can't access the historical data. This is a good way to do that. So we're sort of making data right here that we don't actually have with what we have. And once again, something that I found in here is that there's actually two last updated columns. I do not know how Google Sheets let me get through without catching this, but just be aware of that this is a potential thing that you could face. So I'm going to make this last updated. So don't worry about that. Just things to look out for because what I tried to do is make this chart but I was using the wrong last updated field. Proceed. Now you can see that we have this setup. But what we have is looks like it's filtered with like the, like right, select left to right. In terms of like the future being on the far right, which is what we want for, for our chart so we could do is we think we're going to sort ascending, right? So most recently the price has been up, but in the past little bit it was low. So see, here we go. Now we have our, I believe our 15 days where the data, so yeah, we're rocket. So this is a now at time series chart that's just going to update. If you have it on schedule. If you don't, then it's just going to load in whatever you have for each day. So the only thing that's a bummer with manual, it queries when you go in here and you actually go to your tools and or your add-ons and go to API Connector and actually manually click this each day and run it is that you have to do it every day. Else you are going to miss out on data and you're going to have a gap in your chart. So that's why I have mine set to schedule, but up to you on if you want to do that. So another thing to think about with this chart now that we're building is we could change the date format down here to be a little bit cleaner. So that way it's not like always putting the year because I think maybe the year is assumed right. When you've got that front of the chart, when it says for past 15 days, like you know what year it is, the top of your head, right? So if you go and you click the Edit button for the dimension and the date, you can actually kinda change around what sort of date it is. So if I went and it was like date our than it would give me time. If we went through here and was like day of week. All right. Then it would give me like each day of the week that it was. If you want to mess with time, that's kind of a super cool way to, way to do that. And then ISO week, that's something entirely different. You're probably never going to use. But if you do year, right, that just filters it down to the entire like the entire year. If you do date and time, that's going to give you that long option. So maybe we'll just do day, like month, day. Year, month day is going to be like our cleanest option to just be like July 31st, July 30th, July 29th, on down the line, like super easy to read with a little explanation of what sort of content you're going to be seeing here. And we can even make this a little bit bigger and put this like right there. And that looks clean, right? So we're off to a good start. And let's move on to some more stuff. 9. Scorecard and volume time series chart: All right, so the next thing we want on that dashboard is something that highlights the 24-hour volume for the day. So just a little car that just gives you a summary of what the last like today's 24 hour volume is and then it's sharp for the past couple of days, 24 hour volume. And I did the same thing I did with price. Or I went in and I just change some of these numbers around. So just you can just change them right here in Google Sheets, like just, just like you did with the price. I just change a couple of different numbers just to give it some spiced, something interesting, right? And now we can use that data to push forward and make a chart. So once sort of Life Act that we can do for ourselves, let's go ahead and we're just going to copy this chart. So just copy and paste actually. And then we will actually have this. But then we're going to just sort of slide at below this and put it like over here. And we're going to change a couple of things about this, but this gives us like a template. So we don't have to work as hard with building a new char from scratch. We already have some configuration that we like and it's all kind of setup. The only thing we have to do is change pretty much one value. So let's go in here and find our 24 hour volume. And we'll add is the dimension. All right, cool. All right, so there's your 0.24 hours, That's the sum. But see you one day where it says 3 billion, That's because we have multiple logs on one day, so it's getting the sum. So let's do the max. Okay? And then it'll say the volume traded will be a 1.5 billion rounded up for that entire time period, right? So let's go ahead and make sure that this is a numeric to currency. Let's put it in dollars. So $1.5 billion. Great. Let's go ahead and change this to say 24 are all. So reads a little bit better. And then there's not a lot of variation here because all of our data is kind of around this same spectrum, but this one should be showing up in a couple of other. It should be a little bit lower. It shouldn't be stuck just at 1.5. So let's see if we can get the numbers here to be a little bit more like specific, instead of just rounding in a certain direction, which is kinda weird. And the reason why that was happening, which had just looked real quick, is that we had not refresher data and I just made those changes and just go to View and refresh your data. And now we see all those numbers are in there reflecting exactly as we want. The only thing we have, it's a little bit interesting is the labels. So what we can do is we can maybe take our number down to like 10. So that way the number of bars is being shown as only 10. And that way that labels will always fit in here really nicely because they'll just have the last 10 days in there. And we can kinda crush this down and maybe move this up a little bit with its label. I think we can get right, I'm below that. And I'm going to copy and paste this text box over. And if I can ever copy and paste it, put this right here. So we'll say DOJ, 24 hour volume, 10 days, right? And with with this, let's make sure that we have this sorted so that that way we are getting the most recent over here on the front, which it looks like we might not be getting that. Descending. We're not getting that. So the way we can fix this problem real quick is we can set a custom filter so that, that way we see the last 10 days. So if you on this chart just go to the date and then you get to click on Custom and then hit this button right here if you all these options, but you're gonna wanna go to Advanced. And what we'll do is wash you. Just do a query where we start ten days ago and end on our current day. So we'll say start date today minus 10 days, and so we'll start on the 21st and end on the 30th. So actually we can do something where we say today plus 0 days or something like that. There we go. And then we'll apply it. And then it should show us pretty up to date. We might have to change this a little bit. That way. It is showing us like kind of not showing with the 31st and I don't know why because it says that it should be pullets do plus and we still don't get it, but that's okay. We can go over here to style and see if we can squeeze and other one out of it. Yes. So if we make it 11 days, there are 11 bars, then we're good to go. We can turn it out a 10. Let's see if maybe my sort, let's see descending. Ascending. Yep. Just doesn't quite want to let you have the 31st. So what we can do though, is we can just change this to 11 and we can just pretend nobody notices, right? So there's some things about Google Data Studio that aren't quite all the way where I would like them to be personally, just things like this where and maybe it's me, but I thought that I query was pretty good to be able to do the lab past 10 days, but that's alright. And we can also do that here as well on our top chart. And this is a good thing to to set. So let's do custom date range advanced. And they'll do today minus 15. And this will actually be an interesting test to see if it will show the 31st on this chart and it does. And I don't know why I think the amount of bars in the chart sometimes it's like we don't care if you wanted your value all the way on the right to be the 31st, we're not going to give it to you unless you give me more bars. But that's okay because we're still we're still accomplishing what we want. So all right, cool. So now we have this right here and this down here. And let's actually pick an alternate color for this because I don't like having two bar charts be like the same color. So We'll see if we can get something that's just a little bit cleaner and maybe not, maybe not your white right here. Let me see if I can grab another hue through here that's not mustard. Okay, that's a nice little offset. Okay, now let's go ahead and add in Scorecard. We're just gonna do a scorecard. And this is literally just going to show us a copy and paste this so I can give it a label. And then we'll say, I don't need to have it be all caps. So we'll say DOJ 24 hour volume today. I'll center that nice and neat. Then we have this horrible looking thing right here, which will clean up. Let's center it, hide the metric name, and then we're going to make this really large number. Okay? And so right now it's giving us a sum of price, but we're going to want 24-hour volume for today. So let's say the metric is 24 hour volume. Okay? And this is the sum. So let's make it be the max. So that, that way we have 24 hour volume and then we'll go style. Let's do compact numbers. So it's like 5.7.7 bill. And we can even give us some more decimal points if we want. So we can expand that out. That's fine. Let's do that. And we will make this a neat little square. Let's give it a background color of one of the DOJ. Highlight colors. Let's do that. One's kinda too light. What aesthetics are important for your, for your stuff. So don't, don't waste time on design. So that, that's fine. We'll me to do it like that for the aesthetics anyways. And it looks fine. Kinda, kinda skipped that, center with that. And move this down a little bit. These things are kind of hard to move unless you grab them right on the edge. So we have this. And what we need to do now is we need to custom filter this to be always today. So we'll say today, and this is b, that preset item here. So this says that it has no data for today, but I'm pretty sure we do so standby well, we figured that out and just to make sure I went verified and the data that yes, we do have a value for today for volume 24 hours. And so I changed this again and it worked and it came up with the it did not come up with the correct figure because we should be looking at like 1.5 billion. It's saying that the 7 billion, so this is, this, this is something is off with this. And we're going to figure this out. So a lot of this dashboard building is a lot of like debugging of your data in real time. So all of these little kinks and we're running two are going to be really helpful for you to know sort of how to problem-solve to fix these types of things. So debugging this, I figured out exactly what we have going on. So this is super useful. So when you do a chart like this and you're filtering and Dataset, don't forget to add that filter. So we had that DOJ filter and I forgot to apply it. So you have to apply it to everything in the chart. Where else you're getting that some of like everything that's in your dataset. So super crazy, right? Also I had the date added as the date, and that's the date that the cryptocurrency was added to coin market cap, not the date that the price was added. So I put in last updated. I got my volume 24 hours. I've got my max there. I've got my custom date range from a set that and that is today. Apply that. And it doesn't like that. So we're going to be back in the sauce real quick to figure out what the deal is. Let me do two days and see if it doesn't like that. Data Studio cannot connect to your dataset. The underlying data has changed. Oh, I think I know what it did. I think I put in the wrong last updated field. Standby. Let's check on that. So all I did to fix that and to get the correct number of 1.49 billion is I just deleted that other scorecard chart and just reloaded another one and just reconfigured it kinda the same way. I just had to change the D field and that was pretty much it's sometimes stuff happens like that. So if your data source ever gets broke when in doubt, just refresh initially and then just check your datatypes and your resources and just remake the chart and see if that'll work. 10. Formula to find previous market cap: So our next goal with this dashboard is actually going to take a whole video to explain. And it's super important because we get this really cool piece of data that isn't available many other places. So our main goal, what we're gonna do is we're going to find the last recorded market cap number and then compare it against our current market cap number. So we want to see the difference. We want to see if we gained or lost market cap. So we're going to write a formula to look back in time and our data. And I'm going to give you the formula. So don't worry, it's a pretty long one. And we're going to select the row that has the last recorded market cap figure. So the way this formula works and just plain English is we're going to search for our most recent previous match up DOJ. And then we're going to select that cell that has that last recorded entry from market cap. And then we're going to reference it. So it'll, this cell is going to be like our previous, like previous market cap is what it is. Then what we'll do is we'll inside of Google Data Studio, subtract the previous and the current, and then we will know the difference. So we'll be able to say to go up or down. So that's the goal of this and it will show you this formula. Don't get overwhelmed because you'll have it, you'll be able to manipulate it. And if you're she is set up just the way mine is, which you'll pretty much have to be if you follow along, you'll be okay. So here's our column with the formula already filled out. I've called it previous market cap. So let me explain to you what you're looking at. This right here in the cell. This is what you're going to put in the cell. You're going to start with an equal sign and then the rest of this. So what are we doing? Let's talk through this first line right here. So we say equals index. So index means basically we're searching through all the rows and we want that specific row. So we're referencing BJT to be J4. So we're actually going through and we're getting all the way up 21 past or searching this area, which is the market cap. And then we are getting large, which means like the second most largest element from the dataset. So we're going to get one down from that. And that'll be, we're only going to do that if J3 equals a match within J3, J2. So somewhere within this range, and j is our symbol. We go all the way over here and we can see J is this symbols. So we're going to be going through looking for that symbol, like we said, finding the match. And then we're going to get that row within that range. And then we're going to get the row number. And then we're going to get one after that. And this is just gonna be super confusing, but I'ma show you how it's working out right now. So this is returning a value. So right now we have previous market cap and current market cap, right? We don't have any previous up and up in here. We don't have any previous recordings of DOJ. So right now the previous market cap is the current. Right here on this next line you can see the current market cap is like 2.8 and the previous 2.69. All right. And then over here, so current 2.99, previous 2.89. So it's grabbing that if it finds a match. So this is kind of an interesting thing. The one thing that you have to change, right, is when you have, when you first have your market data like Bitcoin, right? I pick one has no other previous matches. So we had to kind of modify all of the first recorded instances to just reference themselves. So that's why like let me show you here. Yep, Perfect. So like if you see that reference right here, if you need it to reference the past, then you need to change this to be one previous. So like if I needed this dereference, a path that would change this to J2. But it can just reference itself because it's the first one. Let's look how to make sense going down. So like right here, we're on line 6 and I'm referencing five, so I referencing for five up, I'm not going to reference six because I need to look into the past to see because these are all Dogecoin injuries. And I know this is getting getting pretty confusing, but let me show you some examples of how this is. This is working so that way you can have some confidence in it. So I went all the way down through here. I'm, I did like another run of the data. Right? And so Let's go down here. So like here's an example of one. We have 4739 as the previous and 47 36 as the current. So let's actually go through, find that. Let's copy this. Find it. There should be only one other match, right? So we have this entry on row 39. Let's see what the symbol was for that. So that was FIL file a coin. And let's go look and see on row 139, That's File Coin. So this is sort of that data verification to make sure we got our formula right. So this one, look down here, this is doing exactly what we wanted to do is looking into the past. So this is saying, look at row 138 and up and find me a match, and then bring me back that value that matches that. Because I want to look for it says, find myself, find my symbol in the past. This is what this is doing. So if you set this up and it isn't working, chances are that you have messed up on this right here. This specific value. And all of your rows and columns should be set at the same as mine. So all of these, j's and b, j and j and b j are the only columns we're messing with. They should all match perfectly. So you shouldn't have any like hashtag errors or hashtag numb. You shouldn't have any sort of issues coming through. And it, you should be pulling the past schema. Now if you look in, it's giving you an error. That's because you haven't calibrated to look for itself because it is the first value in there is nothing in the past. So I've think I've explained this well enough, but go ahead and get this, set this up to calculate. And then what you're gonna wanna do is this is going to need to go like way, way down the line. And you're going to have that num error right there. So so if this is blank, we would have normally gotten a number air and I'll actually show you what that error would have looked like beforehand. And I'm going to replace this with an equal sign. So you can see that beforehand we would have gotten this Num Error, which will throw our whole dataset off. Because we either need like a blank value or we need there to be a number there. So we can't have this num error or it's going to throw off our entire dataset. So what we'll do is we'll just put a little clause in here and we'll just say a if it's blank, is don't worry about it. So if you plug this into the front, this is the smartest way to build this out. So we'll say if this value right here with this date is empty, we'll just pick anything, but we'll just say, Hey, that's blank. There's no data in the row. And then what I want you to do is fill this column which is nothing. But if there is something there I want you to execute, go through, find the value and proceed. So that's how you do that. So we'll do that and we'll take this and we'll literally just drag it down to infinity. And this is going to be kind of a like a lengthy process. And the only thing about this is probably the most caveman way to actually do this sort of data formulation and databasing inside of Google Sheets. Eventually like something might break. But I wish I could just calculate this column to just know that this is the case for every single row that's ever gonna take. But the way that Google Sheets is built up as we're using it for a database. It's like an open source solution. It gets you there. But you have to do a couple of different like hacky things to make it work. But hey, in the meantime, you are learning about how to write some pretty cool formulas that can be pretty useful in the long run. So anyhow, we have all the previous market caps and here we have all these blank values. And as the new data comes in, this should calculate just fine because we put this column on the end of our data. So it's not going to interfere. It's going to be like one of the last things, it's actually in there. So this should calculate the previous market cap. And now we're in a place where we can go ahead and move back into Google Data Studio and we can make a table chart that shows if it went up or down or what happened, what the difference was. 11. Market cap gain visualized on dashboard second page: Okay, So we're back in Google Data Studio and our goal is going to be to use that previous market cap column that we just took all that time calculating to our advantage. So let's go ahead and go to resources, managed data sources so that we can bring that new column in there. Because remember we just made it. So if you go to Edit and we look in here, you're not going to see previous market cap in here. It's not in here yet. We have to do is we have to actually reconnect to our, our dataset. So we'll say done at it. See here. I was just there. So this is a little bit tricky at it. Edit. And then you actually go back to Edit connection and then you can hit reconnect. And then it's gonna say, hey, you've added these new fields and we'll say, Yeah, I did. And we'll apply them. And then they will be inside of your dataset. Previous market cap is a number, Cool. And we'll hit Done and we'll hit Close. And then just for everything, we'll hit refresh data just to make sure that we've got that down. And then remember when we did that, that tab, Let's rename that to say changes in market cap. Right now we have over here a little broken chart that does nothing but let's go ahead and add in table bars because that's how I like to do these. And what we'll do is we'll go and we'll do. What I want to show is the difference between the previous and the current market cap. So this should be pretty easy to knock out here, and we're just going to calculate it all inside of Google Data Studio on this side. All right, so what we're gonna do with our new chart first and foremost, let's filter down to DOJ, put our DOJ filter on, and then let's get our dates correct. So we'll add in last updated. I can be art date. And then I'm going to add that infer the dimension as well. Okay, So we have that and we have we don't want the record count. We want somebody brand new. We're going to make up a new metric and we're going to create a field. So we'll say we're going to call this market tab gain or loss. And we're gonna say market cap was, we can just bring these fields in here. So we'll say market cap minus previous market cap. And we'll say the type, this is a currency and this is in US dollars. And we'll apply that. And let's go ahead and see if we can get this to show up in a like a dollar dollar figure here. So we still have that record count narrow, so it Record Counts gone. Okay, market cap gain or loss. We had this sort of odd visualization going on right here. So let's figure out how to get this to be a number. And if we want that to be a number, I realized that was using the wrong chart. I'm doing a table of bars, but what I want is a table with a heatmap. So boom, that shows that immediately in there. So now we have this figure will have to determine whether or not it's correct. We'll have to check these figures real quick and make sure they're good to go. But let's do a little bit of styling first because there's weird stuff going on here. So let's go to style and collapse that top bit there. But let's take the table labels and make them like a little bit bigger so that our numbers are just a little bit, little bit larger. We don't want row numbers, we don't care about them. And then maybe you'll see if we can center these and then we can kind of grab the chart. You sort of move is to the middle. So that way it's sort of like here's a middle ground. And then let's see from here, I think we're doing good on speed dial. We want to change this last updated one. We're gonna make sure that's reflecting something that sounds like English, so something like date. And this is set to date and time, but we could just do there we go. Just date. That'll work just fine. That looks pretty clean to me. And we have everything centered. The only thing we have to do is just go and confirm that all of these are reflecting correctly. And also, let's sort the data. So we want to sort by. The Let's get that in there sort by descending. I didn't not want it to be, the new field. Doesn't want to sort by date. Hmm, interesting, Let's see here. Okay, so I gotta do is click on this and then just select the chart fueled to date and then just hit descending and that it should be all good to go. And if you bring this down, we can see this goes back as far as we want, as far as we have data in new ways. So all we apps to do left is verify. So let's see. Did it go up a 125 million? Let's find out. So July 31st, DOJ. Lets see what we have to top. Last updated. 31. What was DO? Think that that's going to filter down to DOJ only. There's DOJ and the row 23 skew over and see wasn't market cap, It was this. So what I did real quick to check this was I just took the Google Sheet. I just did exactly what I did there. I just subtracted the market cap from the previous market cap to get these numbers. And yeah, we have the same round numbers that we're looking at here, like July 27th, 13 million. So I got 27 and I can see it was thirteen million. A hundred and twenty-five million. Yep. There's that. And the only thing that I think would help out too, is I did like compact numbers. So if I go in here and I say, I think that they'll give me compact numbers. Boom. Yep. So there we go. And so you can see like on the 30th, it actually went up to 0. I think because there's no comparison that day or something like that. Anyhow says no gain or loss. And so anyhow, this is what we have. So it's like negative or positive, right? We can see the heat map is reflecting basically like if you have like really red, that's going to be like super high gain. And then if you have the opposite of red, then you have like a big loss, like a 13, $1 billion loss, right? These are all numbers that I made up. So this isn't directly correlated to DOJ. I should probably have plugged the real numbers in there. But you can see now that we have, yes, we have a field that actually shows us the up and downs of the market as it goes, right? So this is super valuable because a lot of people want to see how things progress, right? And day-by-day, you can get a quick snapshot of like how much the market cap is going up or down. And that's super valuable to help you figure out where the entire coin is trending. So that's why I care about getting this metric, but it's an interesting exercise in manipulating the data, working with it, and actually getting something that's valuable out of it. So I'm gonna delete those columns because they were just for practice and we're good to go now. So now that we got this part right here, we'll just in the next video do some finishing touches and just sort of clean the sheet up and share it out. 12. Calculating fields and final charts, sharing instructions: Okay, let's go ahead and do some labeling here. We'll go ahead and insert a text field at the top. And we'll just say daily market cap or loss. And we'll make a pretty big, we kind of already have that there with the column label, but we'll just solidify that we have that there. And then the only other thing that I think will be useful for this is we'll just do a little scorecard here at the bottom. Lead that a tad bigger and we will make this for just a number a little bit bigger, center that hide the metric name. We're going to make this for the percentage of change over 24 hours. So let's make sure that we have our filter for Dogen per usual. Let's make sure that our date is good. So we'll do our whereas it last updated as our date and then we have the field it's percent change 24 hours and we'll bring that in. And then we'll have the sum. And this is kind of like everything that DOJ has. Like I think gets the entire dataset added up. It's all of this 24-hour changes. So we're just going to say this is a numeric percentage. And then let's go. And actually that jumped that way up. So let's see. We can change this formatting here will go. So let's go to date and custom date we'll do today. I'll apply that. Okay. And then sum, let's do max. So that, that way we have the same thing in the same day. We're good. Okay, So one thing that I really do not care for in google Data Studio, but this is to understand how to get around it. This is going to say this is 341% when it's really 3.41% for the 24-hour change. So or something comparable to that? I'm going to make sure that that's correct, but I'm pretty sure that that's the case. And if you format it as a percent, it won't let you change where the decimal is at. So if we just put down a number, we'll get that 3.4 that we want. And we can also go in here and get this a metric name. And we can change this and make this pretty. And we'll just say percent change. I think it's in price and price, right? And we'll put that there. And then that looks all nice. Nest and ISIL scorecard for us. And we'll be, we'll be good to go, but we cannot put that percentage in. So with that being said, that Google Data Studio does not like to let you put in a proper just clean 3.4 percentage in there and it makes you do all of this. You know, you have to do a lot to get around. I'll show you how you do it. So what you do is you actually let me go grab the formula here and then I'll explain it because I've already built it out. So this is a formula we're going to use to get this right here. But let's go ahead and start off with the 24 percentage just as it is. So I'll put that in. I'll do my max. I'll say that it's a percent. Okay, So we get this horrible thing, right? 341%. And why is, why is it not letting us do this? So, great, what we're gonna do is we're going to say, we're going to add a field. And we're going to say calc, calc, 24 hour per cent, right? And then we're gonna put this formula and you can use this exact one. So this is going to concatenate the max of percent change 24 hours, right, filtered over that time range. And then what we're gonna do is we're gonna do substring. Which substring just means I'm going to take out a pair of scissors and cut something out of it. So I just said from index one to three, I want to cut out that. So basically everything after the first decimal place we're getting rid of. And then we're going to concatenate on a percent sign. And that is going to be what we have in here and I actually just remove that. So let me put that back on and I think we're good and I'll hit Save. And then let's go ahead and look and see where it's at. And I've got that calculated in there. And it should be like a text field. And we'll bring it over into our metric and then it will display. So this is going to be weird because it's a number, but you're making it a text field because you have to calculate it because what's actually coming out is text. So think of that. When you're focusing on the datatype, right? Which kind of morphing the datatype, jamming together a percent sign, cutting out some of the data in the actual percentage, which is like a bunch of decimal places long and here whatever this is like eight long, cutting some of that out so that we can get just what we want. And that's that. So now we have that sweet little situation right there. So we'll say, we'll do something like that. And we'll say, let's give it a background. Do some aesthetics real quick. And we'll even do some border-radius. We'll do ten on the border radius to sort of center it. And then there we go. We got a little dashboard here. So if I hit View, we're actually see this is the main page that you would show up on. So we'd say, all right, here's Dogecoin, here's the price high for the past 15 days. And we'll see, okay, well, I don't want it to $0.21 kind of hanging out there. Those 24 hour volume the past seven days, right. So some days it was treated a little bit more heavily than others. And then here's the volume today, which is kind of a quick peak right here. And then we can see changes in market cap. So we can see this right here. And then there's a bunch of other things that we could do with it. But this is giving you the foundation to be able to go forth and add just whatever you want to, your dashboard that you make. You can mess with this and make it to be seven different pages of very nuanced interesting angles on Dogecoin's market data, right? But just to recap everything. So we have this living database now. You can bring the data in as much as you want. You have all the calculations for all the metrics you want. We did all that. All this data is clean, ready to go. It's being ingested into here and any changes in that database will reflect in your dashboard here, live for you to consume. So let's focus on sharing real quick. That's an easy win. So if you just go and you just do share, what you can do is you say manage access and you say anyone on the internet can find in view and then use a save. And then that's kinda where we're at. So now if I give somebody this link, anyone can save him, view it and you can take it in imbedded places or put on a website or just save it in a bookmark or whatever. And it'll always be there and it's all free. And that's that. So just remember to update your database. That's pretty much the only thing you have to have going for you to keep this thing rolling. So I hope that you guys really got something out of this course because now you know how to make like API calls. You know how to get data in, clean it and structure it and bring it into Google Data Studio. And the actually build a dashboard with it. So this is a big stuff, really cool. If you made it this far. Thanks. Please share your project with anyone in any medium that you're finding this if you're on Skillshare, go ahead and drop that into the course resources and definitely break this class if you're, if you're able to give a full review, please do. Let them know what you think about it so I can make future stuff better for you all. And all said, thanks for hanging out and have a good one.