Magic the Gathering Crash Course

[00:00:00]Data science comes down to three things.

[00:00:02]Number one, critical thinking, thinking critically. I'll talk about that in a second. Two, is data and the power that comes with it.

[00:00:12]Incredible.

[00:00:13]We'll get to that in a second, too. And then the third thing is web scraping.

[00:00:17]Enough said. I guess I will say more later, but that's going to be the premise. Today we're going to talk about lot number two, about data and the power that Magic the Gathering and the data behind it has, and how you can leverage some of that data in Google Sheets using XPaths and Google Sheet formulas, and be your own data scientist starting today tracking Magic prices from TCGplayer.

[00:00:40]But, hey, what's up? My name's Nick.

[00:00:43]Subscribe. All right, great intro. Okay.

[00:00:46]So, first thing, critical thinking.

[00:00:49]I invite you to think critically.

[00:00:51]My comment section is really good at that. They listen to me and they think critically. I'm just kidding.

[00:00:57]Um but seriously, critical thinking critical thinking is being argumentative, it is being uh combative.

[00:01:08]You don't have to be rude, right? But you need to oppose what the dialogue is to think critically.

[00:01:18]So, if you just listen to people and you're like, "Oh yeah, that sounds good.

[00:01:21]That's great." You're not critically thinking.

[00:01:23]If you listen and you say, "Yes, but" are you annoying? Yes, but you're also thinking critically. And so, do it. Think critically. Don't be annoying. Don't be a jerk. It's not a free pass to think you're a know-it-all or whatever. But, if everybody in the world says this Magic set is going to fail or this Magic set's going to do well, or heck, if I tell you this Magic set's going to do well or fail or whatever, think critically. And many of you are very good at that, so my hats off.

[00:01:55]Number two.

[00:01:56]For being a data scientist, you need to understand data and the power that's behind it.

[00:02:01]There's a lot that goes into this.

[00:02:04]And and a lot that goes into this with magic.

[00:02:07]If you don't think that Wizards of the Coast hires teams of analysts and data scientists that are crunching numbers, that are web scraping, that's number three, we'll get to that. Web scraping price data, sales history, all these things and doing analytics on what is the minimum number of cool special guests they can put inside of a booster box to get it to sell out as quickly as possible.

[00:02:33]You're wrong.

[00:02:35]You're wrong. And now the data scientists at Wizards of the Coast is watching and and shaking their head cuz they're understaffed and overworked, but but still you exist. So, um you know, my point is made.

[00:02:46]And there's it's because you need it. And in this modern economy and modern day to be strategically dangerous, you have to have scientists and analysts and people that know what they're doing or at least know how to crunch the numbers going through it and making recommendations on what kind of choices you should make based on the data that is out there. And that's a lot of what data science is. And you can call it many things, you know, you can call it you're an analyst or a business analyst or you know, whatever, but at the end of the day, if you're working with data, you're crunching data, and you're using it to solve real life problems, uh you're kind of a data scientist. So, we'll get more into that um about exactly examples in magic and things like that in a second, but let me talk about web scraping real quick. Web scraping is how you get data.

[00:03:37]I worked as a research assistant at college and I was around many many very smart people, much smarter than me. They would go on to get PhDs and stuff like that. I did not get a PhD.

[00:03:51]But these people, um there's also many many people that like me didn't get a PhD and just went out and worked and made money.

[00:03:59]Boo.

[00:04:00]And the number one skill that came back cuz my professor would kind of keep in touch with a lot of these research assistants and say, "Hey, what was the most valuable skill you learned while being a research assistant? What did you use out in the real world as an economist, as a data scientist, as an analyst, as whatever?"

[00:04:17]So, you know, all this white-collar shin and shin dick ness.

[00:04:22]And it always came back web scraping.

[00:04:24]And there's a reason for that.

[00:04:26]Web scraping is how you get data. All the power that comes from knowing what cards are going to spike, from knowing the prices of sealed booster boxes and where to get it, at what price, at what deal, at what time, it all comes from web scraping.

[00:04:44]And so, that's that's the skill. And we're going to do a little bit of that today. You're going to learn it today with me, right now.

[00:04:50]So, let's let's start with the basics. And the basics you have a collection or a portfolio, if you want to call it that, or if that grosses you out, you you can call it a collection, and you want to keep tabs on how valuable it is.

[00:05:07]There's lots of ways that we could go about doing this.

[00:05:09]And I am going to demo with you right now. Let's change the screen what it looks like if you want to build yourself a little spreadsheet. And so, mine's called Commander Decks, ignore that, um because this is going to be my little portfolio of my magic products, the stuff I've got on this shelf, and we're going to I'm going to show you how you grab the prices from TCGPlayer and can track it and turn this into a cool tool that you could use in your day-to-day life. And guess what? I do use this. I'm not going to show you my actual sheet, but I have a sheet of my collection and what it's worth, and I keep tabs on it. I check on it probably once every other week or something like that. And if something spikes and hits a price where I'm like, "Oh, it's time to sell." That's how I know. That's how I know it's time to sell. And yes, you can do this by just going on to TCGPlayer and looking up every single product, but a big part, you know, maybe the fourth thing in being a data scientist is automation and using technology to make your life easier, and that's what we're going to do today. So, I've made a spreadsheet.

