How to Build a Crypto Portfolio Tracker on Google Sheet

hey what's up guys welcome back to another video this is toriel sultan in this video i'd like to show you step by step how i build a google sheet-based crypto portfolio tracker from scratch not only we're going to display the major data that's really important to us we're also going to capture every one of your transactions it's gonna be a fun one packed with a lot of cool tips and tricks i've also made a decision to make this entire template available for you guys to download and deploy the link is in the description do me a favor if you enjoy this video and find some value in here drop some comments below give it a like and subscribe to the channel for future content as i'll be posting more and more this type of tech related tutorials for you guys so without further ado let's get into it [Music] so there are many benefits of tracking your cryptocurrency in terms of transactions and your portfolio it gives you more clarity on what you're holding and how the performance is going on your portfolio and most importantly we might have some upcoming tax regulation and changes so it's always good to track your crypto transactions and the purchases all right guys before we actually build this crypto portfolio tracker from scratch i just wanted to show you a little bit of the feature side of things i want to show you how this entire sheet works basically i have listed about 20 different cryptocurrency here you don't have to do this many you can just do as you go uh depends on how many crypto you're invested in and you are planning to invest in and you can build as many as you want everything here is pulled from live data i can check on this box on and off i can do a refresh on all data so this will automatically pull the most accurate market data takes about 20 to 30 seconds so the current pricing of all these cryptocurrencies are pulled live as well as market cap we have a 24 hour change percentage we have a seven day change percentage and six months if you sit as curved chart color is synced with a seven day change that means if we have a positive seven day change percentage this curve will turn green and if we have a red one which means the negative then it will turn red and we have a seven day volume and most importantly from column k to p is our own portfolio tracker and this is not my actual holdings this is just for demonstration purpose and then we'll have a current value of these tokens based on how many you have and we have acquisition cost which is how much you spend on acquiring these cryptos we have a total roi in terms of dollar amount and we have a total hour wide of percentage and we have a 24 hour holding change in terms of dollar amount and now on the second tab here we have something called a dashboard this is going to display to us in a pie chart of the exact ratio or percentage of each one of your holdings so this is basically reading the crypto data on this column current value and current holding we have portfolio value of 36 900 we have a four different tokens that we're holding and we have an acquisition cost this is total we paid for the total portfolio value minus acquisition cost that's going to show us the total roi so far in terms of dollar amount and percentage and last but not least you can see we have all these different cryptocurrencies 24 hour transaction if i click on btc we had about 180 000 transactions in the past 24 hours on btc alone here just to show you the difference ethereum had a 1.1 million transaction in the last 24 hours last but not least we have the third tab this is actually connected to a google form this will capture exact timestamp of when you purchase certain crypto and a ticker how many tokens you purchase in that single transaction personally the most important data you need to capture is the price you paid for at that specific moment right this is really handy in the future if you want to pay tax or if you get any audited and stuff so this will actually save your life in terms of knowing exactly when you purchased what token and we have a note section mainly this could be where you stored it or where you bought it any kind of keyword that makes sense to you total paid is automatically calculated just give you an example if i copy roll five paste it to roll six you'll automatically calculate the total paid amount so you don't have to type this yourself this is linked to a google form there's a lot of benefits of having a google form link it with a sheet and i'm going to show you guys how can deploy this on your mobile app and from there you can capture any sort of transaction as you go right so that was enough just to show you a little bit review of this entire sheet and now we're gonna do is i'm going to show you exactly how i build it from scratch so make sure you stick around so now the first thing we want to do is load up your google sheet if you don't have a google account make sure you have one google sheet is completely free same as google form let's rename this sheet crypto portfolio tracker in the first row we're going to reserve for something called a data refresh right so a1 is going to be refresh data b1 is going to be data validation and here we want to toggle to checkbox and once you're done with that click on save it'll automatically turn it to a checkbox for you so this is easier for us to refresh the data pull the most recent market data so the second row here we have a name ticker exchange taker current pricing usd market cap 24 hour seven day six month and so forth so you can just stick to exactly what i have here and in terms of crypto list and you can do as many as you want and we have a list of 20 in here in this case just for this demonstration and i want to just resize them a little bit like that there's many different ways you can pull live data you can use some equations such as import xml or import html to read certain data from certain websites like coin market cap however the trick that i found is actually working amazingly well it's something called a cryptofinance.ai this link is in the description you can go ahead and find it basically it's little plugin we're going to install to connect and integrate with a google sheet so we can use this equation or formula to pull the live market data so installation is pretty straightforward i'm going to show you guys step by step basically you come to this site click on get started and it will lead you to this page you can actually read through a lot i learned a lot just by reading these documents so if you scroll a little bit basically what we want to do is we want to use a script editor so let's come to script editor you want to click on tools script editor it will automatically load up a new window called app script by google so in here you can rename this project let's say crypto tracker then now if you scroll down a little bit here is a content you need to copy click on this blue font here this is the code we're going to copy okay command a i'm going to copy entire script here come to my script editor i want to replace everything here and click command v to paste the entire script on my app script and now just click on save after saving it you want to click on run it will tell you that authorization is required simply this is first time you using this script on your google sheets so you want to review permission pick your gmail and you want to click on allow just in case sometimes if you see it doesn't allowed or warning sign click on advanced and you want a proceed and then you should be able to see this allow window basically just click on allow and that's all you need to do this is just giving the permission for the script to run on our google sheet so you can see here we execute it and we complete it and now if i come to my google sheet you're going to see i have this menu option here called a crypto finance and now i can basically click on equal to crypto finance i can start to use this formula to pull the live market data however i do want to mention that if you don't have an account with them you're kind of getting restricted with how much live data you can pull so personally what i recommend you guys to do is actually to have an account with a crypto finance their website is right here the link is in the description come to this site top right corner you want to sign up create an account basically enter your email and your username and give it a password and you can create an account you do need to verify your email so basically log into your gmail click on the verify email so you should be able to go right away so now you have an account with the crypto watch you come to your top right corner this account and you want to come to api access in here we want to generate a new key so just give it a name call it crypto tracker and click on generate key it will display to you the public key and the secret key what you want to do is you want to copy paste your public key come back to your sheet obviously you can click on this menu option and enter crypto watch public api key and just to paste public api here click on ok and it will show you you have successfully saved and unlocked it so now we can freely pull a lot of market data instead of getting restricted with how much you can pull right so let's now finish up this sheet quickly so in terms of ticker we have all the ticker here you can go to websites such as coin market cap to check the tickers make sure it's correct because this one has to be correct in order to pull the live data exchange ticker i have something called slash usd this will be really handy just in a moment you'll see why i do exchange ticker because a lot of things we can just drag and drop instead of manually type it just make sure your exchange ticker is exactly what i have here and now in terms of current price how we are able to pull the current btc price the bitcoin price is click on equal to crypto finance you can do two quotation mark inside of the quotation mark we can do btc slash usd do close bracket and if i click enter that will show me the dollar amount of what is the current price of btc bitcoin right column d is going to be dollar format so click on that so that's showing me it's 34 500 just to come to coin market cap you can see it's 34 500 it's pretty accurate and now a quick shortcut is i don't want to manually type btc usd ethereum usd a quick trick you can do is instead of typing quotation mark btc usd we just simply refer to the cell value of c3 which is exactly what we entered earlier click on enter that's going to show me exact amount but the benefit of it is right now i can drag and drop all the way across and it will load up all these exact token price because they're reading the sale value of the previous column which is exchange ticker so you don't have to manually type it so hopefully this is a quick shortcut for you i recommend you guys to come to their crypto watch docs you can actually read a lot here to learn what kind of data you want to pull in terms of pulling the market cap data is equal to crypto finance and the token we're gonna refer to is btc usd comma and the attribute is you want to do two quotation mark inside of the quotation mark you want to do market cap and now you want to close brackets this will automatically populate the current market cap of btc again this entire column is going to be dollar format so we have a 646 billion for btc because i refer to c3 so now if i can drag and drop all the way across and this will automatically pull all the live market data of these specific tokens right so now one more thing i want to introduce earlier than later now is because right now you see this refresh rate data here if i toggle this box nothing happens that's because we didn't tell this equation or formula to listen to b one when it's on and off they refresh here so the way we do it is very simple you can see here right it's asking for market attribute option and refresh cell so we want to do is c3 do a comma we don't have attributes so let's just do two quotation mark comma we don't have option as well two quotation comma and now we want to refer to b1 however one key element here is you want to click on f4 on your keyboard you want to locked into the cell so the way you logged in is having this dollar sign in front of it that's because you will see just in a moment if i drag and drop again here and the b1 is locked it won't switch like c5 does right c5s this one says c6 c7 so we'll lock into this exact cell and now look at what happens if i do on and off on this toggle it will automatically reload all the data here same as market cap and right now because we have an attribute that's why the next one is option we don't need options so two quotation mark comma and now again we want to lock into b1 and click on f4 on your keyboard just to lock it and now if i click on enter and if i drag and drop all the way down so this way all the b1 is loaded on these equations right and now again if i do on and off this will automatically refresh and reset all the current price and market cap how you pull the 24 hour percentage data is to do crypto finance again we're referring to c3 again is btc usd if you want to type it you want to do quotation mark btc slash usd so that will pull the same data again we're not going to do manually so this way we're just referring to the value of c3 which is btc slash usd right so again the attribute is going to be quotation mark change and the option is going to be 24 hour because we're pulling the 24 hour data and then refresh cell will lock into b1 with the dollar sign click on enter and then come all the way across to the bottom and now this will show you 0.06 right select the entire column you want to click on format as a percentage so this will show you the exact percentage fluctuation in the 24 hour window now in terms of seven day straight forward c3 again attribute is quotation mark change and then the option is one w stands for one week locked into the cell to refresh data click on entered and let's just drag and drop all the way down and now g entire column is going to be percentage formatted just like this and now let's quickly do the six month change it's going to be crypto finance c3 change six month and refresh cell b1 locked in click on enter it's pretty straightforward once you know how to do one the remaining ones are pretty straightforward and now again the entire age column is going to be percentage format and that is percentage you can see here bnb token had 670 percent in the past six months so now the next one is going to be curve inside of a cell i'm gonna do a simple version for you guys so you know what's going on here right now what i basically did is it's listening to column g if g3 is below zero it's going to show me a red line if it's above or equal to zero it's going to show me a green line because that makes sense right if it's a positive seven day percentage show me a green otherwise show me a red so just wanted to show you the simpler version to do it if you don't want to do the color coding it's basically equal to sparkline and then you want to open bracket and crypto finance and c3 is the cell we're referring to in here let's do c4 because this line is for ethereum c4 and you want to do a comma quotation mark sparkline is what you want to enter and if i click on enter now that's going to show me this line you can see here it's going to show you a generic black line right now it's not actually doing any color difference again if you want to do the color difference what i recommend you to actually copy paste what i have here i'm using ifs formula if g3 is below zero and then that show me this entire line you can see the color coding code is right here this is showing me a red color code and then comma if it's a greater than or equal to zero then show me a green line here this is a green line color code right just do exactly what i have here click on entered that should be able to change the color and you just cross all the way down and that's going to load up the entire seven day price curve so you can easily visually to see what's going on with the price and then in terms of seven day volume what we do again is spark line and crypto finance c3 instead of spark line we have underscore volume because we're going to show a chart type is column so if i click on entered that's going to show me these little bars too visually to see what is the past seven day volume like right you can see here cardano has had a really big volume spike as well as matic here so visually you can read a lot of data just by doing so now from the current holding till the 24 holding change this will be pulled from a different tab i'll show you guys just in a bit and let's clean up this a little bit from q to z we don't need them so let's remove this entire columns and now i want to rename this tab let's do rename crypto data so that's it for that and now i want to show you how i build a transaction capture google form you will see the benefit of having a google form it's a really simple way to capture your transactions right by the way i use a similar strategy on my personal and business finance tracking it's ridiculously simple and really accurate i've been using it for two years i'll leave the link in the corner somewhere or in the description it's a video and you can go watch it and a template is completely free a lot of people benefited from it so i want to share that with you it's completely free hope you pull some value from there and now in terms of google form you don't have to do google form you will see why i do google form but if you don't do google form basically just do exactly what i have here do stamps ticker amount purchased token price note and paid paid here i have a little formula here i'll show you guys in a bit this will automatically populate for example if i bought one btc at 30 000 and it will automatically calculate this total paid amount if i bought two bitcoin it will show me 60. so this is automatically calculated so pretty handy that way now quickly come to google form and we want to rename this form crypto purchase tracker or crypto transaction tracker whatever name is comfortable with you first question is going to be ticker and then instead of short answer we're gonna go with the drop down in here depends on how many cryptocurrency you're buying frequently and you can do as many as you want in my case i did a list of 12 cryptocurrencies we want to toggle to required because this field is required and that's it let's click on the plus sign add one more question this is going to be amount purchased again this is not a dollar amount this is the token amount that you purchase for example one btc two ethereum and so forth this is going to be short answered again it's required and if you click on three dots we want to do something called response validation we don't want any sort of input we want to narrow down to number that's greater than zero right makes sense you want to have more than one if you're putting zero or below zero it doesn't make sense it has to be a number that's above zero just in case the arrow message is going to be please enter an amount that's greater than zero that's it for this one click on plus let's add in one more question and in here it's going to be token price at purchase from a tax stands point if you want to transparently track everything it's really handy to have how much you paid for that token at exact what price if you want to be more transparent down the road in terms of tax stance point again we have a regulations coming up it's really important to track what price did you pay when you had that transaction right a lot of people would actually miss out on this key element here this is going to be short answer and it's going to be required and we're going to do a quick validation here the number has to be greater than zero otherwise we just copy paste this error message and paste it here it's going to be please enter an amount greater than zero and that's it and last but not least this is optional you can do something called a note this is just a quick note that makes sense to you a key phrase or keyword that you're typing that's going to remind you where you bought it from this could be coinbase could be binance right or where you're storing this could be your ledger wallet or metamask just do a quick note that makes sense to you we don't have to do required on this specific one it is going to be either short answer or paragraphs you can do either or and now that's it and you can see it took us less than a few minutes to build out this google form you can change the color to any other color that you prefer i'm gonna do blue and now you can actually add a header image if you want to when you're done with this you can do a quick preview and this is how it looks like right we have the ticker and this is the ticker we enter we have the amount purchased token price and note and let's close it out the key element you want to do here is we want to link the sheet with the form the way you do it is click on responses you're going to see this icon this is google sheet icon click on it you don't want to link it to a new sheet that's going to create itself you want to link it to an existing sheet and if i click on select all you need to do is the locate the crypto portfolio tracker click on select and then it will automatically open up this crypto tracker we created not only that it will automatically create a new tab below so we can rename this freely let's do this transaction capture and from column g to k we don't really need them so let's remove that but we do want to utilize column f and what i have here is something called a total paid you actually want to have a one row minimum of transactions in order to make this trick work so let's quickly come to crypto purchase tracker click on preview and let's quickly click on btc let's say we bought 0.3 btc and token price today's price btc right now is at 34 618 so let's quickly type here thirty four six one eight and now note let's say binance or coinbase click on submit come back to a google sheet you're going to see this role appeared itself right now we want to tell this google sheet moving on forward automatically calculate total paid total paid is very simple it's simply amount purchased times token price that is how much you paid for right the formula i used here is called every formula again i'm not going to explain exactly how it works just simply copy what i have here if it's blank right if there's no transactions leave it empty you can see i have a two quotation mark no value in between that's going to show me empty however if there is any value on this row what you want to do is c column times d column and that's going to display if i click on enter that's going to display amount purchase times the token price is going to be how much you paid for now just to test it out again if i come to submit another response let's do ethereum where let's say we bought one ethereum the price is at 1976 so let's type in 1976 and note let's say coinbase click on submit come back to your sheet you're going to see it will automatically calculate how much you paid for for one ethereum at this exact time now let me show you the true benefit of having google form instead of just a dry sheet is because when you're done with the google form you can click on send and you can click on the second option here you can shorten this link this will populate a link this is dedicated for you this is a google form link you can email it to yourself so you can open this form on your mobile device just like what i did here if i click on the bottom option i can save this to my home screen and i can rename this let's say crypto tracker click on add and it will show up on your desktop as this little app icon says crypto tracker right every time when i'm outside somewhere if i want to capture a specific transaction i can simply come to my phone and open up this google form and now i can actually do everything on my mobile device you don't have to log into your laptop or your desktop you can actually capture these transactions on your mobile device right again whatever you did input here is exactly just like how you did here it works exactly the same and the data will be populated to this google sheet here so really convenient way to actually build it with a google form instead of just a dry sheet here so that's it and now let's come back to crypto data and let's quickly finish up these couple columns here so in terms of the current holding is equal to i want to sum if this is going to be the formula that we use to calculate what token we have on what amount so click on that and then the range of what we're gonna analyze is going to be click on transaction capture the range we're looking at is ticker range here btc and if i do a comma come to crypto data and we want to calculate the ticker so it's going to be b3 is a btc right click on comma again and some range is going to be transaction capture and amount purchased because we're tracking the token amount close bracket click on enter because we bought the 0.3 btc and that's going to sum sum is total right it will automatically do a sum on the total token amount to show you an example if i duplicate this row twice i have a 0.9 btc right if i come to my crypto data it's showing me 0.9 so it's working beautifully and now if i drag it all the way down and this is going to calculate other token as well right you can see i have one ethereum right now if i let's duplicate the second world ethereum rule twice so we have a three ethereum now if i come to crypto data i have a three ethereum so it's working really good so the current value is actually the token amount right times and the current price that's going to be my current holding value so this is the current value of my holding position depends on how many token that i'm holding three times 1976 is five nine three zero one thing if you if doesn't bother you you can leave it as is but there's a way we can hide all this zero so the quick way we do it is to do an if formula so it's actually reading the k three if this current holding is a zero then don't show me anything so two quotation mark there's no value in between so it will show me empty other than that if it doesn't show zero if it shows the balance then go ahead and calculate k3 times d3 which is current price times current holdings so if i do a enter it will still show me the exact number but right now if i drag and drop look at what happens so all the zeros are gone just to hide all the zero now the acquisition cost is pretty simple click on equal to sum ifs the range to look at is transaction capture the ticker column here right click on comma and now we want to come back to crypto data let's select b3 this is the ticker we're looking at click on comma one more time the sum range right now as you can see it's acquisition cost right the sum range is going to be total paid because this is how much it cost us to acquire that specific token at that price right if i close bracket click on entered we paid 31 000 for 0.9 btc that's basically how it works again if i do cross all the way down the zero will still appear by the way this column is going to be dollar formula i want to be able to hide all this zero so the way we do it i just showed you guys is to actually do a if k3 equal to zero then show me nothing other than that do a sum if so if you don't know how to do it just do exactly what i have here and do a enter it will show you the same number however when you drag out now it will hide all the zero so pretty straightforward in terms of roi wise is the current value minus acquisition cost is going to be our dollar roi click on entered again i still have all these zeros so the way we hide it is to insert an if equation here k3 equals to zero show me nothing other than that go ahead and do l3 minus m3 which is our white in dollar amount and do a drag and drop here down so zeros are all gone right so just want to show you how the refresh data works right now c says 0.73 if i do a quick refresh turn it on and off on the refresh data b1 and now just give it a few seconds to load all these data once so once that one is loaded you can see here we have a negative 23 dollars that means when we acquire the btc versus now the btc price actually dropped a little bit that had a negative 23. let's quickly finish off the last two columns in terms of the overriding percentage how it works is l3 which is current value a minus acquisition cost and divided by acquisition cost that's going to show me this 0.0007 this entire column is going to be formatted as a percentage so we had as negative 0.00 impact in terms of roi the way we hide all the zero is to do if k3 equals to zero show nothing otherwise go ahead and proceed with the formula and if i click on entered that's still going to show me this and if i drag and drop all the zeros are gone only going to show me the roi that i have token on right and now last but not least 24 hour holding change is actually going to be l3 times f3 so l3 is current value times the last 24 hour change that's going to populate to us a dollar amount that it had a fluctuation on again now to hide all the zeros what we do is to insert if k3 equals to zero to a quotation mark otherwise do go ahead and do l3 times f3 click on enter and drag all the way down all the zeros are gone right so now let me remove the roll 23 all the way down to 1000 because we don't need these rolls let's delete these rolls only leave the ones we have here again your tracker can be way shorter than what i have here this is just for demonstration purpose if you just have a five six different tokens then you can build as you go again you the way you build it is literally drag insert a new one and drag and drop right let's do a quick xrp here 0.65 is what we have on xrp let's say today we purchased xrp we bought 1 000 xrp at 0.65 and now where we bought it is on binance and click on sub bit and now come back to my sheet you're going to see this roll appeared right we have 1000 xrp current value is at 648. it cost us 650 because 6 0.65 is what i entered and now the roi is negative one buck you can see here xrp rolled beautifully showed up here let's do a few ones right just for demonstration purpose let's submit another request let's say we bought some ada cardano here 1.33 1.33 and let's say we bought 999 and the note is coinbase and click on submit and come to my sheet i have this transaction if i come to crypto data i have this crypto data role shows me 999 of cardano and again current value acquisition cost and you can always do a quick refresh on and off on this checkbox and it will load up to you the most accurate data so now this will refresh the entire data and you can see here the price will reflect accordingly right that's pretty much it and what you want to do from there is obviously you can do some decoration you can decorate the background a little bit make it dark this is totally personal choice and you can see from f to h here if i do a right click you can actually set up a condition format so you can tell it that if these three columns right if it's less than zero then show me red font right if it's greater than zero then show me a green so this way i can easily visually to see what is the price going on in the last 24 seven and six months in terms of the color coding here the same rules apply here i have a column n to p if it shows me if it's greater than or equal to zero then show me green otherwise if it's below zero then show me red that means that specific token had a negative performance and now what i have in terms of dashboard again i'm not going to go into depth about this dashboard because this dashboard is just a tiny little touch you can actually grab it from the template as you want i do want to show you the formula behind these cells so in terms of total portfolio value is doing a sum of the l column on the crypto data if you come to crypto data the l is actually current value of individual tokens right if i total combine them that's going to be my total portfolio value and token type in terms of how many different types of token that you hold this is going to be account unique count unique transaction capture b transaction capture b is actually looking at how many different types of tickers that you have right in our case we have one two three four type of tokens however i do add a little bit minus here because i do want to minus the header we do have this header if you don't do minus one that's going to include this ticker because we don't want to include ticker that's a header so just do a quick minus one that's going to show you how many different types of token that you're holding acquisition cost is how much you spend on it is transaction capture and a sum of the f column right here this is gonna be how much did you spend on all these tokens in total and this is fancier trick if you want to do it or not so at the bottom i want to show you here the the actual formula you're able to pull the 24 hour transaction count is going to be equal to crypto finance two columns inside of column is going to be coin matrix and btc and this is the exact ticker you want to enter and do a comma if you do tx underscore account this is going to go ahead and pull the live market data on how many btc transactions we have in the 24 hour window that's going to show me how many btc transactions that we had right same rule applied if i changed btc to eth that's going to show me the transaction count in the last 24 hours in the entire market so that's about 1.15 million transactions so that's pretty straightforward that's how you're able to do this data our y percentage is going to be the portfolio value minus acquisition and divided by acquisition is the percentage of roi and then in terms of dollar amount is easiest it's going to be current value of the portfolio minus acquisition cost and that's it in terms of this pie chart you can see here i have a added chart so the chart data it's reading here is reading two columns here it's a number one crypto data it's reading the entire b columns to look at the different type of tickers and it's reading the column l l is our current value so basically with these two different data range we're able to populate this pie chart you can make it as pretty as you want with this pie chart is going to populate to us what is the percentage that we're holding on that specific token and you can hover on it to see the exact amount you're holding same as here litecoin will have a 3.6 percent 2 percent and so forth so this is how you're able to do the dashboard again the dashboard is just some fancier touch you don't have to have a dashboard but in my case i just wanted to make some more value for you guys maybe you can pull some trick and tips here and there [Music] that's pretty much it guys hope you enjoyed this video as much as i enjoyed delivering to you guys if you pulled some value in here from this video drop some comments below and give it a like and subscribe to the channel so the youtube algorithm can push it out to more and more people to deploy this type of trackers on their google sheet if you have any questions drop them below i will do my very best to answer them this is toriel sultan thank you for watching this video i'll see you guys in the next one cheers [Music] you

Channel: Tario Sultan

Views: 10,747

Keywords: tario sultan, bitcoin, investing, financial tracker, crypto portfolio tracker, portfolio management, tracking cryptocurrency, cryptocurrency portfolio tracker, crypto tracker, cryptocurrency tracker, google sheet, google sheets, google forms, google form, track crypto, google sheet crypto, track crypto on google sheets, track crypto with google sheet, coinbase, binance, uniswap, coinmarketcap, altcoin daily, manage portfolio, tracking portfolio

Id: YaP2jYnuL7g

Length: 40min 32sec (2432 seconds)

Published: Wed Jun 30 2021