[How-To] Pull BTC-E Ticker API into a Google Spreadsheet - Apps Script code

[ny2cafuse]

So my partner and I have been documenting everything we can with our ever-evolving mining operation.  One of the things we do is keep track of our wallet transactions and balance in a Google Spreadsheet.  We're looking at how to automate the process, but that's another post all-together.  One problem we ran into is how to pull the latest price of LTC/USD from BTC-E and calculate our wallet worth.  So I did some searching and couldn't really come up with anything specific to Google Spreadsheets.  There could be something like this out there but I couldn't find it.  So I'm going to post it here if anyone is looking for this.

So to get this running, you need to create a new spreadsheet, or use an existing spreadsheet in Google Apps.  Click on "Tools" in the menu bar, then "Script Editor".  Once it opens, rename the script, and paste in the following code:

Code:

function onOpen() {
  
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var menuEntries = [ {name: "Refresh", functionName: "onOpen"} ];
        ss.addMenu("BTCE Ticker", menuEntries);
     var range = ss.getSheets()[0].getRange("A1:A1"); 
     var url = 'https://btc-e.com/api/2/ltc_usd/ticker';
     var content = UrlFetchApp.fetch(url).getContentText();

     SpreadsheetApp.setActiveRange(range); 
     SpreadsheetApp.getActiveRange().setValue(content);
  
};

Save the script and press the play button.  It will pull the API info set with the "var url" variable, so set it to whatever API ticker you want (LTC/USD, LTC/BTC, etc...).  The "var range" variable sets the active cell with the "getRange("XX:XX") option.  I'm not a pro at scripting, so this was the first quick and dirty way to set the active cell that I could find.  The "menuEntries" variable adds a menu at the top of the spreadsheet with a "Refresh" option listed under it.  The script is called when the spreadsheet is first opened, and whenever you hit the "Refresh" menu item.

From here you can use functions to separate out the data, but at least getting the data into the spreadsheet is done.  I may or may not post the functions to separate the data depending on when I get time to do so at work.  I'll reserve the first reply on this post for that info if I get the chance.

Let me know if this helped you out.  Happy hashing!

[ny2cafuse]

Reserved for future info.

[miaviator]

Well thank you for that!  Saved me a few hours at least.

I modified this to provide the Buy rate for BTC-LTC and Sell rate for BTC-USD (I track the sale value of LTC mining earnings as USD daily)

Code:

function onOpen() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Refresh", functionName: "onOpen"} ];
  ss.addMenu("BTCE Ticker", menuEntries);
  var range = ss.getSheets()[0].getRange("C2:C2"); 
  var url = 'https://btc-e.com/api/2/ltc_btc/ticker';
  var content = UrlFetchApp.fetch(url).getContentText();
  var ticker = Utilities.jsonParse(content);
  var price = ticker['ticker']['buy'];
     SpreadsheetApp.setActiveRange(range); 
     SpreadsheetApp.getActiveRange().setValue(price);
  
  var range = ss.getSheets()[0].getRange("E2:E2"); 
  var url = 'https://btc-e.com/api/2/btc_usd/ticker';
  var content = UrlFetchApp.fetch(url).getContentText();
  var ticker = Utilities.jsonParse(content);
  var price = ticker['ticker']['sell'];
     SpreadsheetApp.setActiveRange(range); 
     SpreadsheetApp.getActiveRange().setValue(price);
  
};

[ny2cafuse]

Well thank you for that!  Saved me a few hours at least.

I modified this to provide the Buy rate for BTC-LTC and Sell rate for BTC-USD (I track the sale value of LTC mining earnings as USD daily)

NICE!  I was sure someone would be looking for it... I know I was.  Thanks for the updated code as well.  Teamwork makes the dream work!

[3Dfilament]

I'm working on javascript to display the fiat conversion value of the FTC spot based upon BTC/USD and will work LTC/USD into that as a metric, and then all of this will be used to assign fiat value to khash/s in units of BTC, LTC, and FTC. I primarily mine, so I need to know which converts best in the exchanges, (what is most profitable to mine), for the khash/s/electricity used to mine it, and this is going to be useful as we see the current "Attack of the Coin Clones" in progress, i.e., CHNcoin mining debut, and FTC IPO exchange debut at btc-e.com

[AlienBoy]

Very nice!

Is this possible in Excel? I tried to use "get external data from web" but excel doesn't recognize .json data! Please shed some light on me...

I apologize because this is not related to google spreadsheet / reviving an old thread!

Thanks!

[ny2cafuse]

Very nice!

Is this possible in Excel? I tried to use "get external data from web" but excel doesn't recognize .json data! Please shed some light on me...

I apologize because this is not related to google spreadsheet / reviving an old thread!

Thanks!

