For those having issues with the macro not updating the prices. Delete the column with the crypto names and ctrl-Z to bring the column back and the prices will update.
This seems to work better than the macro that I recorded! Excellent suggestion! Thank you very much for sharing Bruce! I'll pin the comment so that everyone can find it.
Had to struggle for a bit with the format when trying to pull the data via the ImportXML function. It was due to a delimiter setting. I needed to use semi colon instead of a comma ;). Thanks for sharing the video!
You are a life saver. You really helped me convert my Excel dashboard to Google Sheets. This now has set me free from my laptop as I can access my dashboard from anywhere plus I can give access to my associates wherever they might be. God bless you.
My deepest appreciation for this Tutorial. You are amazing and so helpful. Helped me a lot to track all my cryptos in one spreadsheet.and from any page of coin base market web site. Thank you.
In case you want to copy and paste the XPATH's, here they are! PRICE_XPATH //*[@id="__next"]/div/div[1]/div[2]/div/div[1]/div[2]/div/div[2]/div[1]/div CHANGE_XPATH //*[@id="__next"]/div/div[1]/div[2]/div/div[1]/div[2]/div[2]/div[1]/span/text()[1] CHANGE_AMOUNT_XPATH //*[@id="__next"]/div/div[1]/div[2]/div/div[3]/div/div[1]/div[2]/div[2]/div/div[1]/table/tbody/tr[2]/td/span
@@marty_mk you are wellcome, i must you create high quality content. those steps helped me get through most of them apart from few like - Polkadot, Luna, aave, ckb, iotex, slp. Is there a way to get prices for these. same formula is showing error ?
This was great thanks. It worked initially, but now the cells that fetch the data just say "error loading Data" Anyone else had this problem and know the solution?
Hi Marty, great video. Just one big problem, it is not working for me the way to convert the IMPORTXML into a number. I just cant get the reason why. I followed exactly what you did, and it wont work :(
Hey Manzar! Try copying the XPATH again specifically for those cryptos that are not working. Some people have solved it that way! Let me know if this solves the issues for you!
@@paustinachou915 try copying the XPATH again specifically for those coins. Some coins seem to have a different website structure! Let me know if that works!
i have issue with the math function with the price value, there is an errror cause the price value is a text and can't be used as number in the multiply function
Michael! Thanks for the feedback! Regarding your question, take a look at the GOOGLEFINANCE function. Here's an example: GOOGLEFINANCE("CURRENCY:USDGBP")
not sure why Im having issues with the value, I know how to convert that text into numbers format , but somehow I'm doing a normal operation and the result it's wrong.. otherside, this is the best video ever ,
Thanks for the feeback, Sebas! Let me know if I can help you in solving the problem. Maybe, the solution to your issue is already in the comments. Have a great week!
@@leonneumann4152 I don't know the specific cause that is causing your issue, but could you take a look at the comments and see if one of the suggested solutions is useful to you? I recommended adding a 0 at the end in order to transform the cell into a number, but some viewers solved it in other ways! Let me know if you managed to solve it!
Subscribed: You're my hero.... Is there anyway i can capture and record data everyday? So i can place it on a chart 365 days or more. For example, i want to capture and record the 24hr volume, at 4pm everyday so that i can see the historical daily volume in a 30day or so, timeframe. I want to track daily reflections and supply burn rate so i can visually see fluctuations and patterns if i put it on any charts. Ive been looking for someone who made a video about doing this with no luck. Thank you I appreciate your work.
Thank you very much for those kind words! If you don't mind switching to excel, you might find this other video useful for your problem at hand: czcams.com/video/oI3_P5UKZTM/video.html
I have a question. How do you take into account the profit that you took ? So I bought 20 times some BTC, I have 0.09 BC and I sell 2 times, so let's say -0.02 BTC. What is the best way to show that in the table ? Thanks
Hello, it's a great video. Thank you, but I have a problem with substitute formula - it removes the $ symbol, but I still can not to make any operations with that imported data, google sheets says that is still text. How to fix it?
I'm glad you are finding the video useful! Most often than not, in order to force it to be a number, it is enough with adding "+0" at the end of the formula. Let me know if that solved the issue!
@@goefemstories4737 I reuse the same approach, and it works, by replacing point with comma: =SUBSTITUTE(SUBSTITUTE(IMPORTXML(base_url&D7; price_xpath);"$";"");".";",")
Thank you for the video. Everytime its showing "loading" when triger active. its annoying,, is there any way to stop "loading"?? only updated value will appear without showing "loading"??
He Lisa, I already answered to Tiberius, but I'm not sure if you'll get notified, so I'll also answer to your comment. You can use the "googlefinance" function. Here's an example: =GOOGLEFINANCE("Currency:USDGBP").
Hey man! I've seen 5 tutorials of these... tried out 5 different formulas.. this one is the first that works for me. Thanks a lot! Is there a way to track a historic price? or maybe a way of defining a price with a macro? I create new columns every 2 weeks to keep a track in time, duplicate the columns of price token and total of each, to check in new movements in price, buyings and sellings... but once I create this new column (which I want the price to be updated), I wish the previous price column does not update price anymore so I know what price it was 15 days ago. Makes sense? Is there a way? Thanks!
Hi Martin, thanks for this wonderful tuturial. I am a googlesheets for dummies type of level and even I was able to follow your tutorial. Is there any way I can do the same for floor prices on opensea? I could not make it work. Would appreciate if you could do a tutorial on that.
I'm glad you found this video helpful! Replicating this for Opensea would be pretty similar (depending on which data you would like to scrape). If you have a recent version of Msft Excel, you can easily import tables from a website. Check out this tutorial (not mine): czcams.com/video/NdUZx_yyEqY/video.html&ab_channel=LeilaGharani Hope you find it useful!
Hello, thank you very much for your wonderful video. I have a problem on set up refresh data, I have followed your step carefully, but it does not refresh the data price. The price is still keeping the same amount (not changing). Could you show more on your sheet when you refresh your sheet? thanks in advance.
For some coins it does not work with the same XPATH. In those cases, you need to copy the XPATH again and use that one instead! Let me knwo if you could solve it!
I cannot get the refreshes to work properly. The macro is functioning fine, but the data is not updating. It's more like coiinmarketcap only updates their XML streams every 30 minutes or so. Is there a way to get it to update more regularly?
Hi, I have watched many videos on CZcams on how to create a crypto price tracker through Google Sheets. You have by far the best tips and tricks which works wonderfully. Thank you for sharing it with us. However, I have a quick question. Why the numbers imported under the "% Change 24h" column are all positive numbers only and not showing negative change in price? Is there a way to fix this?
@@marty_mk Thank you. Yes, I have no problem following the details after at 14:00. However, I would like to get the minus sign inserted for values under "% Change (24H)" column. For example, you have a crypto called Unus Sed Leo which supposed to be "-1.58%" instead of a positive value. Then you came up with a Change_Amount_Xpath solution and you hided them with green and red color but this is not the part that I am asking. So how do we figure out getting the minus amount/value which is "-0.04181" for Unus Sed Leo to be reflected as a negative change in the percentage column (from +1.58% to -1.58%)?
@@alpk7032 , instead of using the change percentage, you can get the profil/loss over the last 24HS. Having that, you can implicitly calculate the change in percentage: change_pct = (current_price / (current_price - daily_profit_loss)) -1
hi, i have a problem, i do the same formula as you to get out the "$"... but it still gets me the error #VALUE!, that says it is a text format... can u help me? tyvm
I LOVE THIS VIDEO! Thank you so much. I've been researching all day and this has finally helped me with exactly what I needed! I do have a question: In your example, UNUS SED LEO's price dropped in the last 24hour, meaning you would have lost money. Since there is no minus in the 'profit/loss' column doesn't that mean that your 'total portofolio value' is wrong since it would have added instead of subtracted it? Is there a way to indicate with the 'daily % change'- numbers whether it was '+' or '-'? Please excuse me if I was mistaken, but could you explain how that works?
Thanks for the feedback! Yes, some poeple had a similar issue and they were able to solve it by copying the XPath again using shiba-inu's url. Let me know if that worked!
thanks! this method worked without having to mess with the scripts. i ran into one problem which was trying to get the price for the graph. i put the hyphen in between like how its formatted on coinmarketcap but still comes out with errors. any idea?
@@davidSYcho replacing the full xpath with the xpath GRT is giving me a parse error. Did you have to do anything else? I have this issue with a few coins. Thanks
Thanks for taking the time to make the video. But my data remained static for hours. I got this basic script to actually update by incorporating a sleep timer into the macro. 1) delete BASE_URL cell 2) Sleep 3000 milliseconds 3) copy BASE_URL back to the original cell from a second cell I had stored the information. Deleting the BASE_URL forces a recalculation of the cell which results in a error. Copying the information back after a 3 second wait forces another calculation with fresh data. CTRL+Z just deletes the previous statement from the macro. When any of the queries such as price change and price percent change return a plus or minus sign formatted in a text field, I use a double negative (minus, minus returned text value) to force a change to a number field. Works as expected now. I update prices every 5 minutes but it works with 1 minute as well.
@@splashbeats_ You are going to have to search for it. I have, unfortunately, tried to reply 10 time but my comments are always deleted. A sleep timer just pauses the execution of macro for a designated period of time allowing the data in a particular cell to be updated.
spent over 40h now trying to firgure out how to get the sheet to automatically refresh and noting seems to work and can't find any information what so ever on how to do this... not possible in current version or smth?
Some people also had similar issues to yours and they solved it by doing the following: Delete the column with the crypto names and ctrl-Z to bring the column back and the prices will update. Let me know if that works!
@@marty_mk thanks yeah I have no issue getting it to work manually the way you described here, I figured our how to do it automatically with an app key linking coinbase to an add on, seriously they should just include an auto refresh setting for xml functions
@@elias4855 there is an add on called syncwith for 10$ q month you get imported auto updates every 5min you can select categories like price 90 day performance and volume
I don't get it. It just doesn't work. I type the command correctly but I get answer Ν/Α with error: Imported content is empty. It worked for some minutes but not anymore (didn't change anything).
Subscribed!, Thanks for the tutorial. However, some of the currencies gave an error: imported content is empty. For example, aave, the-graph..etc. What do you think is wrong?
You are right! It seems like there is something different for those cryptos. I solved it by copying the XPATH again (that one won't work for BTC for example). If you want to have them all, I think you should have both xpaths, or add a conditional IF to use the other XPATH in cases where the first one returns an error. Let me know if that worked!
What worked for me was to use "Copy Full XPath" from the dropdown menu and use that for the specific cryptos that didn't work with the regular method. For me I needed the Full XPath for DOT, POLYGON, and HELIUM.
I was able to get everything inputed and followed along, but it's not updating for me. I added the trigger and and recorded the macro. What do you think is wrong?
Some users are facing similar issues, whereas it works for others. It is definitely strange! You can try editing the macro in order to do other things that also force to recalculate. For example, adding a character to the crypto names so that it yields an error and then removing it, forcing it to recalculate.
Hi, Is there any way I can purchase the completed spreadsheet in the demo you give? Along with any updates you have made? I am a very beginner tech person and would like to analyze the way it works and study the formulas in case I need some hand holding. I would gladly send you a zelle, cash app or paypal payment for the completed google sheets link.
Hi Johannes! Whenever I have that issue you are describing, I add +0 at the end of the formula. That forces the cell to be a number. Let me know if that worked!
Giuseppe, try again by adding a +0 at the end of the formula. This tends to solve those issue because it forces the cell to a value. Let me know if that solved your issue!
@@marty_mk Still not working, I've tried this formula =SUBSTITUTE(IMPORTXML(BASE_URL&C6;PRICE_XPTH) ; "€" ; "")+0 It returns #REF any others way to try? Thanks for the help
The CHANGE_AMOUNT_XPATH is not working for me. Google sheet says that the imported content is empty. Can anyone help me? Please. Thank you sooooo much!
hello and thanks for your video but i have some problems "Parameter 1 of the "ADD" function" has number values. However, "19135.49" is a text value and cannot be coerced to a number value. can someone help me
Yep! Now that I'm back at creating content, this is on my todo list! I have to format a few things before sharing it, but I'll make it available in a few days. Thanks for the feedback!
The percentage change did a weird thing.. the cell it was used was empty and the cells next to it filled with the number and the next one with the percentage sign.. What do I do ??
Hadique, I'm sorry but I don't quite follow what is happening. Could you please tell me the steps and formulas so that I can try to recreate the problem?
@@marty_mk I applied the formula exactly the way you did.. followed it exactly.. but when I entered the formula in one cell, the values displayed onto the adjacent cells like First cell: blank | next cell: 1.34(the number value) | next cell: % (just the percentage sign)
When I delete and undo for example. Some work, some don't. Then they all switch. The ones that didn't work now work and vice versa. Almost like there's a limit
I've done only one column which is "Price" and did everything as you did but I still can't get the Price to refresh depending on real-time market value. Tried doing the macro recording and also deleting the tokens' column and then cntrl+z, nothing is working out the value isn't changing when I refresh.. PS: I would like to add GST token, which isn't available on Google Finance therefore cant use their formula
Thanks for the feedback! As soon as I have a few minutes to spare, I'll clean and tidy my file and share the link so that everyone can check out the formulas!
Good idea. I will have to clean in a little before because I used it to test some questions and issues that people had. I'll definitely do it! Thanks for the suggestion!
Thank you for the Vedio. Please help ..When I did a right click on a price of Bitcoin..it doesn’t show” inspect” so I can’t copy an expath in the html code
Are you using Chrome or Firefox? I don't know how it works on other browsers, but you can also go ahead and copy the xpath from here (prices): //*[@id="__next"]/div/div[1]/div[2]/div/div[1]/div[2]/div/div[2]/div[1]/div
great tutorial - worked except for the refresh macro which came backwith a ReferenceError: f is not definedDetails and any URL that has a hypen in it, eg poldadot-new does not work. I also could not get more then 2 decimals places in the returned prices (not a format issues). Any help appreciated
Hi. Thanks for the video. One question. In Change_Amount_Xpath section, when u were copying from Coinmarket, u had highlighted the change amount, and this was reflected in ur xpath. I have 2 problems, 1. I dont know how to highlight the change and 2. whenever im copying the change_amount_xpath data from coinmarket to google sheets, it is showing error. Rest of the changes are working. Kindly let me know how i can resolve this? \
It is because some of the crypto coin pages are formatted slightly different. For certain coins that are not syncing you have to copy the xpath specifically from that coins page in order for it to sync.
Very helpful thank you. Suggestion: post a link to a "read-only" version of your Google Sheet so that everyone who watches doesn't have to recreate themselves. they can just use your template as a starting point and save a copy.
For those having issues with the macro not updating the prices. Delete the column with the crypto names and ctrl-Z to bring the column back and the prices will update.
This seems to work better than the macro that I recorded! Excellent suggestion!
Thank you very much for sharing Bruce! I'll pin the comment so that everyone can find it.
@@marty_mk You're welcome.
But I can't record it as a macro, because when I press ctrl+z, the macro stops. Can you help me?
@@aaronh.1372 I tried recording the macro too but it doesn’t work. You just have to delete the column manually.
@@BruceLevickbrusca ok thanks
You, Sir, have my deepest appreciation for this Tutorial. Helped me a lot to track my assets in one spreadsheet.
Thanks, I really appreciate the feedback!
this video is the ONLY ONE that solved HOW TO CONVERT CURRENCY into NUMBER. really made my day, I lost weeks before getting to this video. LOVE bro.
Your comment made my day! Thank you very much for those kind words!
@@marty_mk tks to you buddy! 😎👌🏻
Had to struggle for a bit with the format when trying to pull the data via the ImportXML function. It was due to a delimiter setting. I needed to use semi colon instead of a comma ;). Thanks for sharing the video!
I had the same issue
I was really searching for a video which tells how to refresh the data automatically.
Great tutorial ! Very helpful.
You are a life saver. You really helped me convert my Excel dashboard to Google Sheets. This now has set me free from my laptop as I can access my dashboard from anywhere plus I can give access to my associates wherever they might be. God bless you.
I'm really glad you found it useful, Michael! I really appreciate the kind words!
Thankyou so much! By far the fastest tutorial i've seen in youtube
Thank you! I really appreciate the feedback!
The only tutorial that really worked for me, thank you!
I'm glad you found it useful!
"And I also have some DOGE, because you have to have some fear of missing out at least once in your life". I couldn't stop laughing!
hahahahhaha I'm glad you liked it!
I´m brazilian and I got it, I´m so grateful to you.
Glad I could help! Greetings from Argentina!
My deepest appreciation for this Tutorial. You are amazing and so helpful. Helped me a lot to track all my cryptos in one spreadsheet.and from any page of coin base market web site. Thank you.
Thanks a lot, Ali! I really appreciate the very kind words!
In case you want to copy and paste the XPATH's, here they are!
PRICE_XPATH //*[@id="__next"]/div/div[1]/div[2]/div/div[1]/div[2]/div/div[2]/div[1]/div
CHANGE_XPATH //*[@id="__next"]/div/div[1]/div[2]/div/div[1]/div[2]/div[2]/div[1]/span/text()[1]
CHANGE_AMOUNT_XPATH //*[@id="__next"]/div/div[1]/div[2]/div/div[3]/div/div[1]/div[2]/div[2]/div/div[1]/table/tbody/tr[2]/td/span
How to deal with some coins that have different xpaths... Like Luna?
you are a rock star - truly a hidden gem.. you solved my problem. thank you very much mate
I really appreciate the comment! Thanks a lot for the positive feedback, it really makes my day!
@@marty_mk you are wellcome, i must you create high quality content. those steps helped me get through most of them apart from few like - Polkadot, Luna, aave, ckb, iotex, slp. Is there a way to get prices for these. same formula is showing error ?
@Martin - ignore me, I figured that out. cheers
This was great thanks. It worked initially, but now the cells that fetch the data just say "error loading Data" Anyone else had this problem and know the solution?
change coin name to lowercase and some coins price xpath is different (like CKB)
Thanks a lot, i have to put some url of cripto and its change % manually, but it works! This helped me very much.
I'm glad you found it useful, Tomas!
%Change is not displaying the negative symbol for negative values, how to display the negative symbol?
yes, how to solve it?
HELLO THANKS FOR YOUR VIDEO, WHY DOES THE CHANGE IN 24H MARK ONLY THE POSITIVE NUMBER AND NOT THE (-) NEGATIVE ONE?
HOW CAN I SOLVE? THANK YOU
Danke Martin!
Bitte schoen!
Hi Marty, great video. Just one big problem, it is not working for me the way to convert the IMPORTXML into a number. I just cant get the reason why. I followed exactly what you did, and it wont work :(
buy high sell low lol love it and fumo omg. perfect
😂
FANTASTIC VIDEO, CONGRATS!
Thanks, Giovanni!
Very helpful, thanks.
I'm glad you found it helpful! Thanks for the feedback!
This was teh video that i needed
I'm glad you enjoyed it, Sochi!
Thankssssssssss!!!!!! pretty simple to implement and works great
Glad you liked it! Thanks for the comment!
It is not working with BTC price. Absolutely working fine with USD. Is there any way to work with BTC
Hey Manzar! Try copying the XPATH again specifically for those cryptos that are not working. Some people have solved it that way! Let me know if this solves the issues for you!
Very helpful indeed thanks a lot
But i have a question. What if we have various entries for 1 cryptocurrency
Could you please make a video on that ?
Thanks for the feedback! I'll try to add your video idea to the list of upcoming videos!
IT WORKS! AWESOME!
hi, it doesnt work for coin with dash "terra-luna" "polkadot-new". What can id o to fix it?
Thanks for the feedback Paustina!
@@paustinachou915 try copying the XPATH again specifically for those coins. Some coins seem to have a different website structure! Let me know if that works!
so cool! thx for this =)
I'm glad you liked it! Thanks for the feedback!
i have issue with the math function with the price value, there is an errror cause the price value is a text and can't be used as number in the multiply function
Hi Andrea! Try adding a +0 or something that does not change the result, but forces it to interpret it as a number.
Let me know if that worked!
I followed the steps, but there is a 'resource at URL not found‘, need help
this is fantastic thank you! any suggestions on how to get values displaying AUD not USD?
Michael! Thanks for the feedback! Regarding your question, take a look at the GOOGLEFINANCE function. Here's an example: GOOGLEFINANCE("CURRENCY:USDGBP")
It is working! Thank you so much.
Great!
Thank you. Its working.
Finally, I found u Thank!!! a lot
Thank you very much for the positive feedback, I'm glad you liked it!
at first i want to skip this long tutorial then you mentioned ICP and you love to buy high and sell low 🤣i watched till the end.
hahahahahha! I'm glad you liked it!
The Best.
Thanks for Sharing
Thabks, aMan! I'm glad you enjoyed it!
not sure why Im having issues with the value, I know how to convert that text into numbers format , but somehow I'm doing a normal operation and the result it's wrong.. otherside, this is the best video ever ,
Thanks for the feeback, Sebas! Let me know if I can help you in solving the problem. Maybe, the solution to your issue is already in the comments. Have a great week!
yep, got the Problem too and i don't know how to fix it
@@leonneumann4152 I don't know the specific cause that is causing your issue, but could you take a look at the comments and see if one of the suggested solutions is useful to you? I recommended adding a 0 at the end in order to transform the cell into a number, but some viewers solved it in other ways!
Let me know if you managed to solve it!
Genius
I'm glad you liked it, Philip! Thanks for the feedback!
Followed steps most of it worked... Can I share googlesheet with you? to clean up some errors.
Of course Brodie, I would really appreciate it!
Subscribed: You're my hero.... Is there anyway i can capture and record data everyday? So i can place it on a chart 365 days or more. For example, i want to capture and record the 24hr volume, at 4pm everyday so that i can see the historical daily volume in a 30day or so, timeframe. I want to track daily reflections and supply burn rate so i can visually see fluctuations and patterns if i put it on any charts. Ive been looking for someone who made a video about doing this with no luck. Thank you I appreciate your work.
Thank you very much for those kind words!
If you don't mind switching to excel, you might find this other video useful for your problem at hand:
czcams.com/video/oI3_P5UKZTM/video.html
I have a question.
How do you take into account the profit that you took ?
So I bought 20 times some BTC, I have 0.09 BC and I sell 2 times, so let's say -0.02 BTC.
What is the best way to show that in the table ? Thanks
Hello, it's a great video. Thank you, but I have a problem with substitute formula - it removes the $ symbol, but I still can not to make any operations with that imported data, google sheets says that is still text. How to fix it?
I'm glad you are finding the video useful! Most often than not, in order to force it to be a number, it is enough with adding "+0" at the end of the formula.
Let me know if that solved the issue!
@@marty_mk Not worked :(. Can you exact formula ? Thank you
@@goefemstories4737 I reuse the same approach, and it works, by replacing point with comma: =SUBSTITUTE(SUBSTITUTE(IMPORTXML(base_url&D7; price_xpath);"$";"");".";",")
@quantmate can i have copy example ready of file for download? some formula continually returns me error. thanks!
I need to clean the sheet a little bit! But I'll do it as soon as I have a few minutes!
I followed the step but its not refreshing
Thank you for the video.
Everytime its showing "loading" when triger active.
its annoying,, is there any way to stop "loading"??
only updated value will appear without showing "loading"??
What an awesome video, thank you. can somebody tell me how to get the euro prices?
+1 to this. :)
Hey Tiberius! You can use the "googlefinance" function. Here's an example: =GOOGLEFINANCE("Currency:USDGBP").
He Lisa, I already answered to Tiberius, but I'm not sure if you'll get notified, so I'll also answer to your comment. You can use the "googlefinance" function. Here's an example: =GOOGLEFINANCE("Currency:USDGBP").
Thank you so much :)
@@marty_mk thanks so much!!
In Brasil, they use 49.984,22 format where as in USA 49,984.22 this may cause an error in some sheets.
That is a good observation, and everyone should make sure that the formatting matches! Thanks Bilel!
great video i just have a tiny problem with sandbox, i've tried every way to write it, but it still won't work do you have an idea why is that?
i had the same problem for terra-luna, polkadot-new, shiba-inu.. any solutions for this? for some reason it just registered as "N/A"
This video is very informative.
Can you please do something like a $1,000 challenge?
More Power
Thanks for the deedback!! What do you mean about the $1.000 challenge?
Hey man! I've seen 5 tutorials of these... tried out 5 different formulas.. this one is the first that works for me. Thanks a lot!
Is there a way to track a historic price? or maybe a way of defining a price with a macro? I create new columns every 2 weeks to keep a track in time, duplicate the columns of price token and total of each, to check in new movements in price, buyings and sellings... but once I create this new column (which I want the price to be updated), I wish the previous price column does not update price anymore so I know what price it was 15 days ago. Makes sense? Is there a way? Thanks!
same question. Looking for a alternative to cryptosheets which costs a lot lol
Hi Martin, thanks for this wonderful tuturial. I am a googlesheets for dummies type of level and even I was able to follow your tutorial. Is there any way I can do the same for floor prices on opensea? I could not make it work.
Would appreciate if you could do a tutorial on that.
I'm glad you found this video helpful! Replicating this for Opensea would be pretty similar (depending on which data you would like to scrape).
If you have a recent version of Msft Excel, you can easily import tables from a website. Check out this tutorial (not mine):
czcams.com/video/NdUZx_yyEqY/video.html&ab_channel=LeilaGharani
Hope you find it useful!
@@marty_mk Thanks brother. I will try it.
@@vadia100 hope you find it useful!!
Hello, thank you very much for your wonderful video. I have a problem on set up refresh data, I have followed your step carefully, but it does not refresh the data price. The price is still keeping the same amount (not changing). Could you show more on your sheet when you refresh your sheet? thanks in advance.
for some reason, terra-luna doesnt pick up for me, any idea why?
same here, looking for tips on how to fix this
i can't do it....the price keep reloading....didnt show up...anything i can do?
global problem dev of google are trying to solve it
Hey sir the work is wonderful however some coins doesn't work (Luna and 1INCH) it shows imported content is empty any tips how to fix this
For some coins it does not work with the same XPATH. In those cases, you need to copy the XPATH again and use that one instead! Let me knwo if you could solve it!
@@marty_mk Thanks. Work well !
Error : Resource at URL contents exceeded maximum size.,,,, is the website too big to be processed by sheets??
I cannot get the refreshes to work properly. The macro is functioning fine, but the data is not updating. It's more like coiinmarketcap only updates their XML streams every 30 minutes or so. Is there a way to get it to update more regularly?
Hi, I have watched many videos on CZcams on how to create a crypto price tracker through Google Sheets. You have by far the best tips and tricks which works wonderfully. Thank you for sharing it with us. However, I have a quick question. Why the numbers imported under the "% Change 24h" column are all positive numbers only and not showing negative change in price? Is there a way to fix this?
Hi! I cover that issue starting at 14:00. Let me know if you could solve it!
@@marty_mk Thank you. Yes, I have no problem following the details after at 14:00. However, I would like to get the minus sign inserted for values under "% Change (24H)" column. For example, you have a crypto called Unus Sed Leo which supposed to be "-1.58%" instead of a positive value. Then you came up with a Change_Amount_Xpath solution and you hided them with green and red color but this is not the part that I am asking. So how do we figure out getting the minus amount/value which is "-0.04181" for Unus Sed Leo to be reflected as a negative change in the percentage column (from +1.58% to -1.58%)?
@@alpk7032 , instead of using the change percentage, you can get the profil/loss over the last 24HS. Having that, you can implicitly calculate the change in percentage:
change_pct = (current_price / (current_price - daily_profit_loss)) -1
@@marty_mk I couldnt solve this "import content empty" =(
you never fixed the % change 24 hrs so it still shows positive changes even tho its negative that day
hi, i have a problem, i do the same formula as you to get out the "$"... but it still gets me the error #VALUE!, that says it is a text format... can u help me? tyvm
I LOVE THIS VIDEO! Thank you so much. I've been researching all day and this has finally helped me with exactly what I needed!
I do have a question: In your example, UNUS SED LEO's price dropped in the last 24hour, meaning you would have lost money. Since there is no minus in the 'profit/loss' column doesn't that mean that your 'total portofolio value' is wrong since it would have added instead of subtracted it? Is there a way to indicate with the 'daily % change'- numbers whether it was '+' or '-'?
Please excuse me if I was mistaken, but could you explain how that works?
try to import the price of polkadot, but its URL has a dash like polkadot-new,
the cell can't read, how to make it able to read?
I have the same issue with that coin and terra-luna. Did you find a fix for it?
Thanks QuantMate....
Somehow shiba-inu price cannot be capture...any idea?
Thanks for the feedback! Yes, some poeple had a similar issue and they were able to solve it by copying the XPath again using shiba-inu's url.
Let me know if that worked!
thanks! this method worked without having to mess with the scripts. i ran into one problem which was trying to get the price for the graph. i put the hyphen in between like how its formatted on coinmarketcap but still comes out with errors. any idea?
David, which crypto are you trying to get? Could you please share the formula so that I can review it?
Thanks for watching the video!
@@marty_mk i figured it out. some cryptos, you have to use the xpath full instead of the regular xpath. an example was the graph (grt)
@@davidSYcho replacing the full xpath with the xpath GRT is giving me a parse error. Did you have to do anything else? I have this issue with a few coins. Thanks
how can i know if the % change 24h is positive or negative, when im pulling the data its just show whole % not knowing if its positive or negative %
Thanks for taking the time to make the video. But my data remained static for hours. I got this basic script to actually update by incorporating a sleep timer into the macro. 1) delete BASE_URL cell 2) Sleep 3000 milliseconds 3) copy BASE_URL back to the original cell from a second cell I had stored the information. Deleting the BASE_URL forces a recalculation of the cell which results in a error. Copying the information back after a 3 second wait forces another calculation with fresh data. CTRL+Z just deletes the previous statement from the macro. When any of the queries such as price change and price percent change return a plus or minus sign formatted in a text field, I use a double negative (minus, minus returned text value) to force a change to a number field. Works as expected now. I update prices every 5 minutes but it works with 1 minute as well.
Chris, thanks for all sharing all those improvements! They are very interesting!!
what do you mean sleep timer
@@splashbeats_ You are going to have to search for it. I have, unfortunately, tried to reply 10 time but my comments are always deleted. A sleep timer just pauses the execution of macro for a designated period of time allowing the data in a particular cell to be updated.
spent over 40h now trying to firgure out how to get the sheet to automatically refresh and noting seems to work and can't find any information what so ever on how to do this... not possible in current version or smth?
Some people also had similar issues to yours and they solved it by doing the following:
Delete the column with the crypto names and ctrl-Z to bring the column back and the prices will update.
Let me know if that works!
@@marty_mk thanks yeah I have no issue getting it to work manually the way you described here, I figured our how to do it automatically with an app key linking coinbase to an add on, seriously they should just include an auto refresh setting for xml functions
@@Libra_custos88 could you please tell me how you did the coinbase linking thing? Data isn't refreshing for me as well..
@@elias4855 there is an add on called syncwith for 10$ q month you get imported auto updates every 5min you can select categories like price 90 day performance and volume
I don't get it. It just doesn't work. I type the command correctly but I get answer Ν/Α with error: Imported content is empty.
It worked for some minutes but not anymore (didn't change anything).
thank you, it was awesome, I'm really happy to know this. I really appreciated it.
Thank you very much for those kind words! I'm really glad you enjoyed it!
Subscribed!, Thanks for the tutorial. However, some of the currencies gave an error: imported content is empty. For example, aave, the-graph..etc. What do you think is wrong?
You are right! It seems like there is something different for those cryptos. I solved it by copying the XPATH again (that one won't work for BTC for example). If you want to have them all, I think you should have both xpaths, or add a conditional IF to use the other XPATH in cases where the first one returns an error.
Let me know if that worked!
What worked for me was to use "Copy Full XPath" from the dropdown menu and use that for the specific cryptos that didn't work with the regular method. For me I needed the Full XPath for DOT, POLYGON, and HELIUM.
@@georgewkush_ thanks for letting us know. Much appreciated!
@@georgewkush_ , thank god for you buddy...
I was able to get everything inputed and followed along, but it's not updating for me. I added the trigger and and recorded the macro. What do you think is wrong?
Some users are facing similar issues, whereas it works for others. It is definitely strange! You can try editing the macro in order to do other things that also force to recalculate. For example, adding a character to the crypto names so that it yields an error and then removing it, forcing it to recalculate.
Hi, Is there any way I can purchase the completed spreadsheet in the demo you give? Along with any updates you have made? I am a very beginner tech person and would like to analyze the way it works and study the formulas in case I need some hand holding. I would gladly send you a zelle, cash app or paypal payment for the completed google sheets link.
Hi! I have plans to share it for free, but I have not had the time to recreate the workbook yet. I'll promise to do it in the next couple of days!
pleaseee help me to get wazir x data , please please please!!! its not getting exported for wzair x
many thanks bro
I'm glad you liked it! Thank you very much for the feedback!
Even after "SUBSTITUTE" the currency symbol, it's treated as a String. Recommendations/Ideas?
Hi Johannes!
Whenever I have that issue you are describing, I add +0 at the end of the formula. That forces the cell to be a number.
Let me know if that worked!
For me worked with changing "spreadsheet settings" in "file" from Location X to United States. Great tutorial btw. Thanks!
I reuse the same approach, and it works, by replacing point with comma: =SUBSTITUTE(SUBSTITUTE(IMPORTXML(base_url&D7; price_xpath);"$";"");".";",")
Would be cool to see how to handle different currency formats in google sheets.
i cant do operation, because in fomulas it says that is text and not number, i also add + 0 at the and but nothing, can you help me?
I've removed the dollar sign but still can't perform any arithmetic how can I do? Please thanks
Giuseppe, try again by adding a +0 at the end of the formula. This tends to solve those issue because it forces the cell to a value.
Let me know if that solved your issue!
@@marty_mk Still not working, I've tried this formula
=SUBSTITUTE(IMPORTXML(BASE_URL&C6;PRICE_XPTH) ; "€" ; "")+0
It returns #REF any others way to try?
Thanks for the help
Hello guys, is it possible that, few coins don't work? I had issue with Cronos and polkadot, other coins works. What can be the issue?
The CHANGE_AMOUNT_XPATH is not working for me. Google sheet says that the imported content is empty. Can anyone help me? Please. Thank you sooooo much!
hello and thanks for your video but i have some problems
"Parameter 1 of the "ADD" function" has number values. However, "19135.49" is a text value and cannot be coerced to a number value.
can someone help me
How about those crypto name that have this " - " ex. Shiba-inu, cause the formala is not working anymore
can we have a copy of that doc plis? thanks and good video
Yep! Now that I'm back at creating content, this is on my todo list! I have to format a few things before sharing it, but I'll make it available in a few days.
Thanks for the feedback!
The percentage change did a weird thing.. the cell it was used was empty and the cells next to it filled with the number and the next one with the percentage sign..
What do I do ??
Hadique, I'm sorry but I don't quite follow what is happening. Could you please tell me the steps and formulas so that I can try to recreate the problem?
@@marty_mk I applied the formula exactly the way you did.. followed it exactly.. but when I entered the formula in one cell, the values displayed onto the adjacent cells like
First cell: blank | next cell: 1.34(the number value) | next cell: % (just the percentage sign)
Some of them just don't work while opthers so. Like "bnb", "polkadot-new" etc
When I delete and undo for example. Some work, some don't. Then they all switch. The ones that didn't work now work and vice versa. Almost like there's a limit
I've done only one column which is "Price" and did everything as you did but I still can't get the Price to refresh depending on real-time market value. Tried doing the macro recording and also deleting the tokens' column and then cntrl+z, nothing is working out the value isn't changing when I refresh..
PS: I would like to add GST token, which isn't available on Google Finance therefore cant use their formula
Thanks for the video, very informative. The only problem i have so far is that all my % changes show 0.00%. Do you know how to fix this?
Thanks for the feedback! As soon as I have a few minutes to spare, I'll clean and tidy my file and share the link so that everyone can check out the formulas!
I followed all the steps and it worked!
but after a while it stopped updating, any tips ?
I'm strugglin with SHIBA, I can't import the price.
link to the shared sheet would be cool.
Good idea. I will have to clean in a little before because I used it to test some questions and issues that people had. I'll definitely do it!
Thanks for the suggestion!
getting error while loading the price resource at url not found
Thank you for the Vedio. Please help ..When I did a right click on a price of Bitcoin..it doesn’t show” inspect” so I can’t copy an expath in the html code
Are you using Chrome or Firefox? I don't know how it works on other browsers, but you can also go ahead and copy the xpath from here (prices): //*[@id="__next"]/div/div[1]/div[2]/div/div[1]/div[2]/div/div[2]/div[1]/div
@@marty_mk thank you very much…I copy and try it again…by the way …I use safari…is it an issue?
Thank you again..I’ve just tried using chrome..it works! Hooray 😃, thanks again
Not working right now
great tutorial - worked except for the refresh macro which came backwith a ReferenceError: f is not definedDetails and any URL that has a hypen in it, eg poldadot-new does not work. I also could not get more then 2 decimals places in the returned prices (not a format issues). Any help appreciated
I can't use it with with some cryto like JADE or Hector DAO. Any suggestion? Thanks.
Hi. Thanks for the video. One question. In Change_Amount_Xpath section, when u were copying from Coinmarket, u had highlighted the change amount, and this was reflected in ur xpath. I have 2 problems, 1. I dont know how to highlight the change and 2. whenever im copying the change_amount_xpath data from coinmarket to google sheets, it is showing error. Rest of the changes are working. Kindly let me know how i can resolve this?
\
SLP AND AXS CAN`T SYNC TO SPREAD SHEET HOW TO FIX THAT ?
Hey Jan! Could you elaborate a little further on the error so that I can better guess what is causing the issue?
It is because some of the crypto coin pages are formatted slightly different. For certain coins that are not syncing you have to copy the xpath specifically from that coins page in order for it to sync.
I love this video. Thank you.
I have one problem @martin mayer. I cant see exact the price (I mean it is rounded) if it has more then 2 decimals
great :)
Thank you! I really appreciate it!
Very helpful thank you. Suggestion: post a link to a "read-only" version of your Google Sheet so that everyone who watches doesn't have to recreate themselves. they can just use your template as a starting point and save a copy.