How to Automatically Track Bitcoin Profit in Google Sheets

what's up youtube it's your boy rhett back at it again with another video i released a video last week about how to buy bitcoin automatically in what is definitively the safest cheapest and fastest way possible i haven't seen anyone else talk about this method on the entire internet and considering that bitcoin is the best performing asset of the last decade that video might be the single most valuable video on the entire internet so go watch that video after this one if you haven't already some of the wonderful people in the comments of that video who have implemented the strategy that we talked about are now wondering how they can automatically track the profits that they've been making by using that strategy so as promised that's what this video will be about so if you're watching to the end of the video you're going to get a spreadsheet that's going to allow you to track all of your bitcoin purchases and you could really use this for any asset it doesn't have to be bitcoin you're going to get step-by-step instructions for how to automatically pull your transaction data from gemiini into that spreadsheet so you'll never even actually have to update the spreadsheet it's just going to update itself and you're going to get hugely valuable experience using aws aws is a hugely in demand skill right now if you know aws you can definitely get a job doing aws this video is not going to be enough to get you there but it's a great intro into a platform that is widely in demand in the technology industry your brain is going to thank you for leveling it up so much your resume is gonna thank you for adding aws to it the like button down below is gonna thank you for smashing its face off and your wallet will probably thank you too for saving so much money by watching these videos so enough talk let's just jump into it okay so the first thing you guys are going to want to do here is to get a google sheet it can be any google sheet but i'm going to use this public definitive bitcoin sheet that i'll have linked up down in the description so once you have that sheet you're going to go over to console.cloud.google.com you either type that in directly or you can just do a search for google cloud console and it should take you to a page like this for you it probably says something like create new project you already have one like me you're going to want to go up to the top and hit create new project so i'll go in here and i'll just name this uh youtube demo sheets and you can create your new project so that project's been created you can go switch that project and this is basically how we're going to create the api connection between our code and google sheets so we're going gonna find this link in the side for api and services just gonna click on that and then the first one we're gonna need is google drive so if you hit enable up at the top you're just gonna do a search for google drive can't spell but it works anyway so here's google drive api you can hit enable here and google makes this all really easy with their ui here this is all very simple to do so to use this api you may need credentials so let's create the credentials in this list here we're going to select google drive api we're going to be calling it from a web server node.js or tomcat and then what data will you be accessing you're going to select application data and then here you're going to select no so then you'll click this find out what kind of credentials you need for your service account name you can just give it your name so i'm going to call this rhett so then for role i'm going to give it project editor and then you're going to want to make sure that json is selected down here so then you hit continue service key was created you're going to want to download this json file so i'm just going to save this on my desktop and i'm going to save it as sheets underscore creds.json you can call it whatever you want but if you're using the code from my notion down in the description you're going to want to change where it says sheets underscore creds to whatever yours is named for simplicity you might want to just name it the same thing so you don't have to change any code so you can hit close here and then we will go back to the library search and we will look for sheets click on google sheets and we'll hit enable and that's it so now we're done setting up the google sheets api that was really easy no programming at all you just have to go through the different wizards and google makes it really easy for you so what we're gonna do next is we're gonna take that json file that we downloaded and we're going to connect it to our google sheet that we want to get the data out of and then host all of that up on aws so that we can automate this whole process okay so here what i've done is i've opened up the sheets creds.json that we just downloaded from that api step you're going to want to take this client email here yours will definitely be different than what mine is you're going to copy this and then we'll go back to the sheet that we had opened in the beginning you're going to want to take your own local copy of this spreadsheet so once you get the link to the spreadsheet in the description you're going to want to copy it into your own local google drive and then once you have your own local copy you're going to come up here to share you're going to hit share and then you're going to fill out the email address here with the email address that you got from that sheets creds json file and then you're gonna hit send and so that is gonna allow this json file to access this google sheet right you shared the client email from this json file with this google sheet that you got down in the description and then made a local copy out so next you'll open your aws account here and you'll come to the aws management console and you'll head into lambda functions first thing you need to do here in lambda functions we've already done this in the other video so if you've already watched that other video you're not going to need to do this but you're going to need to come into layers here and then you're going to open up this notion link that i have down in the description where all the code is and you're going to take this gemini layer zip if you're using gemini or there's one down below for coinbase pro and you're going to upload that layer zip into aws so you're going to hit create layer you're going to name it whatever you want maybe gemiini demo and you can give it whatever description it's google sheets plus gemini and then you're going to upload the zip file and you're going to give it python 3.8 i'm not going to do this because i've already uploaded this layer like 100 times and i don't want to do it anymore but if you're just doing this for the first time you're gonna have to do this so i'm gonna hit cancel here but you would hit create so next you'll go back into lambda and you'll go to functions and we're gonna write an update bitcoin sheet gemini function so let's do a create function update bitcoin sheet demo and we'll give it python 3.8 and then you'll hit create function so now in this lambda function whatever's there you're just going to go ahead and delete that and you're going to come over to the notion page where all the code is and you're going to find update bitcoin g sheet gemini and you're going to copy all of this you're going to copy all that and paste it over in lambda function if you highlight it weird it might give you like bad lines of code here so it should end with this curly brace and it should start with import json and it should only be 51 lines long so the first thing you're going to need to change in this script is your public key and your private key to your gemiini api i'm not going to go over how to do that in this video just because it's sort of outside the scope of this video so if you're confused about how to do that definitely go check out that other video where i show you how to buy bitcoin every day from gemiini using the same strategy and in that video i show you how to generate your own public key and private key from your own gemiini account so i generated some api keys here i'm just going to add those in here these are demo api keys never share your real api keys with people or they will be able to trade on your behalf and take your bitcoin and that is something that you don't want so keep these a secret don't send anyone definitely don't send them to me don't send them to anyone impersonating me i will never ask you for your api keys so just keep these safe don't give them to anyone and then again in line 29 here this sheets creds.json if you named your sheets credentials json file that we generated from that google sheets api in the last segment of the video if you named it something else you're gonna have to change that here and then this the public definitive bitcoin sheet i maybe could have named it something a little more simple for myself there if you change the name of that that's the sheet that i'm providing you in the description that we started this whole video off with here if you change the name of this spreadsheet or if you change the name of this tab down here btc by audit file if you change either of those names you're going to have to come back into the script and change the names here so you'll see btc by audit files the worksheet and public definitive bitcoin sheet is the spreadsheet and so basically what this is going to do is it's going to take all your transaction data from your gemiini account and it's going to populate it into this btc by audit file spreadsheet and then that data will roll up into your general ledger you'll get some charts and you'll get some stats based on all of this data that's in this spreadsheet here so be aware if you change any of that stuff right if you change the name of the spreadsheet if you change the name of this worksheet if you change the name of this credentials file this exact code will not work for you you'll have to either change the names back or edit this code here all right so next in this folder over here you might be able to see it it says on the left in environment it says update bitcoin whatever you named it sheet demo and then it has your lambda function under it you're going to want to right click on the folder and click new file and you're going to call that sheets underscore creds.json and then you're going to copy and paste your existing sheets creds json into that file so i'm going to take all of this here and paste it in here and so it should look something like that obviously your details will be different than mine and then last thing here we need to add the layer so you can come down to add a layer and then select custom layers and choose your layer i'm going to choose my gemini demo layer and again this is just that file that you got from up here this gemini layer zip or i think there's a coinbase layer zip down at the bottom if you're using coinbase so hit add here once you've added the layer and then final final thing you'll come into configuration here and i have had some issues in the past with this timeout being three seconds so i always change it to 30 seconds not a big deal so then you'll come back to code you'll hit deploy and now i think you should be good to go i'm gonna hit test and see if this works so i'm gonna just call this uh demo you can leave this alone and then hit create so i'm going to hit test and i'll come over here to didn't work because i misspelled the name sheets with an s creds.json i called it sheetcred.json so let's try that again need to deploy after you change the name of the file this is demos nothing works on the first time all right so hit test and now if you go over to your bitcoin sheet you'll see that your transaction data is getting filtered in from gemini into this sheet right here you'll see that i have used this quite a bit so here quota exceeded for quotametric if you're trying to fill a ton of backdated information like i just did there you're going to have to run this a couple times because it's only going to allow you so many requests to the google sheets api so you'll see here we hit the limit for write requests per minute per user if i waited like five minutes and did this again you know i could eventually get all of those transactions in there if you're just doing this for the first time you're probably not gonna have this problem your transaction details should just come over automatically and so then you can just take these and drag them down and those will give you some good statistics there and so then you can see here the you know the stats and the charts and the general ledger and stuff like that it's important though that you have this very first line in there when you do run the script and that's because you need something where the transaction id is zero or one so that when your transaction data is getting pulled from gemini it's only going to pull transaction data that is greater than this number all of your transaction ids should be greater than this number because gemini is in like the hundreds of millions of transactions or something by now so you shouldn't have a problem you should be able to delete this line when you're done with it so once your data has populated you can go ahead and delete this but just make sure that it's there when you do test the script for the first time because if it's not it's probably not going to work for you so now you have a script that will add every bitcoin transaction from gemini into that google sheet next we're going to automate this script so that it does it every single day for you and you don't have to come in here and click test every time so back in aws we're going to go to this home screen back to the console and we're going to go to cloudwatch which is under management and governance if you're looking for it for the first time so in cloudwatch we're going to go to rules and then we'll hit create new rule and we'll hit schedule up here and you can choose to run this however often you want i really just run it like once a day and then you can get fancy here with the cron expressions like if you wanted to run it every day at 12 o'clock noon or every day at like 5 p.m or something this can be helpful because you want to run this script every day after you run your buy script right i run my sheet updating script a few hours after my buy script and usually it's able to pick up that transaction the same day sometimes the limit order doesn't fill right away like we talked about in the last video but for the most part it fills pretty quickly and then this updater runs like an hour or two later and then i get the transaction the same day in the sheet if you miss a day it's not really a big deal because it will pull in any transactions that are greater than your most recent transaction data id and that are still stored in gemini so if in your sheet your most recent transaction was like 300 million and one and you didn't update for two days for some reason and you have three million and two and three million and three both of those will come in in the same running of that sheet like you saw when i ran mine it pulled in like you know 50 lines of data so if you miss a day really not a big deal so i'm just gonna leave this at fixed rate of one days if you want to get fancier there's some chronic expressions in the last video that i'll leave in the description of this video too but when you're done there you're just going to come over hit add a target and you'll pick the update bitcoin sheet demo whatever you called yours from that last part you hit configure details you'll call this uh update bitcoin sheet demo youtube homies get some so are a couple fixable limitations to the script i think the biggest one right now is that i imagine if you sell bitcoin that this is not going to pick that up correctly in the way that you would want it to i think it will pick up the transaction but i don't think that it will do it removing bitcoin from your stack in the spreadsheet i never bothered programming this because i don't plan on selling any of my bitcoin but if this is something that a lot of you want maybe i can look into it and figure out how to get that to work correctly should just be some tinkering in the spreadsheet and a little bit with the script so let me know down in the comments if that's something you're interested in the other limitation to this is that if you're not using gemiini or coinbase pro i haven't written code that's going to let you do this for any other exchange and then some places that you might get bitcoin from like block fi or like lolly or like fold all places that i use but that don't have publicly available apis for you to pull transaction data from so whenever i get bitcoin from those places i have to update the sheet manually which is a little annoying but it is good to have one spreadsheet that's sort of taken care of everything if you haven't seen it yet go watch the video from last week where i show you how to buy bitcoin every day or on whatever schedule you want from gemini it has the best fee structures it's really safe gemini is a super reputable regulated company and i think that this is the fastest way you're going to be able to implement something like this i haven't seen really anyone else talk about this in the internet so again i'm biased but i think that that video is disproportionately valuable so go ahead and check that out if you haven't watched it already i'll put it up in the cards it's gonna save you a ton of money on bitcoin fees and the best part is it's free because i love you and i care about your financial health also if you're looking for more general guidance on the google sheets api we didn't really go over generally how to use the google sheets api but if you want to apply this to a different problem there's a great video resource here on youtube made by a guy named tech with tim that i learned to use everything that i needed to know to make my solution work and so i'll leave that tech with tim video down in the description as well if you're interested in checking that out and applying this google sheets api to a different problem that you might have go ahead and like this video if you learned something and comment down below if you got stuck or if i confused you at any point and i'll try to help you get unstuck or fix your problem i do still respond to all the comments and subscribe for more tech money and success videos every monday at 10 a.m eastern we hit 420 blazid subscribers this week and so previously if you were watching this channel we've all been traveling in two buses that were duct taped together and it's been getting really really cramped we now have way too many people to be doing that anymore people have been complaining about leg room it's been a huge problem so we've destroyed the buses and we've upgraded to an airbus a380 which has about 900 seats i'm told hopefully that's a little bit more roomy for everyone legroom again was becoming a problem we are however like almost halfway full on the airbus a380 so i don't know how long that this leg room is gonna last especially with how fast the seats are filling up so if you were on the fence about subscribing you hadn't done it before but you were just waiting for the perfect time to do that we have a ton of legroom right now so that's gonna be really great for new people seats again though are filling up really really fast so you know you're gonna want to book your ticket as soon as possible that's it for today i love you all goodbye [Music]

Channel: Rhett Reisman - Level Up Your Brain

Views: 2,211

Keywords: how to track bitcoin profit, how to track bitcoin profits, how to track bitcoin buy, google sheets, crypto portfolio tracker, crypto portfolio tracker google sheets, bitcoin tracker, altcoin tracking, bitcoin, blockfi, google sheets budget, bitcoin spreadsheet, cryptocurrency spreadsheet, cryptocurrency tracking, ethereum, cryptocurrency profit, bitcoin profit, blockfi referral code, cryptocurrency portfolio 2021, how to track crypto, track crypto, google sheets bitcoin tracker

Id: V51zRxLAVWU

Length: 15min 36sec (936 seconds)

Published: Mon Apr 12 2021