본문 바로가기

컴퓨터 활용/구글스프레드시트 응용

구글스프레드시트에서 가상화폐 정보 가져오기

최근 암호화폐를 거래 하면서 수익률을 관리할 수 있도록 스프레드 시트를 만들고 있습니다. 
주식과 비슷하기에 해외주식 시트를 수정하여 만들면 될 것 같아 준비중에 있습니다. 
제일 문제점은 실시간으로 암호화폐의 가격 정보를 받아야 하는데  암호화폐의 경우 거래플랫폼에서 API를 제공하고 JSON 데이터 형태로 무료로 제공해주고 있어서 편하게 가져올 수 있었습니다.
코인겟코(Coingecko)라는 사이트 인데 www.coingecko.com/ko/api#explore-api 입니다. 

여기서 제공하는 데이터로 구글 앱 스크립트의 UrlFetchApp.fetch() 와 JSON.parse() 를 사용하여 분석하여 필요한 정보를 뽑아 내어서 구글 스프레드 시트의 셀에 찍어서 사용하려고 합니다

소스 구현은 허접하고 간단한 학습으로 누구나 만들 수 있습니다. 예외처리는 하지 않았습니다 

비트코인의 가격을 가져오는 함수는 =GetCoinGecKo( coin_id ,currency ,"current_price") 입니다.


첫 번째 파라미터는 코인겟코에서 제공하는 Id 이며 두 번째는 통화 세번째는 속성(attribute) 입니다. 
ID를 알기 위해서 첫번째 시트인 "coingecko암호화폐목록" 을 참고해야 합니다.

btc 으로 검색 하면 symbol 은 btc , coin_id 는 bitcoin 으로 검색 됩니다.   coin_id를 알아내는 것이 먼저 입니다. 
Currency 는 krw 로하면 BTC/KRW , usd로 하면 BTC/USD 가 되겠죠.

아래와 같이 사용하면 됩니다.  예시 시트에 모든 함수를 나열해 놓았습니다.

price 와 current_price 는 같은 기능인데 price 가 속도가 좀 더 빠를 겁니다.
price 는 api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=krw 를 이용하여 추출합니다. 
market_cap은 시가총액, market_cap_rank 시가총액 랭킹, total_volume 은 전체거래량, high_24h 24시간 최고가, low_24h는 24시간 최저가 입니다. 
나머지는 영어를 읽어보면 무엇을 뜻하는지 아실 수 있을 것입니다. 
API 메뉴얼을 읽어보면 다양한 정보를 제공합니다. 파라미터를 넣으면 실습할 수 있는 URL도 만들어 줍니다. 

Market_Chart 를 이용하여 구글 스프레드 시트의 SPARKLINE 을 이용하여 그래프를 그릴 수도 있습니다.

Market_Chart 시트에  일 단위 /시간 단위 가격을 표시 해주는 함수의 예시가 있습니다. 


=GetCoinGecKo("bitcoin","krw","market_chart","30")
=GetCoinGecKo("bitcoin","krw","market_chart")

세번째 파라미터는 일 수인데 30일 지정하면 오늘 부터 30일 전의 데이타만 보여 주고 생략하면 max 로 전체기간을 모두 보여 줍니다. 

=GetCoinGecKo("bitcoin","krw","price_range","2020-10-01","2020-10-09")
위와 같이 기간을 지정할 수 있도록 함수를 만들었는데 두 기간동안의 시간단위 데이터를 보여 주고 

다음과 같이 SPARKLINE 함수로 감싸면 그 셀에 바로 그래프를 그려줍니다 
=SPARKLINE(GetCoinGecKo("bitcoin","krw","price_range","2020-10-01","2020-10-09"))

 

 

구글 스프레드 시트 주소는 다음과 같습니다. 

docs.google.com/spreadsheets/d/1DeS9CehBB4ckSYfVRufJzjU8aAfyDsupUQgicUXaQlk/edit#gid=962115200

 

Google 스프레드시트 - 스프레드시트를 작성하고 수정할 수 있으며 무료입니다.

하나의 계정으로 모든 Google 서비스를 스프레드시트로 이동하려면 로그인하세요.

accounts.google.com

수정을 위해서는 파일메뉴의 사본 만들기에서 개인 구글드라이브에 복사를 하면 됩니다. 


