Import Cryptocurrency Price In Real Time In Google Sheets

Подписаться 63 тыс.

Просмотров 75 тыс.

50% 1

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: RU-vid@LearnDataAnalysis.org
#Sheets #GoogleSheets #Cryptocurrency #Crypto

Опубликовано:

12 дек 2021

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 183

@jiejenn 2 года назад

Important: Please visit ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-CKs83Q_ddoA.html for updated solution.

@MATHIEUCHEVALIERweb 2 года назад

Awesome ! Had to play with the replace functions and locales to clean the result and perfectly works ! Many thanks !

@qeebod 2 года назад

Finally. I've found the real working tutorial on this. Subscribed! 🙂👍

@timo1294 2 года назад

Very easy to follow, straight to the Point. Great Tutorial helped a Lot in my spreadsheet

@jiejenn 2 года назад

Glad the video helped.

@MarcusKannberg95 9 месяцев назад

@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?

@wirbetc5261 2 года назад

Awesome guide. Thank you so much. So clear and to the point. 🙏

@jiejenn 2 года назад

Glad the video helped.

@Ravi-sf7sh 2 года назад

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

@krazesaplat1565 2 года назад

Awesome, and thanks for this straightforward insanely useful video.

@jiejenn 2 года назад

Glad the video helped.

@mussinata8133 Год назад

Awesome, thanks a lot for sharing your knowledge ;)

@artem_kukuruzza 2 года назад

Thank you for the tutorial, it's amazing!

@jiejenn 2 года назад

Glad the video helped.

@MarieOuaterBox Год назад

Thank you so much. Excellent video.

@michaelkamko 2 года назад

Awesome! Thank You!

@AlvaroRamosDelacoste 2 года назад

Thanks for your video!

@Gozelis Год назад

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?

@Rinaldi186490 2 года назад

Wow - so easy. Thanks!

@feherzsombi21 Год назад

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?

@Sean-xr1xj 2 года назад

Amazing a clear tutorial - thanks! How can I get I can get in another currency, such as AUD rather than USD?

@tanjirokamado7433 2 года назад

Wow. Fantastic stuff, thank you kindly

@jiejenn 2 года назад

Glad the video helped.

@davidcoogan4330 2 года назад

This is amazing! thank you so much!

@jiejenn 2 года назад

Glad the video helped.

@adefayeayodeji9839 2 года назад

Thanks for this tutorial, I will like to add the price of btc in realtime against other coins

@vistazopimp4650 Год назад

is there a way to log BTC price at a specific time in the day? (say 12am)

@andrearestori1839 2 года назад

how to convert pulled data into numbers? the importxml function import it as text

@javiermontoya8032 2 года назад

Wow thanks man!

@jordimargalef2629 2 года назад

Can you help me to extract data token from alcor exchange for wax tokens. Please

@ghost_clock 2 года назад

super helpful thanks bro!

@jiejenn 2 года назад

Glad the video helped.

@bishoyadelzakaria 2 года назад

Great tutorial, thanks

@jiejenn 2 года назад

Glad the video helped.

@EverythingWildNZ Год назад

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?

@gay30000000000000000 Год назад

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

@sutat11 2 года назад

Thanks,If I want to retrieve other columns like 24h or Volume(24h), where do I get these values?

@jiejenn 2 года назад

I will be doing a follow up video to cover that topic.

@hamsta891 2 года назад

@@jiejenn thanks I’ve been looking for that too

@thomas.o 3 дня назад

Thank you so much !

@zoalfiqars5255 2 года назад

thank you so much bro

@hamsta891 2 года назад

Thanks, can I use a similar formula to get the 24hr % / 7 day % values from the website onto google sheets?

@jiejenn 2 года назад

Do you have the link of the page you are referring to?

@hamsta891 2 года назад

@@jiejenn same page as you’ve listed

@Sirvival205 2 года назад

does this only works for cmc?

@hartikan70 2 года назад

Hello man. Everything clear and great but I don't see any update that was mentioned in the video. It is not working

@WoodyHertzogIII Год назад

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?

@marylisichkina9728 2 года назад

God bless u, thx a lot!!!😘

@carlavv5302 7 месяцев назад

On min 00:01:58 what did he select on the B5 enter to select the bitcoin name? Anyone

@Gragento Год назад

Do you know how to get the close price?

@JamesGuerrero 2 года назад

Dude you are a God

@JO-qe5or 2 года назад

awsome, thank you

@jiejenn 2 года назад

Glad the video help.

@syedaffifahmed7044 Год назад