Hey, mate.  No need for apologies, it's a good question.  I think with Excel, you could use get external data from web and just use the actual page to fetch the data from.  I think excel just looks at the webpage, breaks it into it's xpath data, and then uses that xpath data to import into the spreadsheet.

I'd have to verify, but I think that would be the way to go.  I'll test it at work and report back.

-Fuse

[AlienBoy]

Hey, mate.  No need for apologies, it's a good question.  I think with Excel, you could use get external data from web and just use the actual page to fetch the data from.  I think excel just looks at the webpage, breaks it into it's xpath data, and then uses that xpath data to import into the spreadsheet.

I'd have to verify, but I think that would be the way to go.  I'll test it at work and report back.

-Fuse

Thanks for the response, Fuse!

I'm currently using the method you mentioned, so I can confirm it will work! But the problem with this method is it doesn't work well with many exchange web pages which will update itself dynamically. I haven't tried it with BTC-e - and I doubt it will work, but it can't read the page data normally on bitcoinaverage.com and cryptonator.com (main page only, the "all rates" page works fine).

Just thinking that if Excel can achieve the same with Google spreadsheet, then it will be very useful since many cryptocoin exchanges/sites usually support various API info in .json format!

[palm12341]

If you want a method without using scripts, you can just paste the following function into a cell
=LEFT((LEFT(RIGHT(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),LEN(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"))-(SEARCH("last",IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),"1")+5)),6)),SEARCH(",",(LEFT(RIGHT(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),LEN(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"))-(SEARCH("last",IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),"1")+5)),14)),"1")-1)

Only thing is it doesn't have auto-refresh.

If you want to display the time when it was most recently updated, you can use the function below. Just make sure to set the cell to the date/time format of your choice using the number menu under format
=((LEFT(RIGHT(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),LEN(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"))-(SEARCH("updated",IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),"1")+8)),"10"))/(60*60*24)+"1/1/1970")-(8/24)

[AlienBoy]

If you want a method without using scripts, you can just paste the following function into a cell
=LEFT((LEFT(RIGHT(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),LEN(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"))-(SEARCH("last",IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),"1")+5)),6)),SEARCH(",",(LEFT(RIGHT(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),LEN(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"))-(SEARCH("last",IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),"1")+5)),14)),"1")-1)

Only thing is it doesn't have auto-refresh.

If you want to display the time when it was most recently updated, you can use the function below. Just make sure to set the cell to the date/time format of your choice using the number menu under format
=((LEFT(RIGHT(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),LEN(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"))-(SEARCH("updated",IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),"1")+8)),"10"))/(60*60*24)+"1/1/1970")-(8/24)

Thanks palm12341! But I think the function you gave me is based on Google spreadsheet, I'm looking for a solution for Excel...

Anyway, I found the ticker URL you provided can actually fetched by Excel! It just display text data on web page instead of returning .json file, so by combining the function you provided, I successfully got the data I need! Thanks to you!

Question: how you found the specific text-based BTC-e ticker URL? I couldn't find it through its homepage!

[kevin1234a]

If you want a method without using scripts, you can just paste the following function into a cell
=LEFT((LEFT(RIGHT(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),LEN(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"))-(SEARCH("last",IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),"1")+5)),6)),SEARCH(",",(LEFT(RIGHT(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),LEN(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"))-(SEARCH("last",IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),"1")+5)),14)),"1")-1)

Only thing is it doesn't have auto-refresh.

If you want to display the time when it was most recently updated, you can use the function below. Just make sure to set the cell to the date/time format of your choice using the number menu under format
=((LEFT(RIGHT(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),LEN(IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"))-(SEARCH("updated",IMPORTDATA("https://btc-e.com/api/2/btc_usd/ticker"),"1")+8)),"10"))/(60*60*24)+"1/1/1970")-(8/24)

Thanks palm12341! But I think the function you gave me is based on Google spreadsheet, I'm looking for a solution for Excel...

Anyway, I found the ticker URL you provided can actually fetched by Excel! It just display text data on web page instead of returning .json file, so by combining the function you provided, I successfully got the data I need! Thanks to you!

Question: how you found the specific text-based BTC-e ticker URL? I couldn't find it through its homepage!

currently im working on this project, would you provide me some info how to call cryptonator exchange rates for any coin in excel

[smrp]

From here you can use functions to separate out the data, but at least getting the data into the spreadsheet is done.  I may or may not post the functions to separate the data depending on when I get time to do so at work.  I'll reserve the first reply on this post for that info if I get the chance.

Let me know if this helped you out.  Happy hashing!

Ok, so first I would like to say thanks for the script, and second I would like to ask if you can help me with this functions to separate out the data. I been trying with no success... (I'm trying to import LTC to USD value, I got the data but I don't know how to separate it.)


Sorry to bother you with this, and again thank you!