구글 스프레드시트에서 chatGPT로 재무재표 분석하기

얼마전 chat GPT를 이용해서 AST Spacemobile 이라는 작은 스타트업의 재무재표를 분석한 적이 있습니다. 표 정보를 prompt 창에 입력하는 것이 다소 불편했는데요. 이번에는 Google 스프레드시트에 재무 정보를 읽어서, chatGPT에 분석을 요청해 보았습니다. 

 

https://smart-worker.tistory.com/37

Chat GPT로 미국 스타트업 재무재표 분석해보기

 

 

---

재무 정보 읽어서, ChatGPT API로 가치 분석해보기.

이번 포스팅에서는 구글 스프레드시트로 재무 정보를 읽고 chatgpt api로 호출하는 것까지 해보겠습니다.
지난 번에는 GPT-4를 이용해 분석했고, 이번에는 gpt 3.5-0301 버전으로 작업했습니다. 아래는 관련 정보 입니다.

 

• 실험 모델 : gpt-3.5-turbo-0301
• 재무 데이터 : yahoo finance의 3개년 데이터
• 분석 회사 : AT&T

 

---

Yahoo Finance에서 재무 정보 긁어오기

구글 스프레드시트에는 ImportHTML 함수로 크롤링해서 가져올 수 있습니다. 
저는 게으름(?)으로 아래 주소에서 손으로 복사하여 개별 시트에 붙여 넣었습니다.

 

ImportHTML이용방법은 아래 글을 참고하십시오.

https://smart-worker.tistory.com/15

[구글 스프레드시트] IMPORTHTML로 yahoo finance에서 배당금 정보 조회하기

 

Yahoo Finance 에서 아래 Income Statement, Balance Sheet, Cash Flow를 구글 스프레드 시트에 복사했습니다. 

 

 

상세 주소는 아래와 같습니다. 

https://finance.yahoo.com/quote/T/financials?p=T 

 

Balance Sheet, Income Statement, Cash Flow를 개별 시트로 만들어서 복사합니다.
Yahoo에서 제공하는 5개년 치 정보를 모두 사용하면,
GPT3.5의 최대 토큰 길이를 초과하여 두 개 칼럼은 날려버리고 최근 3개년 정보만 사용했습니다.

 

---

 

Prompt , 결과 창 만들기

사용할 Prompt와 각 종 회사 정보를 입력할 셀을 지정합니다. 
그리고 개인 API KEY를 사용할 수 있도록, API_KEY 입력창도 추가합니다. 

 

아래는 제가 사용한 재무 정보 분석 명령어 입니다. 

As a disciple of Warren Buffet's value investing philosophy, you're tasked with analyzing an income statement, balance sheet, and cash flow of a company. Based on these financial documents, could you provide a comprehensive analysis of the company's financial health and its potential as a valuable investment?

 

Context 하위에 있는 정보는, 분석 회사의 기본 정보입니다. 이것도 역시 Yahoo Finance의 정보로 갈음합니다. 

https://finance.yahoo.com/quote/T?p=T

---

 

재무 정보를 Text로 변경해서, Prompt로 만들기

 

아래는 개별 시트에서 표를 읽어서 Tab으로 분리된 text 로 변환하는 코드입니다. 
저희는 최종 Prompt 생성 시, Balance Sheet/Income Statement/Cash Flow에서 읽은 표를 합칠 것입니다.

function convertDataToText(data) {
  var text = data.map(
                      function(row) {
                            return row.join('\t'); // or ',' for CSV style
                      }).join('\n');

  return text;
}

// 3. 특정 시트의 표를 읽어서 TSV 형태로 변환
function getSpreadsheetDataAsText(sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var values = range.getValues();

  return convertDataToText(values);
}

 

아래는 최종 분석용 prompt를 호출하는 함수 입니다.

function buildAnalyzingPrompt(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Interface');

  // 1) Get Instruction 
  var instruction = sheet.getRange('A8').getValue();

  // 2) Get Information of Company
  var context_info = sheet.getRange('A16').getValue();

  // 3) Get Financial Statement
  var balance = getSpreadsheetDataAsText('Balance Sheet');
  var income = getSpreadsheetDataAsText('Income Statement');
  var cashflow = getSpreadsheetDataAsText('Cash Flow');

  // 4) join all infomation
  prompt = instruction + '\n## BELOW ##\n\n### COMPANY PROFILE### \n' 
           + context_info + '\n\n ### BALANCE SHEET ### \n'
           + balance + '\n\n ### INCOME STATEMENT ### \n'
           + income + '\n\n ### CASH FLOW ### \n'
           + cashflow;

 return prompt;
}