It's just amazing to learn crypto industry about rising and down 0:45 fall of this industry

@CryptoChatWithAlex Год назад

Thank you mate

@PeteBuchwald 8 месяцев назад

I got error too. Complete code at 3:24. thank you for your help with this! I'll watch the updated video.

@Kazerilo 2 года назад

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.

@Nivek19 2 года назад

Same thing happened to me and this used to work fine.

@KryptoClub Год назад

thanks bro!!

@LagunaLeonhart Год назад

How can I get the price in GBP?

@borisaime-bauderlique9189 2 года назад

TY !

@mmmmmMuffinz 2 года назад

ty so much!

@jiejenn 2 года назад

Glad the video helped.

@stevenlong2177 7 месяцев назад

Can this be converted in the formula to show as GPB (£)? @jieJenn?

@wiseman235 11 месяцев назад

i'm using google sheet and i copied paste the formula exact like you but i got this msg: Error Imported content is empty.

@Chris.Finance 7 месяцев назад

Same

@farferkugelis 5 месяцев назад

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.

@wiseman235 5 месяцев назад

thanks bro@@farferkugelis

@wiseman235 5 месяцев назад

thanks bro@@farferkugelis

@sandraguajardo7970 4 месяца назад

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

@jm7990 2 года назад

thx Jie

@ukcryptogal48 Год назад

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.

@mussinata8133 Год назад

MANY MANY THANKS ;)

@basharkhdair 2 года назад

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?

@jiejenn 2 года назад

You need to remove the dollar sign first I believe.

@basharkhdair 2 года назад

@@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 !!

@jiejenn 2 года назад

Can you share your Google Sheets to RU-vid@LearnDataAnalysis.org

@Xolfcfan 2 года назад

@@jiejenn How do you remove the dollar sign? I have the same issue

@Kazerilo 2 года назад

I did this: =Value(Substitute(SUBSTITUTE(Substitute(IMPORTXML($E$1&A5; "//div[@class='priceValue ']/span");"$";"");",";"");".";","))

@bradleytm1718 2 года назад

Amazing dude, amazing explanation, amazing English, amazing tutorial = amazing video experience. Nailed it, officially subscribed

@nickyvictorious 2 года назад

Best!

@J-L-O 2 года назад

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 👍

@jiejenn 2 года назад

This is actually a question brought up to me a few time. Please refer to v2 video for the solutions. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-LC00b7TYVQc.html

@muhammadbaihaki4301 2 года назад

nice video. but some price are not appear as the show #N/A in the column like $1INCH, $TLM, $CRO and etc. Why?

@jiejenn 2 года назад

Why are you including a dollar symbol in the ticker name?

@dancefloor6225 Год назад

How about change percent ?

@gustavojuantorena 2 года назад

Great!

@jiejenn 2 года назад

Glad the video helped.

@maulanz3187 2 года назад

Thanks! How shall I get lastday price?

@jiejenn 2 года назад

I will be releasing a follow up video to cover that topic.

@chinmayakumarbehera4754 2 года назад

Hi, thank you for the tutorial but the sheets aren't getting updated

@jiejenn 2 года назад

I found out about this issue just recently. Here's an update video to cover a few of the limitations, including force re-calculation: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-LC00b7TYVQc.html

@chinmayakumarbehera4754 2 года назад

@@jiejenn Thank you

@XEROPOP Год назад

nice but i wanna se only currency value, not 24h Volume / Market Cap below the price. How fix tha?

@WoodyHertzogIII Год назад

I have the same problem. Did you figure this out?

@pinnaclefinancial533 5 месяцев назад

can I know how get the historical data into google sheets for crypto?

@jiejenn 5 месяцев назад

Let me look into it.

@9391862 Год назад

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.,

@scoopiddy 11 месяцев назад

Same issue. Can't get it to work in 2023

@ViktorUnginovic Год назад

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.

@XEROPOP Год назад

the same problem

@tpootai2 Год назад

Bro all you need is just to add 1 row below each of the existing rows you have. Problem solved for me.

@tpootai2 Год назад

@@XEROPOP add 1 row below each of the existing rows you have, then filter blank rows out or leave it as is

@ViktorUnginovic Год назад

@@tpootai2 ok, thanks, I'll try that But I have solved it another way

@PlentyPotentialOfficial Год назад

@@ViktorUnginovic How?

@Kashue_ 2 года назад

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

@jiejenn 2 года назад

I just tried, it is working for me. (=IMPORTXML("coinmarketcap.com/currencies/bitcoin", "//div[contains(@class,'priceValue')]"))