전체 소스는 도구> 스크립트 편집기에 있습니다.

/* 
 * 코인의 속성에 따라 값을 얻는다
 * GetCoinGecKo(  coin_id , currency, attribute,[start_date|num_of_days], [end_date]) 
 */

function GetCoinGecKo(coin_id, currency, attribute, start_date_days, end_date)
{
 
  
  if(!coin_id) return "첫번째 매개변수 : coin_in가 필요합니다.";
  
  if(!attribute) return "attribute is required";
  if(!currency) currency = "usd";
  
  
  switch(attribute)
  {
    /* /v3/simple/price */
    
    case "price":
     
      if(!currency || (currency == coin_id) ){
        return "Currency is required";
      }
    
      var url = "https://api.coingecko.com/api/v3/simple/price?ids="+coin_id+"&vs_currencies="+currency+"&include_market_cap=true&include_24hr_vol=true&include_24hr_change=true&include_last_updated_at=true";
      var response = UrlFetchApp.fetch(url);
      var json = JSON.parse(response.getContentText());
      
    
    break;

    case "market_list": 
    case "current_price":
    case "market_cap":    
    case "market_cap_rank":    
    case "fully_diluted_valuation":    
    case "total_volume":    
    case "high_24h":    
    case "low_24h":    
    case "price_change_24h":    
    case "price_change_percentage_24h":    
    case "market_cap_change_24h":    
    case "market_cap_change_percentage_24h":    
    case "last_updated":    
    
      var url = "https://api.coingecko.com/api/v3/coins/markets?vs_currency="+currency+"&ids="+coin_id+"&order=market_cap_desc&per_page=100&page=1&price_change_percentage=1d";
      var response = UrlFetchApp.fetch(url);
      var json = JSON.parse(response.getContentText());

    
    break;
    

    case "market_chart":
    
      var days = start_date_days;
      
      if(!start_date_days) days= "max";
      
      var url = "https://api.coingecko.com/api/v3/coins/"+coin_id+"/market_chart?vs_currency="+currency+"&days="+days
      var response = UrlFetchApp.fetch(url);
      var json = JSON.parse(response.getContentText());
   
   break;
   
   case "price_range":
   
      
      //var from = (start_date_days - 25569)*86400; 
      //var to = (end_date - 25569)*86400;
      
        
        var from = FROM_STR_DATE(start_date_days);
        var to = FROM_STR_DATE(end_date);

      
      
      if(!(from && to )){ return "From , To is required as Excel Date";}

      
      var url = "https://api.coingecko.com/api/v3/coins/"+coin_id+"/market_chart/range?vs_currency="+currency+"&from="+from+"&to="+to;
      var response = UrlFetchApp.fetch(url);
      var json = JSON.parse(response.getContentText());
      
    break;
  }
  


  switch(attribute){
  
 
    case "price":
    
      return json[coin_id][currency];

    break;


    
    
    case "market_list":
    
      
        var arr = new Array();
              
        json.forEach(function(obj) {
           
         for( var a in obj){
              arr.push([ a, obj[a]]);
         }          
          
        });
        
        return arr;
      
    
    break;
    
    case "current_price":
    case "market_cap":    
    case "market_cap_rank":    
    case "fully_diluted_valuation":    
    case "total_volume":    
    case "high_24h":    
    case "low_24h":    
    case "price_change_24h":    
    case "price_change_percentage_24h":    
    case "market_cap_change_24h":    
    case "market_cap_change_percentage_24h":    
    case "last_updated":    
    
          return json[0][attribute];
          
    break;
    
   
    case "market_chart":
    case "price_range":
    
    
        var arr = new Array();
        arr.push(["date", "value"]);
      
        json["prices"].forEach(function(obj) {
             arr.push([ new Date(obj[0]),obj[1] ]);
        });

        return arr;
        
    break;
  }
}


구글 스프레드 시트 상에 바로 쓸 수 있는 플러그인이 있던데 cryptosheets.com
가입도 해야하고 인증절차도 복잡하더군요 일부 기능은 유료라서 수익률 관리하기 위하여 몇몇 정보만 필요하기에 사용하기 쉽게 정형화 하여 그냥 저만의 함수로 만들었습니다. 
다음은 위의 기능을 가지고 구해진 암호화폐 가격으로 수익률 관리하는 시트를 만들어서 사용해 보려고 합니다.