Forum Discussion

Sheralyn G's avatar
Sheralyn G
Copper Contributor
Nov 15, 2017

Excel Mac - get external data from API - how to get only the data I want, and not extra stuff

I have a txt file (query1.txt) saved in my Excel Queries folder that contains the following text (it's displaying as a link in this forum, but in my plain txt file it's just the text):

https://api.quadrigacx.com/v2/ticker?book=btc_cad

 

 

 

(It's followed by a few line breaks since if I don't do that, Excel can't read the file.)

 

Then I go to my workbook in Excel (for Mac), select cell A1, then go to Data > Get External Data > Run Saved Query and select query1.txt.

 

However, the data that it then displays in cell A1 looks like this:

 

{"high":"9370.00","last":"9329.88","timestamp":"1510758340","volume":"434.66143942","vwap":"8737.12428249","low":"8439.98","ask":"9349.49","bid":"9266.01"}

However, the only piece of that I want to display in the cell A1 is the current value for "last", 9329.88.

Is there a way to get Excel (for Mac) to only display the value I want, or am I stuck having to manually copy and paste that figure from the long string of data it's grabbing?

I don't know how to code, but I can follow directions :) If anyone can tell me how to make Excel pull out the data for only the "last" (i.e. 9329.88 in this example), that would be awesome!

Thanks in advance to all who try to help me :)

  • Kris Dockx's avatar
    Kris Dockx
    Copper Contributor
    Well, if that data is displayed in cell A1, you can display the data you need (9329.88) by going to the cell where you want to place the data, then using this formula: =left(A1;27;7). Try that and let me know if it worked. It should!

Resources