@Kashue_ 2 года назад

@@jiejenn even pasting your command it does not work, thanks anyway for your video, now i know it is possible to do this!

@Kashue_ 2 года назад

@@jiejenn changing the , with ; it works fine, thanks ^^

@patricevonparis Год назад

@@Kashue_ indeed changing to ; worked for me aswell thanks !

@nomadicperceptions 2 года назад

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!

@jiejenn 2 года назад

Google Sheets' backend is currently experiencing some issue, engineers are working on it.

@nomadicperceptions 2 года назад

@@jiejenn ok, thanks! 🙂

@NeurotechMX 2 года назад

Can someone share the text of the working formula, i typed it and get #error Formula parse error. Thanks

@MarkayCS 2 года назад

You have to change the , in the formula with ; This worked for me atleast.

@JayLeino 2 года назад

@@MarkayCS Thank You very much Markay :) I was wondering this same thing couple of hours last night and now everything works! Thanks! 👍🏻

@AliPizra 2 года назад

@@MarkayCS thanks!!!

@AksuA 2 года назад

Is it possible to get price value in euros? I could get it in Chinese Yuans, but that does not help me :)

@jiejenn 2 года назад

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.

@BeanieBroadcast 2 года назад

amazing tutorial - how do I change the price to a different currency like GBP?

@jiejenn 2 года назад

If you want to convert the prices to a different currency, you will first need to create a conversion table (ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-nMnYXR1NeLE.html) and then do a lookup to get the exchange rate and calculate the converted price.

@LagunaLeonhart Год назад

@@jiejenn video unavailable

@zweiche 2 года назад

Any chance to explain how to convert to number, so i can use on the calculations?

@jiejenn 2 года назад

The VALUE function can convert strings to numbers support.google.com/docs/answer/3094220?hl=en

@zweiche 2 года назад

@@jiejenn I am horrible with that :( all I need is remove Dollar sign from price column , so I can do math with price column :(

@jiejenn 2 года назад

@@zweiche support.google.com/docs/answer/3098247?hl=en

@Kazerilo 2 года назад

=Value(Substitute(SUBSTITUTE(Substitute(IMPORTXML($E$1&A5; "//div[@class='priceValue ']/span");"$";"");",";"");".";","))

@robinhieu Год назад

it's not work now, can you update the formula? please

@carvenyong9345 2 года назад

great video! But How can I add the market cap?

@jiejenn 2 года назад

Please refer to this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-LC00b7TYVQc.html for answers.

@SamOGT_3 Год назад

thx very much bu showm th e market cap 24h vilume how to hide

@WoodyHertzogIII Год назад

Same with me. DId you find a way to fix this?

@Basedegen 5 месяцев назад

not working anymore. is there an updated version?

@jiejenn 5 месяцев назад

I will look into it.

@denyssamsonov9369 4 месяца назад

If someone has issue like FORMULA PARSE ERROR , try to change separator to ; instead of ,

@nimantha0411 10 месяцев назад

I follow the same steps but keep gettin error called " impoted cotent is empty"

@ss2345hvj 9 месяцев назад

@naimudeennaimu2190 5 месяцев назад

@hrist7534 3 месяца назад

@caiomaiaguimaraes4416 2 месяца назад

@amarmohammed5644 2 года назад

anyone know how I can get the value in £ instead of $. Brilliant video!

@MATHIEUCHEVALIERweb 2 года назад

use the replace function

@souvikghosh5087 3 месяца назад

Error showing.Price N/A by this method.Give other ways as this is not working.

@YuriyGorbak 8 месяцев назад

Yes it is not working(

@brettshelite 2 года назад

This method quit working for me a few days ago. It has been working for months then suddenly stopped. Anyone else having issues?

@jiejenn 2 года назад

Google Sheets' backend is currently experiencing some issue, engineers are working on it.

@tyatya8019 2 года назад

//, we cannot parse this operator in this context

@jiejenn 2 года назад

Without looking at your file, I can only guess it is either 1) typo somewhere or 2) region setting is different from U.S.

@angelbaladjay1723 4 месяца назад

mine is imported content is empty

@jiejenn 4 месяца назад

Currently working on an alternative. Stay tuned.

@IbsenCaldas 2 года назад

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!

@jiejenn 2 года назад

What's the formula you use?

@BinhYT-uo8gj 2 года назад

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

@IbsenCaldas 2 года назад

@@BinhYT-uo8gj I looks like it worked here ! Thanks a lot! both of you guys... fully automated now \m/