[00:06:13]It's got four columns. The product, the buy price. That's how much I spent on it.

[00:06:19]The TCGPlayer low price, which is what we're going to grab.

[00:06:23]And then, if I sold today, how much money would I make? I love this column.

[00:06:26]I think it's a great column. I think everybody should be aware of their collection and how much money would you make if you sold it today.

[00:06:34]Uh it it information gives you the ability to make optimal decision-making.

[00:06:40]And if you have, you know, whatever, a Kaladesh Invocations Trinisphere. Ooh, shiny. Thanks. Thanks all. Thanks for enjoying my shiny Invocations Trinisphere.

[00:06:53]And you realize that you could just have a nice Benjamin if you sold it, and you want that Benjamin, then, you know, you go ahead and sell it. But you wouldn't know that unless you knew the price. So, anyways, this is good tool.

[00:07:06]Good tool to have.

[00:07:07]So, what are we going to start with today? We're going to start with these things on my shelf, and I've got a War of the Spark booster box. This is back before play, before set, before all the collector shenanigans. This is a just regular War of the Spark booster box.

[00:07:25]What did I buy it for? I bought it for 160.

[00:07:28]Very cool. Great job, Nick. You're great at getting good prices. Thanks all. All right, TCGPlayer low price. This is the secret sauce.

[00:07:37]If you haven't used Google Sheets before, it's very powerful. It's like Excel.

[00:07:41]Um if you haven't used Excel before, then you must be such a happy person.

[00:07:46]So, there's formulas. In formulas, you can put an equal sign, and then you can type whatever. You can do 8 * 9 and hit enter, and then boom, it does the formula. It tells you what that equals.

[00:07:56]But, you can do more complicated formulas. You can do a formula like equals, and don't worry, I'm going to paste this below. But, if you want to follow along and like learn how to do this stuff, then more power to you because it's pretty cool.

[00:08:07]Equals import XML. Imports data structures from online. And you can click on that, and import XML. Oh, and check this out. There's a formula help.

[00:08:18]You can click on this question mark, and it will tell you like what these things mean and what they are.

[00:08:26]And you can see this formula, import XML, and it has an example of Wikipedia.

[00:08:31]Very cool.

[00:08:32]We are going to import XML from TCGPlayer, and I want to know, whoops, spoilers. Let me exit out of that. Um, and I want to know I want to get this price, this price right here.

[00:08:43]So, this is important. The way this works, you choose a URL, you choose an XPath.

[00:08:48]An XPath is like a treasure map to a spot on a webpage.

[00:08:53]And so, if I choose the XPath that goes right here, or to this number, it's essentially a map to this spot, to this number, how it's embedded in the website.

[00:09:07]And so, keep that in mind because this number right here, this 178.28, is the same price is the lowest priced listing that doesn't have a picture.

[00:09:18]And how do I know that? Just from experience using TCGPlayer. But, that's what I want for my portfolio, I want to know what is the lowest listed price for my sealed product that doesn't have a picture cuz the pictures can sometimes be other products, and it's a bunch of shenanigans.

[00:09:36]So, what do I need? I need the URL and I need a XPath. Let's grab the URL. So, URL is the link Whoops, what did I click on? Go back. All right. Uh is the link to the website. Uh I guess you can't see, but I'm clicking on I'm clicking on the URL. If you don't know how to grab URL, then I'm sorry. Uh double quote paste double quote comma double quote double quote close paren hit enter.

[00:10:02]So, uh the reason I did that was just because we need to fill in this XML or sorry this XPath.

[00:10:08]And then we'll be done. So, this formula here import XML, you paste in your URL, comma paste in your XPath and all of that in double quotes and then you're done. Right, let's grab an XPath.

[00:10:21]So, this is the tricky part and by tricky I mean it's not actually that hard, but if you have no idea what you're doing then it's it's it is that hard. So, you find the number you want. You right click on it and then you click on inspect.

[00:10:36]Boom. Then it opens up all this codey stuff. Don't be scared. Take a deep breath.

[00:10:42]Then you right click so it's you know, you find the number you want. 17828 that is the number I want the 17828. Also, when you hover over it it uh highlights it which is very cool.

[00:10:54]And it calls it the spotlight price.

[00:10:56]Very cool.

[00:10:57]Right click on that. Copy copy XPath.

[00:11:03]Oh, could you even see that? Let me try that again. It copy copy XPath. Yeah, now you can see it for sure.

[00:11:12]All right. Let's go back to our formula.

[00:11:14]We double click on this.

[00:11:16]We go back to these double quotes. Paste it in and hit enter, but there's an error.

[00:11:23]Formula parse error. How do we fix that?

[00:11:25]We double click again. Check it out.

[00:11:27]This app right here is colored black.

[00:11:29]That is because there are double quotes around it when the formula needs to have no double quotes in it. So, what we do is we take those out and replace them with single quotes. Why does this work?

[00:11:39]I don't know, it just does. Single quotes.