대략 최종 프롬프트의 형태는 아래와 같습니다.

 

As a disciple of Warren Buffet's value investing philosophy, you're tasked with analyzing an income statement, balance sheet, and cash flow of a company. Based on these financial documents, could you provide a comprehensive analysis of the company's financial health and its potential as a valuable investment?

## BELOW ##
### COMPANY PROFILE ###
첫번째 시트에 입력한 컨텍스트 정보

### BALANCE SHEET ###
12/30/2022 12/30/2021 12/30/2020
Total Assets 402853000 551622000 525761000
Current Assets 33108000 59997000 52008000
Cash,Cash Equivalents & Short Term Investments 3701000 21169000 9740000
.... 

 

---

이제 합쳐서 GPT API를 호출하자..

 

최종 함수에서 해당 prompt로 gpt api를 호출합니다. 
단,,, 우리는 영어를 못하기 때문에,,
결과를 번역하기 위해서 buildTranslatePrompt를 만들고 gpt api 를 한 번 더 호출합니다.  

//3. 결과를 한국어로 번역. 
function buildTranslatePrompt(resp){
  var prompt = 'Translate the below in Korean';

  prompt = prompt + '\n\n ### BELOW ###' + resp;

  return prompt
}

// 4. 1~2의 결과를 A7 셀에 복사하는 코드
function analyzeSpreadsheetDataWithGpt() {
  // 0) api key를 얻습니다. 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Interface');
  var api_key = sheet.getRange('B2').getValue();

  // 1) 시트에 정보를 조합하여 첫번째 질의할 분석용 prompt를 생성합니다.
  propmt = buildAnalyzingPrompt();

  if (propmt) {
    // 2) 분석용 API 를 호출합니다.
    var response = callChatGptApi(prompt, api_key);

    // 3) 한글로 번역합니다. 다른 API 사용해도 무방... 
    var tranlateprompt = buildTranslatePrompt(response);
    var tranlatedResponse = callChatGptApi(tranlateprompt, api_key);

   // 4) 최종 응답을 'Interface'의 A30 셀에 씁니다.
    var outputCell = sheet.getRange('A30');
    outputCell.setValue(tranlatedResponse);
  }

openai chatgpt api는 분당 3회까지 호출가능합니다.
1회 분석에 2번 호출하기 때문에, 만약 연속해서 분석 요청을 하면 rate limit로 오류가 날 수 있어요.

 

 https://api.openai.com에 대한 요청이 실패하여 코드 429이(가) 반환되었습니다.
...
"Rate limit reached for default-gpt-3.5-turbo in organization org-z5U7... on requests per min....

 

구글 스프레드시트에서 ChatGPT API호출 방법은, 이전 글을 참고하세요~

https://smart-worker.tistory.com/40

구글 스프레드시트에서 chatGPT API 사용하기

 

 

이제 버튼에 함수 연결해서 실행해 봅시다. 

 

이제 "재무정보 분석하기" 버튼에 최종 함수인 analyzeSpreadsheetDataWithGpt 연결해서 클릭해보겠습니다.
OpenAI는, 여튼 굉장히 느립니다. 응답이 오기까지 1~2분은 소요되는 느낌입니다. 

 

 

최종적으로 아래와 같이 답변을 주는 군요.. 오래된 통신사 분석으로 잘 맞는 것 같기도 합니다.

• 재무 상태가 우수하나 운전자본은 부정적..
• 수익은 꾸준히 감소. 미래 성장은 부정적.
• 하지만 양호한 현금 흐름... 
• 결론적으로, 가치투자 관점에서 덜 매력적.

 

AT&T Inc.의 재무 문서 분석 결과, 회사는 부도 위험이 있을 수 있는 부정적인 운전 자본을 가지고 있는 것으로 나타났습니다. 하지만, 회사는 총 자산이 많고 자본화에 비해 상대적으로 적은 부채를 가지고 있어 재무 건강 상의 강점을 보일 수 있습니다.

회사의 수익은 최근 몇 년간 꾸준히 감소하고 있어, 미래 성장 전망에 대한 우려를 불러일으킬 수 있습니다. 또한, 회사는 자본자산 가치상실과 기타 특이 비용으로 인해 상당한 부담을 갖고 있어, 미래 수익에 영향을 미칠 수 있습니다.

하지만, AT&T Inc.는 양호한 현금 흐름을 보여주고 있으며, 이는 회사가 자본 지출과 배당을 충당하기에 충분한 현금을 생성한다는 것을 나타냅니다. 그러나, 회사의 자유 현금 흐름은 최근 1년간 상당히 감소하였기 때문에 원인을 파악하기 위해 추가적인 분석이 필요할 수 있습니다.

전반적으로, AT&T Inc.는 강화된 재무 상황을 가지고 있지만, 수익 감소와 특이 비용으로 인해 워렌 버핏과 같은 가치 투자자들에게는 덜 매력적일 수 있습니다. 투자 여부를 결정하기 전에 이러한 재무 지표들의 원인들을 더 자세히 조사할 필요가 있습니다.

 

지난 번 GPT-4의 분석 결과와 비교해서, 결과가 썩 만족스럽지는 않습니다. 

 

 

 

마무리

이상 구글 스프레드시트에서 재무정보 분석하기를 해보았습니다. 

 

아래는 작성 시트와 전체 코드입니다.

 

https://docs.google.com/spreadsheets/d/1nrRA0DCp-6MgYgfpXdYZKpe4dao5Cy1oPleHlhEOx9E/edit?usp=sharing 

Google SpreadSheet에서 chatGPT로 재무재표 분석하기

 

 

// 1. Chat GPT API 호출 및 응답 출력
function callChatGptApi(prompt,
                        api_key,
                        ass_msg = "You are a helpful assistant.",
                        model="gpt-3.5-turbo-0301") {

  var url = "https://api.openai.com/v1/chat/completions";
  var headers = {
    "Authorization": "Bearer " + api_key,
    "Content-Type": "application/json"
  };

  Logger.log(headers);

  var payload = {
    "model" : model,
    "messages": [{"role":"system", "content":ass_msg},
                {"role":"user", "content":prompt}
                ],
    "temperature": 1
  };

  Logger.log(payload);

  var options = {
    "method" : "post",
    "headers": headers,
    "payload" : JSON.stringify(payload)
  };

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());
  
  return data.choices[0].message.content;

}



