Import Cryptocurrency Price In Real Time In Google Sheets
HTML-код
- Опубликовано: 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: RUclips@LearnDataAnalysis.org
#Sheets #GoogleSheets #Cryptocurrency #Crypto
Important: Please visit ruclips.net/video/CKs83Q_ddoA/видео.html for updated solution.
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! 🙂👍
Very easy to follow, straight to the Point. Great Tutorial helped a Lot in my spreadsheet
Glad the video helped.
@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?
Thanks for your video!
Awesome, thanks a lot for sharing your knowledge ;)
Awesome! Thank You!
Wow - so easy. Thanks!
Awesome guide. Thank you so much. So clear and to the point. 🙏
Glad the video helped.
Thank you so much. Excellent video.
Thank you for the tutorial, it's amazing!
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
Awesome, and thanks for this straightforward insanely useful video.
Glad the video helped.
Thanks for this tutorial, I will like to add the price of btc in realtime against other coins
Wow. Fantastic stuff, thank you kindly
Glad the video helped.
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?
This is amazing! thank you so much!
Glad the video helped.
Thank you mate
thank you so much bro
Dude you are a God
Great tutorial, thanks
Glad the video helped.
super helpful thanks bro!
Glad the video helped.
thanks bro!!
I got error too. Complete code at 3:24. thank you for your help with this! I'll watch the updated video.
thx Jie
how to convert pulled data into numbers? the importxml function import it as text
TY !
God bless u, thx a lot!!!😘
Amazing a clear tutorial - thanks! How can I get I can get in another currency, such as AUD rather than USD?
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?
Can you help me to extract data token from alcor exchange for wax tokens. Please
is there a way to log BTC price at a specific time in the day? (say 12am)
Thank you so much !
awsome, thank you
Glad the video help.
Amazing dude, amazing explanation, amazing English, amazing tutorial = amazing video experience. Nailed it, officially subscribed
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?
It's just amazing to learn crypto industry about rising and down 0:45 fall of this industry
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
ty so much!
Glad the video helped.
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 ;)
On min 00:01:58 what did he select on the B5 enter to select the bitcoin name? Anyone
Hello man.
Everything clear and great but I don't see any update that was mentioned in the video. It is not working
Great!
Glad the video helped.
does this only works for cmc?
Do you know how to get the close price?
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.
How can I get the price in GBP?
If someone has issue like FORMULA PARSE ERROR , try to change separator to ; instead of ,
How about change percent ?
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
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
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
it's not work now, can you update the formula? please
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 RUclips@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");"$";"");",";"");".";","))
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?
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
Error showing.Price N/A by this method.Give other ways as this is not working.
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?
I follow the same steps but keep gettin error called " impoted cotent is empty"
+1
+1
+1
+1
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: ruclips.net/video/LC00b7TYVQc/видео.html
@@jiejenn Thank you
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.
ruclips.net/video/LC00b7TYVQc/видео.html
Thanks! How shall I get lastday price?
I will be releasing a follow up video to cover that topic.
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!!!
Yes it is not working(
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 !
can I know how get the historical data into google sheets for crypto?
Let me look into it.
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.
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.
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?
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?
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! 🙂
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");"$";"");",";"");".";","))
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 (ruclips.net/video/nMnYXR1NeLE/видео.html) and then do a lookup to get the exchange rate and calculate the converted price.
@@jiejenn video unavailable
great video! But How can I add the market cap?
Please refer to this video ruclips.net/video/LC00b7TYVQc/видео.html for answers.
not working anymore. is there an updated version?
I will look into it.
anyone know how I can get the value in £ instead of $. Brilliant video!
use the replace function
Ugh error code every time I come back to this what am I doing wrong
Not sure. Maybe a typo or two.
//, 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.
anyone else getting NA?
Just tested, it is working fine on my end.
57k 😢
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
I need price number version. Because I work for math
Ok
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
I take an error.
I will take a check.
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?