[00:11:42]Hit enter. Loading. 178.28.

[00:11:46]Ladies and gentlemen, we got them.

[00:11:48]We have now web scraped in albeit a pretty primitive, you know, small way, but it's a big way for you if this is your first time.

[00:11:58]We have web scraped TCGplayer and gotten a live snapshot of the TCGplayer low price for War of the Spark booster box.

[00:12:06]Congratulations. Now, let's calculate this last column. If I sold today, how much money would I make? Equals. I'm going to do a couple parentheses cuz you might be tempted You might be tempted to say, "Oh, well, the TCGplayer low price and then you subtract the buy price and hit enter and boom, you'd make 18 bucks." But, no, you wouldn't because if you sold anything online, you know better. You know that you pay shipping and fees.

[00:12:29]It stinks.

[00:12:30]If you can sell locally, man, if I could find someone locally that would buy my War of the Spark booster box for 178.28, I wouldn't do it because, you know, I I want to keep that. But, but that would save me a bunch of money because I wouldn't have to pay shipping or fees.

[00:12:46]So, how much would I actually make if I sold it? What that formula looks like is you take the price that you'd sell it for, you multiply that by 0.87 because that will take out fees and fees are a big part of selling on TCGplayer and eBay. About 13%. I know it's different.

[00:13:03]It's like 13.5 or depends on if you're a pro or direct or, you know, whatever, but if you're just a newbie or whatever selling on TCGplayer, uh around 13% in my opinion is like a good rounding number. Anyways.

[00:13:19]And then we got to subtract out shipping. Let's say that we are going to spend eight bucks on shipping. We might need to spend more. There's the cost of the cardboard and bubble wrap and uh the shipping label is the biggest thing. And so really honestly, it's probably closer to 10. Minus 10.

[00:13:33]And then we take out the cost of the buy price of the original price we paid. And there we go, ladies and gentlemen. We would lose $14.90 if we sold today. Good thing I have no intention. So, that is a product.

[00:13:49]Great job, team.

[00:13:51]Let's do one more real quick. Let's do a single because the problem, like I guess I Let me Let me demo this.

[00:13:59]Uh Jewel Lotus.

[00:14:02]I bought mine for I don't know.

[00:14:06]Did I get them for like 30 each? I think it was about that.

[00:14:10]Pretty cool.

[00:14:11]Um I meant why would I own Jewel Lotus?

[00:14:14]It's a banned card that has no place to be played in.

[00:14:17]TCG Player low price. So, I can grab the same formula, paste it in here, and change the URL. So, that's what I'll do.

[00:14:26]I'm going to change the the URL to be to this Jewel Lotus listing on TCG Player.

[00:14:31]Paste that in.

[00:14:35]Hit enter, and boom.

[00:14:39]$48.70.

[00:14:40]That is so cool, except that's not the price of the card.

[00:14:44]This XPath points us to this spot on the page. However, this spot on the page for singles is not the lowest price.

[00:14:53]It is the TCG Player direct spotlighted card.

[00:14:58]The lowest price is over here.

[00:15:00]Uh $42.99 plus $0.99 shipping.

[00:15:04]Or it is over here. Lowest sale price. Maybe that's what we want. High sale price.

[00:15:13]Maybe that's what we want.

[00:15:15]Um the the point is we can grab any of these any of these numbers.

[00:15:20]So, let's try grabbing this one. Actually, haven't grabbed this one before.

[00:15:25]So, we need the X path. We need the treasure map that points to this number.

[00:15:28]So, inspect.

[00:15:30]Whoa, made the screen all weird. All right, right click, inspect again just to make sure we're pointing at the right spot. Yep, this is it, the 4299 that I want. Keep in mind this isn't including the shipping number, so this isn't going to quite be right, but close enough.

[00:15:45]Copy X path.

[00:15:47]And then we go back over here and let's paste that X path in.

[00:15:54]We have to make sure the double quotes for this app over here turn into single quotes. Hit enter, let's see if that works. Boom, baby, it worked.

[00:16:03]And this formula, what's really cool about spreadsheets is you can drag formulas down. I'm just clicking on the corner, bottom right corner, dragging it down, and boom. Nice. My Jewel Lotus that I bought for 30 bucks, if I sold it today, I would lose $2.60.

[00:16:19]Woo. So, pretty cool.

[00:16:23]All right, any questions, let me know.

[00:16:25]Uh comment section below. If uh you like this kind of thing, if you want to learn about web scraping, if you want to learn about I don't know.

[00:16:32]Data science, you should you know, shoot your suggestions in the comment section below. Subscribe. If you haven't subscribed, you really ought to consider it. I understand you don't subscribe to anything. You just let the algorithm give to you the stuff that you want to listen to, but you could subscribe to just me, and don't worry. If you stop watching me, uh YouTube will stop showing you my stuff even if you're subscribed. So, it's really low commitment, but you you can subscribe, and that will help pump my numbers, and I will be grateful.

[00:17:04]So, and I also hope you come back and watch more stuff in the future. Anyways, thanks for watching, all. Uh let me know what you think, and I will see you later.