+ Reply to Thread
Results 1 to 9 of 9

Pulling Crypto Prices but come as text so error when multiplying them

  1. #1
    Registered User
    Join Date
    02-13-2022
    Location
    England
    MS-Off Ver
    Microsoft 365 apps for enterprise Excel v.2201
    Posts
    4

    Pulling Crypto Prices but come as text so error when multiplying them

    I have pulled data from coingecko.com using the data ribbon and the prices come as text. When i click convert to number or currency they all just change to an error. I also tried doing this text to column trick i saw on youtube which also doesn't work. This means that I get an error when I try to multiply the crypto price by my amount of coins for my portfolio. Is there any way to fix this? I need to keep it in a way that i can refresh it so that the price will update with the market.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pulling Crypto Prices but come as text so error when multiplying them

    With Power Query, I was able to transform the data.

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

    File attached
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Pulling Crypto Prices but come as text so error when multiplying them

    Administrative Note:

    Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    02-13-2022
    Location
    England
    MS-Off Ver
    Microsoft 365 apps for enterprise Excel v.2201
    Posts
    4

    Re: Pulling Crypto Prices but come as text so error when multiplying them

    Hi, thank you for the reply, I followed the video and pasted the code. However, as soon as I do it still comes up with an error.
    DataFormat.Error: We couldn't convert to Number.
    Details:
    $2,908.43

    There are 100 different cryptos on there so maybe there are too many? I only really need the one
    Last edited by Ryan0202; 02-14-2022 at 07:08 AM.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pulling Crypto Prices but come as text so error when multiplying them

    Have you looked at the file I attached. The code was generated from accessing the web site. Look at the steps taken in the file and see if you can replicate them.

  6. #6
    Registered User
    Join Date
    02-13-2022
    Location
    England
    MS-Off Ver
    Microsoft 365 apps for enterprise Excel v.2201
    Posts
    4

    Re: Pulling Crypto Prices but come as text so error when multiplying them

    Hi that file is great thank you, I will use that as my main one now as I dont know how to look at how you did it :D. However, do you know if theres a way to keep more decimal places in there? as for CRO there is 3 D.P in the file but there is 5 on coingecko. Thank you again

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pulling Crypto Prices but come as text so error when multiplying them

    That is how the data comes over from the web site. Cannot change the number of decimal places.

  8. #8
    Registered User
    Join Date
    02-13-2022
    Location
    England
    MS-Off Ver
    Microsoft 365 apps for enterprise Excel v.2201
    Posts
    4

    Re: Pulling Crypto Prices but come as text so error when multiplying them

    ok fair enough. I now have another question :'D. I have created another sheet on the same file as the crypto prices. I then did '=' in a cell and then went to the crypto sheet and just clicked the CRO price. However, if CRO's market cap goes up or down it could move up or down a spot which would mean a different price would be transferred onto the other sheet. I tried to hide all the other prices and just show CRO but when I refreshed the prices they all cam back again. Is there a way to sort the list by my own means rather than by market cap or will it always default to that when I refresh?

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pulling Crypto Prices but come as text so error when multiplying them

    If I am understanding correctly you may wish to create a parameter query. Look at this youtube on the basics of a parameter query. You should be able to adapt for your own needs.

    https://www.youtube.com/watch?v=gK2yBpiITvI

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. crypto monthly not working
    By hendrikbez in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-18-2021, 12:28 PM
  2. [SOLVED] Error While Multiplying Ratios With Numbers
    By zanshin777 in forum Excel General
    Replies: 9
    Last Post: 11-09-2018, 11:58 AM
  3. Coinbase Pro (crypto exchange) How to get live prices in Excel ?
    By mamba76 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-31-2018, 07:47 PM
  4. Pulling part #'s from sheets that have no prices
    By JDanW in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-01-2018, 12:44 PM
  5. A macro for crypto!!!
    By erguntmzkn in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 05-14-2013, 06:17 AM
  6. [SOLVED] If function when multiplying by a none-value cell (VALUE! error)
    By Leif Magnus in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-04-2012, 02:17 PM
  7. VBA Crypto solving
    By spacker15 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-21-2012, 05:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1