ฝัง
- เผยแพร่เมื่อ 12 ธ.ค. 2021
- Important: Please visit • Import Live Cryptocurr... for updated solution.
Google Sheets has very limited support when it comes to pulling cryptocurrency live price. In this tutorial, I will share a workaround how you can use the IMPORTXML function to import cryptocurrency price in real time.
📺 Follow Up Video (Part 2): • Import Cryptocurrency ...
📑 Download Crypto template Google Sheets and app script here: learndataanalysis.org/google-...
► Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------------
☕ Paypal: www.paypal.me/jiejenn/5
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: bit.ly/3iWr7LC
► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
🌳 Becoming a Patreon supporter: / jiejenn
🛒 By shopping on Amazon → amzn.to/2JkGeMD
📘 Facebook Page → / madeinpython
📘 More tutorial videos on my website → LearnDataAnalysis.org
✉️ Business Inquiring: TH-cam@LearnDataAnalysis.org
#Sheets #GoogleSheets #Cryptocurrency #Crypto
Important: Please visit th-cam.com/video/CKs83Q_ddoA/w-d-xo.html for updated solution.
Very easy to follow, straight to the Point. Great Tutorial helped a Lot in my spreadsheet
Glad the video helped.
Awesome !
Had to play with the replace functions and locales to clean the result and perfectly works ! Many thanks !
Finally. I've found the real working tutorial on this. Subscribed! 🙂👍
Awesome guide. Thank you so much. So clear and to the point. 🙏
Glad the video helped.
Thank you for the tutorial, it's amazing!
Glad the video helped.
Thanks for your video!
Thank you so much. Excellent video.
Wow - so easy. Thanks!
Awesome! Thank You!
Awesome, thanks a lot for sharing your knowledge ;)
Awesome, and thanks for this straightforward insanely useful video.
Glad the video helped.
Wow. Fantastic stuff, thank you kindly
Glad the video helped.
This is amazing! thank you so much!
Glad the video helped.
super helpful thanks bro!
Glad the video helped.
Great tutorial, thanks
Glad the video helped.
Wow thanks man!
Fantastic. This was much needed. One question. I do have all my other datapoints (quantity, purchase price etc.) in Microsoft Excel. I followed your procedure and downloaded all prices in a Google sheet but am not able to refer to this google sheet from excel. So i am having to copy and paste it every time. If anyone in this community have a better idea to make this truly dynamic (across Google sheet with above prices AND my excel file with quantity, purchase price etc.), that will be very helpful
thank you so much bro
Thanks for this tutorial, I will like to add the price of btc in realtime against other coins
Thank you mate
@jiejenn Can you help me with a problem? I wrote the same formula but it says "Error
Imported Xml content can not be parsed." What can I do?
awsome, thank you
Glad the video help.
Amazing a clear tutorial - thanks! How can I get I can get in another currency, such as AUD rather than USD?
Dude you are a God
If someone has issue like FORMULA PARSE ERROR , try to change separator to ; instead of ,
thanks bro!!
God bless u, thx a lot!!!😘
ty so much!
Glad the video helped.
TY !
thx Jie
It's just amazing to learn crypto industry about rising and down 0:45 fall of this industry
Hey thanks for info, code works fine, but it adds a column beyond with 24h volume numbers, although i did everything as you did, any suggestions how to get rid of it?
Hi there. I`ve managed to to do it, but im getting some array results too below each listed price. How do I get rid of them?
Great!
Glad the video helped.
I got error too. Complete code at 3:24. thank you for your help with this! I'll watch the updated video.
Best!
Ok so I watched more of your videos, which are great BTW, and I had the same issue of the text to number value as everyone has commented, and so on one of your videos you issued a free spreadsheet which I got, and then copied the code from the extract just the number cell, changing the cell to my first bitcoin cell, which for me was B6, and as I am useless at excel, and even after reading 20 times, couldn't get the substitute thing to work to change the dollar column text to a number to multiply, I have just copied and pasted this and then created my column to multiply my holdings, by the current price as a number. So for everyone, here is what I pasted =SUBSTITUTE(SUBSTITUTE(B6,"$",""),"%","") hope that's ok to put here :) Please keep doing more videos, I'm really getting into this a lot now. Thank you.
MANY MANY THANKS ;)
Thanks, can I use a similar formula to get the 24hr % / 7 day % values from the website onto google sheets?
Do you have the link of the page you are referring to?
@@jiejenn same page as you’ve listed
Amazing dude, amazing explanation, amazing English, amazing tutorial = amazing video experience. Nailed it, officially subscribed
is there a way to log BTC price at a specific time in the day? (say 12am)
how to convert pulled data into numbers? the importxml function import it as text
Hi - Sorry for the noob question - But the result is text, which I cant use in an equation. How do I convert it into a number?
Thanks,If I want to retrieve other columns like 24h or Volume(24h), where do I get these values?
I will be doing a follow up video to cover that topic.
@@jiejenn thanks I’ve been looking for that too
Can you help me to extract data token from alcor exchange for wax tokens. Please
is there also the possibility to add the remaining criteria like: 24h%, marketcap, volume, circulatin supply etc.?? or is this formula only for `priceValue` available? anyway great video 👍
This is actually a question brought up to me a few time. Please refer to v2 video for the solutions.
th-cam.com/video/LC00b7TYVQc/w-d-xo.html
this is just brilliant. Now when i the currency name I get the price, but in the cell below the price I seems to have a random figure in it, anyone know what this is please and how to get rid
Do you know how to get the close price?
I have two numbers that display for me. The correct bitcoin price in your cell C5. But a second number populates below automatically in your cell D5. How can we fix that so the second number doesn't display automatically in D5?
Hello man.
Everything clear and great but I don't see any update that was mentioned in the video. It is not working
does this only works for cmc?
i'm using google sheet and i copied paste the formula exact like you but i got this msg:
Error Imported content is empty.
Same
the website html structure had changed and the price value is not wrapped in a div with "priceValue" class anymore. Now it's in a 'span' element, which contains a class 'jxpCgO'. Changing the xpath part to //span[contains(@class, 'jxpCgO')] works for me.
thanks bro@@farferkugelis
thanks bro@@farferkugelis
where do you get the surname they are using instead, in case I would like to add the all time high or something else?@@farferkugelis
How can I get the price in GBP?
On min 00:01:58 what did he select on the B5 enter to select the bitcoin name? Anyone
How about change percent ?
Thanks! How shall I get lastday price?
I will be releasing a follow up video to cover that topic.
Hi, anyone know why the value is not refreshing for me? I changed the calculations settings to update every minute, but it is not recalculating.
Same thing happened to me and this used to work fine.
Thanks a lot for this very useful video, any idea why some tickets are coming as text, hence any following formulas are coming as #VALUE! (Function MULTIPLY parameter 1 expects number values. But '$4.31' is a text and cannot be coerced to a number.) I tried to do every possible way to convert text to number and still not working, any suggestion?
You need to remove the dollar sign first I believe.
@@jiejenn I already tried that and still the same issue, I wondering if the source data is text? I mean some of them are old coins but for example one is Tezos !!
Can you share your Google Sheets to TH-cam@LearnDataAnalysis.org
@@jiejenn How do you remove the dollar sign? I have the same issue
I did this: =Value(Substitute(SUBSTITUTE(Substitute(IMPORTXML($E$1&A5; "//div[@class='priceValue ']/span");"$";"");",";"");".";","))
great video! But How can I add the market cap?
Please refer to this video th-cam.com/video/LC00b7TYVQc/w-d-xo.html for answers.
amazing tutorial - how do I change the price to a different currency like GBP?
If you want to convert the prices to a different currency, you will first need to create a conversion table (th-cam.com/video/nMnYXR1NeLE/w-d-xo.html) and then do a lookup to get the exchange rate and calculate the converted price.
@@jiejenn video unavailable
Hi, thank you for the tutorial but the sheets aren't getting updated
I found out about this issue just recently. Here's an update video to cover a few of the limitations, including force re-calculation: th-cam.com/video/LC00b7TYVQc/w-d-xo.html
@@jiejenn Thank you
nice video. but some price are not appear as the show #N/A in the column like $1INCH, $TLM, $CRO and etc. Why?
Why are you including a dollar symbol in the ticker name?
can I know how get the historical data into google sheets for crypto?
Let me look into it.
Hi really good tutorial. I have a problem, this IMPORTXML was working till 3 or 4 days ago, now it's stopped. Any idea of what could have happened? Thanks!
Google Sheets' backend is currently experiencing some issue, engineers are working on it.
@@jiejenn ok, thanks! 🙂
Great video. I have a small problem. When I created a code, I've got a value and automatically below another value like 0.0353. I can't delete that automatically generated value. Also, if I delete a row i got #REF in btc code area and it says "Array result was not expanded because it would overwrite data in B4." I don't understand where is the problem.
the same problem
Bro all you need is just to add 1 row below each of the existing rows you have. Problem solved for me.
@@XEROPOP add 1 row below each of the existing rows you have, then filter blank rows out or leave it as is
@@tpootai2 ok, thanks, I'll try that But I have solved it another way
@@ViktorUnginovic How?
nice but i wanna se only currency value, not 24h Volume / Market Cap below the price. How fix tha?
I have the same problem. Did you figure this out?
Thanks it looks like an awesome solution, but it does not work for me, formula analysis error, it says.. Any insight? I checked the formula 3 times and i believe its written properly
I just tried, it is working for me. (=IMPORTXML("coinmarketcap.com/currencies/bitcoin", "//div[contains(@class,'priceValue')]"))
@@jiejenn even pasting your command it does not work, thanks anyway for your video, now i know it is possible to do this!
@@jiejenn changing the , with ; it works fine, thanks ^^
@@Kashue_ indeed changing to ; worked for me aswell thanks !
Any chance to explain how to convert to number, so i can use on the calculations?
The VALUE function can convert strings to numbers support.google.com/docs/answer/3094220?hl=en
@@jiejenn I am horrible with that :( all I need is remove Dollar sign from price column , so I can do math with price column :(
@@zweiche support.google.com/docs/answer/3098247?hl=en
=Value(Substitute(SUBSTITUTE(Substitute(IMPORTXML($E$1&A5; "//div[@class='priceValue ']/span");"$";"");",";"");".";","))
I follow the same steps but keep gettin error called " impoted cotent is empty"
+1
+1
+1
+1
Is it possible to get price value in euros? I could get it in Chinese Yuans, but that does not help me :)
If it is possible using a programming language such as Python to change the currency, but unfortunately, not with Google Sheets since you can only read the data.
anyone know how I can get the value in £ instead of $. Brilliant video!
use the replace function
thx very much bu showm th e market cap 24h vilume how to hide
Same with me. DId you find a way to fix this?
it's not work now, can you update the formula? please
Hi. Can you update it for 2023, seems it does not work. Also can you show how to get result in numbers not in text or currency, Thank you.,
Same issue. Can't get it to work in 2023
Can someone share the text of the working formula, i typed it and get #error Formula parse error. Thanks
You have to change the , in the formula with ; This worked for me atleast.
@@MarkayCS Thank You very much Markay :) I was wondering this same thing couple of hours last night and now everything works! Thanks! 👍🏻
@@MarkayCS thanks!!!
anyone else getting NA?
Just tested, it is working fine on my end.
Yes it is not working(
Error showing.Price N/A by this method.Give other ways as this is not working.
not working anymore. is there an updated version?
I will look into it.
This method quit working for me a few days ago. It has been working for months then suddenly stopped. Anyone else having issues?
Google Sheets' backend is currently experiencing some issue, engineers are working on it.
//, we cannot parse this operator in this context
Without looking at your file, I can only guess it is either 1) typo somewhere or 2) region setting is different from U.S.
mine is imported content is empty
Currently working on an alternative. Stay tuned.
Any chance to explain how to convert to number, so i can use on the calculations? i already tried value,to_pure_number, removing $... but no sucess :(
BTW, thanks!
What's the formula you use?
it is because the diffirent between '"," and ".", first I remove "$" by =SUBSTITUTE(D6; ",$; ""), then remove "," by =SUBSTITUTE(e6; ","; ""), then change "." to "," by =SUBSTITUTE(f6; "."; ",") then use =value(g6) function and it gave me the price in number. Thanks Jie Jenn and good luck to all
@@BinhYT-uo8gj I looks like it worked here ! Thanks a lot! both of you guys... fully automated now \m/
Gonna try it latter. Guys you’re amazing!
@@BinhYT-uo8gj Dude that helped me fix it! I've been trying to for days!
it does not work anymore?
Will look into it. Looks like the page HTML is updated.
Ugh error code every time I come back to this what am I doing wrong
Not sure. Maybe a typo or two.
Not working for me...
It is working for me...
@@jiejenn i am pretty sure it is !! it never does to me... thanks for the video anyway
It just gives me a parsing error
Check if you entered the correct formula.
@@jiejenn I got it working now, thanks.
For whatever reason I had to use semicolons instead if commas in between the parameters?!
Doesn't work anymore.
ok
57k 😢
I take an error.
I will take a check.
I need price number version. Because I work for math
Ok
Not working
Don't know what to tell you, everyone else was able to get it to work.
Can this be converted in the formula to show as GPB (£)? @jieJenn?