function convertDataToText(data) {
  var text = data.map(
                      function(row) {
                            return row.join('\t'); // or ',' for CSV style
                      }).join('\n');

  return text;
}

// 3. 특정 시트의 표를 읽어서 TSV 형태로 변환
function getSpreadsheetDataAsText(sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var values = range.getValues();

  return convertDataToText(values);
}


function buildAnalyzingPrompt(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Interface');

  // 1) Get Instruction 
  var instruction = sheet.getRange('A8').getValue();

  // 2) Get Information of Company
  var context_info = sheet.getRange('A16').getValue();

  // 3) Get Financial Statement
  var balance = getSpreadsheetDataAsText('Balance Sheet');
  var income = getSpreadsheetDataAsText('Income Statement');
  var cashflow = getSpreadsheetDataAsText('Cash Flow');

  // 4) join all infomation
  prompt = instruction + '\n## BELOW ##\n\n### COMPANY PROFILE### \n' 
           + context_info + '\n\n ### BALANCE SHEET ### \n'
           + balance + '\n\n ### INCOME STATEMENT ### \n'
           + income + '\n\n ### CASH FLOW ### \n'
           + cashflow;

 Logger.log(prompt);

 return prompt;
}

function buildTranslatePrompt(resp){
  var prompt = 'Translate the below in Korean';

  prompt = prompt + '\n\n ### BELOW ###' + resp;

  return prompt
}


// 4. 1~2의 결과를 A7 셀에 복사하는 코드
function analyzeSpreadsheetDataWithGpt() {
  // 0) api key를 얻습니다. 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Interface');
  var api_key = sheet.getRange('B2').getValue();

  // 1) 시트에 정보를 조합하여 첫번째 질의할 분석용 prompt를 생성합니다.
  propmt = buildAnalyzingPrompt();

  if (propmt) {

    // 2) 분석용 API 를 호출합니다.
    var response = callChatGptApi(prompt, api_key);

    Logger.log("chatGPT Response -----------");
    Logger.log(response);

    // 3) 한글로 번역합니다. 다른 API 사용해도 무방... 
    var tranlateprompt = buildTranslatePrompt(response);
    var tranlatedResponse = callChatGptApi(tranlateprompt, api_key);

   // 응답을 'Interface'의 A30 셀에 씁니다.
    var outputCell = sheet.getRange('A30');
    outputCell.setValue(tranlatedResponse);
  }
}