Journal Entry Test | 필수적 절차 수행 | Bash | Shell Script | Terminal | Large Data | 메모리 용량을 초과하는 전표 데이터 완전성 검증

Journal Entry Test 개요 (전표분석 시사) 에서 회계감사에서 수행하는 전표분석의 기본 개념에 대해 다루고 R | Journal Entry Test | Fast track 에서는 R lang을 사용하여 프로그래밍 언어로 간단하게¹ Journal Entry Test 하는 것에 대해 Posting 했었다.

여기서는 JE Tester by Joy 사용 설명서 에 포함한 Journal Entry Test 완전성 검증 및 상대계정분석을 Bash Shell Command Line에서 해결하는 방법을 기록하고자 한다.

메모리 문제

R 프로그램 언어 또는 Python이나 Julia 등의 데이터 분석에 용이한 언어들은 복잡하고 어려운 분석을 함수형 프로그래밍을 통해 아주 쉽게 컴퓨터가 알아서 다 해주며 분석 속도 역시 놀랍게 빠르다.

그러나 그러기 위한 전제 조건으로 전체 데이터셋이 메모리에 한번에 올라가서 메모리 여유가 많은 상황이라는 전제가 필요하다.²

메모리에 한번에 올려두고 데이터를 씹고 뜯고 맛보고 즐기는 상황은 매우 편리하고 R이나 Python 등에 이미 캡슐화 되어 만들어져 있는 수 많은 데이터 분석 함수를 도구로 활용하면 엑셀 등의 스프레드시트가 담을 수 있는 행보다 큰 데이터를 다룰 수도 있고 놀랍게 빠른 처리 속도에 감탄할 수 있다.

다만 컴퓨터가 가진 메모리 용량을 초과하는 크기의 데이터를 다루어야 한다면 R 등의 프로그램이 데이터를 열심히 올리다가 뻗어버려 빙빙 도는 모습을 마주할 수도 있다.

그 때는 R에 대해 실망할 때가 아니라 R의 readr 패키지에 갇혀 있지 말고 data.table로 더 큰 데이터를 빠르게 다루는 패키지를 사용해 보거나 Duckdb나 Arrow 같은 대용량 빅데이터를 처리하는데 특화된 패키지를 찾아 사용하는 것이 좋은 방법이 된다.²

Journal Entry Test를 하기 위하여 어떤 Tool(Program)을 활용하여야 할까?

전표 데이터의 크기에 따라서 적절한 Tool을 사용할 수 있겠다.

작은 데이터라면 단지 엑셀 하나만 잘 활용하더라도 전표 데이터의 완전성³을 검증하고 시나리오 테스트를 하는데 아무 무리가 없다.

그러나 엑셀은 1백만행 가량의 데이터를 담을 수 있으며 담겨졌다고 하더라도 많은 행을 담게 되면 심하게 버벅거리는 모습을 보기가 쉽다.

따라서 R 같은 프로그래밍 언어를 사용하면 좋은데 R은 1백만행이 한참 넘어도 버벅거리지 않고 잘 수행한다. 그렇지만 R도 메모리를 풍족하게 사용하는 프로그램인지라 16기가 메모리를 가진 노트북에서 5기가 정도의 데이터를 메모리에 한번에 올리고 분석하면 일시적으로 14기가 이상의 메모리를 소모하고자 하는 순간이 발생할 수 있는데 16기가의 메모리는 온전히 데이터 분석에 전부 사용할 수 없고 운영체제도 돌리고 웹브라우져나 Rstudio 등의 편집기 프로그램도 돌려야 하므로 이런 상황은 노트북 컴퓨터가 메모리 처리를 제대로 하지 못하고 뻗어버리는 현상을 초래한다.

본인이 Data 분석가라면 Duckdb 와 Arrow를 열심히 배워 사용하는 것이 맞지만 Data 분석가는 아닌데 전표 데이터 분석은 해야 하고 사용 가능한 대용량 메모리를 가진 서버가 있어서 FileZilla로 서버에 파일을 옮겨서 분석하면 기존의 방법을 그대로 활용하면서 분석을 마무리 할 수 있겠지만 그런 것은 없고 가진 것은 16기가 노트북인데 10기가를 초과하는 데이터의 완전성 분석을 하고자 한다면 어떻게 해야 할까?

여기에 있어 Simple한 Command Line 프로그램을 대안으로 생각해 내어 제시하고자 한다.

Bash Shell Terminal

Bash Terminal의 명령어만 사용하여 Journal Entry Test를 하기 위한 기초적인 전처리 과정을 해결 할 수가 있다. 전체 데이터가 너무 커서 엑셀이나 R로 다루는데 어려움이 있는 상황일 뿐이고 Journal Entry Test라는 것이 결국은 전표에 대해 그룹에 대해 부분합을 내고 그 부분합에 문제가 없는지 확인하는 수준이라면 부분합을 내어 작은 데이터로 요약하고 요약한 값을 엑셀 등의 다른 작은 데이터를 다루기 위하여 사용하는 익숙한 툴에서 붙이고 빼서 차이가 없음을 검증하는 과정이 된다면 어떨까?

Test 해 보니 Bash Terminal은 그렇게 메모리를 한번에 많이 소모하지 않으면서도 큰 데이터를 차근 차근 합해서 Summarise Data를 쓸 수가 있는 것 같다. 일관된 열을 가진 큰 데이터에 단순한 몇 가지 작업을 하는데에 있어 어찌 보면 아주 Powerful한 방법인 듯 하다.

Bash Shell 프로그램 다운로드 및 환경설정

Bash Shell Terminal은 어떻게 사용할 수 있을까? Bash Shell은 우분투 리눅스를 운영체제로써 컴퓨터를 구동하면 기본적으로 설치 되어 있는 Terminal 환경이다. 다만, 우분투 리눅스를 직접적으로 사용하는 사람은 서버를 작동하는 개발자이거나 어떤 이유로든 리눅스에 관심이 많은 사람이 아니고서야 잘 찾아보기 어렵다.

Shell을 사용하면 가장 가벼운 프로그램을 켜서 큰 파일에 직접 붙어서 작업 할 수 있다. Data 형태가 일반적인 SAP 등의 ERP DB에서 추출한 텍스트 파일이라고 생각하면 최소한 Journal Entry Test 정도는 Shell에서 충분히 간단하게 해결할 수 있을 것 같다. 윈도우즈에서도 Bash Shell을 사용할 수 있는 방법이 있다.

대부분의 많은 사람들이 사용하는 운영체제 윈도우즈에서 다음과 같은 방법을 통해 Bash Shell을 구동할 수가 있다.

1. WSL2

WSL은 https://learn.microsoft.com/ko-kr/windows/wsl/install 윈도우즈에서 앱을 구동하듯이 리눅스를 구동하는 환경이다. 윈도우즈10보다 윈도우즈11에서 더 사용하기 편해졌다.⁴

2. Git Bash

Git https://git-scm.com/

Git을 설치하면 Git Bash가 같이 설치된다. 이 Shell을 활용하면 윈도우즈 환경에서 CMD나 PowerShell을 사용하듯이 Bash Shell을 사용할 수가 있다. 윈도우즈에서 리눅스 터미널을 사용하듯이 리눅스 명령어를 상당 부분 사용할 수가 있으므로 윈도우즈에서 Bash Shell을 사용하기에 좋은 Tool인듯 하다.

Bash Shell Command 기초

어떤 명령어들을 조합하여 Journal Entry Test를 할 수 있는지에 초점을 두어 핵심적인 명령어들만 알아보고자 한다. 이 명령어들만 알거나 복사해서 사용할 수만 있어도 엄청나게 큰 파일에 대한 Journal Entry Test에 무리가 없을 것으로 생각한다.⁵

1. 파일 인코딩(encoding) 변환

아주 큰 파일을 다루는 것으로 가정하였으므로 이미 파일을 받기 전에 파일의 인코딩에 대하여 utf-8로 요청하였다면 발생하지 않을 문제이지만 만약 윈도우즈 환경에서 쓰여진 Text file이라고 하면 encoding 형식이 euc-kr 이라는 이름(또는 cp949)일 것이며 리눅스 환경에서 이 파일에 접근한다면 한글이 전부 깨져버리게 된다.⁶

그러므로, Text로 이루어진 파일에는 encoding을 확인하고 필요하다면 utf-8로 변환하는 과정이 필요할 수도 있다. 큰 데이터셋의 encoding을 변환하는 것은 시간이 상당히 필요할 수도 있다. 메모장을 통해서 열어 encoding을 변환해서 저장하는 것도 방법일 수 있으나 어떤 다른 Tool을 사용하여 작업하는 것 보다 Bash Shell에서 직접 이러한 작업을 할 수도 있으며 무슨 앱을 열고 닫고 할 필요 없고 가장 가볍고 원초적으로 처리할 수 있는 방법이므로 Shell에서 직접 해결하고자 한다.

• 파일의 encoding 확인

filename은 실제 확인하고자 하는 Text 파일의 이름(탭으로 분리된 텍스트 파일 .tsv 확장자를 가정).

  
  file -bi filename.tsv
  

• 파일의 encoding 변환

filename은 원본 파일 이름, newfile은 encoding이 변경된 새로운 파일 생성 이름.

  
  iconv -c -f euc-kr -t utf-8 filename.tsv > newfile.tsv
  

2. 여러개로 나누어진 파일 합해서 하나로 만들기

DB에서 하나의 파일로 내려 받아서 전달한다면 한 개의 파일 용량이 너무 커서 온라인을 통한 전송이 어렵거나 전송 도중에 연결이 끊어지는 등의 사유로 실패한 경우 실패한 이후를 받아서 마무리 하기도 어려울 수 있으며 내려 받는 과정에서도 너무 큰 파일 하나를 만드는 것보다 기간을 기준으로 여러개로 쪼개어 생성하는 것이 용이할 수 있는 경우가 많으므로 큰 Journal Entry Data를 내려 받아 전달할 때 12개월을 반으로 쪼개 24개의 파일로 전달하는 경우가 있을 수 있다. 이럴 때 Bash Shell에서 간단하게 명령어 하나로 하나의 텍스트 파일로 합할 수 있다.

하나의 디렉토리에 24개의 .txt 확장자의 파일을 넣고 명령어를 쳐서 combined_file.txt 하나의 파일을 생성하는 것은 다음의 간단한 명령어로 결과를 얻을 수 있다.

  
  cat *.txt > combined_file.txt
  

3. 가장 윗 행에 변수명을 넣기

만약 탭으로 구분된 텍스트 파일을 합하고 보니 가장 윗 행에 각 열의 이름을 나타내는 열 변수명이 들어 있지 않고 내용만 들어 있는데 이 파일이 메모리에 올려서 R에서 분석이 가능할 것 같다고 하면 다음과 같이 가장 윗 행에 변수명을 넣어 줄 수가 있다.

탭으로 구분된 행을 추가하여야 R이 동일한 구분자에 대하여 열(column)로 구분된 것을 인지할 수 있으므로 \t로 명확하게 탭을 표현해 주어야 한다.

  
  sed -i '1i\Column1\tColumn2\tColumn3' 파일이름
  

4. 여러개의 파일에 같은 변수명을 반복하여 동일하게 넣기

24개로 나누어졌다던지 하는 등 여러개의 파일로 나누어져 있는데 변수명은 없고 전표 내용만 들어 있는 경우에 변수명을 Shell에서 넣은 다음 R에서 열 이름 변수명이 있는 Data로써 읽어들일 수가 있다.

  
  for file in *.txt; do
  
      echo '새로운컬럼' | cat - "$file" > temp && mv temp "$file"
  
  done
  

5. 이렇게 만든 파일의 가장 윗 행 변수명을 삭제하고자 한다면

각 텍스트 파일의 가장 윗 행을 쓸 수도 있으므로 삭제할 수도 있다.

  
  sed -i '1d' data.tsv
  

6. 데이터 상단의 10개 행을 출력하여 첫번째 행에 변수명이 있는지 없는지 확인

예를 들어, “example.txt” 파일의 가장 상단의 10개 행을 출력하려면 다음과 같이 명령한다.

  
  head example.txt
  

tail 명령어를 사용하면 마지막 10줄을 Terminal에 출력해서 확인할 수 있다.

7. 텍스트 파일의 행 수를 세고 싶을 때

예를 들어, “example.txt” 파일의 행 수를 세려면 다음과 같이 명령한다.

  
  wc -l example.txt
  

8. 두 열의 값을 합하여 새로운 열을 추가

예를 들어 5번째 열과 6번째 열의 숫자를 합해서 7번째 열을 추가하여 7열의 데이터로 만들고자 하면 awk 명령어를 사용하여 5번째 열과 6번째 열의 숫자를 합하여 7번째 열을 추가할 수 있다.

  
  awk -F"\t" '{print $0, $5+$6}' input_file.txt > output_file.txt
  

9. CSV 파일을 TSV 파일로 전환하기

.csv 파일은 쉼표(,)로 열을 구분하는 텍스트 파일이고 .tsv 파일은 탭으로 열을 구분하는 텍스트 데이터 파일이다. 이 후에 후술할 Shell Script에는 .tsv로 된 파일을 염두에 두고 작성하였으므로 해당 Shell Script를 사용하여 Journal Entry Test를 하고자 한다면 애초에 탭으로 구분된 텍스트 파일을 입수하던지 .csv 파일을 이미 입수하였다면 쉼표(,)를 탭으로 바꾸고 나서 다음의 Shell Script를 실행하면 구분자로 인해 오류가 나지는 않을 것으로 예상할 수가 있다.

sed 명령어를 사용하면 다음과 같다.

  
  sed 's/,/\t/g' 원본파일 > 변경된파일
  

Shell Script의 활용

Journal Entry Test에 있어 복잡하고 다양한 분석 방법을 적용 할 수도 있겠지만 너무 큰 데이터를 앞에 두고 다양한 분석 방법을 적용하고자 하기에 앞서 다양한 분석은 하기도 전에 최소한의 완전성 검증 및 몇개의 시나리오 테스트를 성공적으로 할 수는 있을까에 대해 걱정이 앞설 수도 있다.

Bash Shell에서 직접 텍스트 파일에 접근하여 몇가지 가정에 의한 합계 또는 부분합을 내거나 단순한 cat과 grep명령어로 큰 파일의 일부 단어를 포함하는 행을 필터링 할 수도 있고 join 명령어를 활용하여 특정 배열값과 일치하는 행을 남기고 나머지는 제거한 파일을 생성할 수도 있다. 이런 Shell 명령어들을 비어 있는 검은 Terminal에 껌벅이는 프롬프트에서 일일이 쳐서 명령하는 것은 어려울 수도 있다. 어떤 명령어든지 사람이 이해하고 외워서 쓸 정도가 되기에는 많은 반복 학습이 필요한데 반복을 하더라도 수 차례 같은 명령어를 치는 것은 엄청난 피로를 수반할 수가 있다.

그러므로 리눅스 Shell에서는 Shell Script를 작성하여 명령어를 순차적으로 모아 두고 그 명령어 모음을 .sh 확장자로 된 파일로 저장한 다음 그 파일을 실행하면 공통된 명령어를 인수를 받아 실행하는 함수처럼 사용할 수가 있다.

Shell Script는 Bash Shell의 명령어를 Text 형태의 문서에 써 두고 계속해서 실행하면서 써먹는 방식이다. 어찌 보면 R이나 Python으로 된 Script를 써 두고 필요에 따라서 실행하는 것과 다를 점이 없다.

Shell Script를 깊이 있게 공부했거나 잘 아는 것은 아니지만 Chat-GPT와 Copilot에게 질문하는 과정을 거치면서 Shell Scripting을 해서 Script를 Journal Entry Test에 적용하였더니 이것만으로도 아주 기본적인 전표분석의 기본기는 갖출 수 있을 것 같은 생각이 들었다. Sample로 사용한 Data는 메모리가 견디기 쉽지 않은 큰 Data로써 잠시 시간을 두고 한줄 한줄 Shell의 명령어가 시킨 일을 수행하는 것을 기다리기만 하면 이내 작업의 결과물을 나타내는 것을 보고 복잡한 Data 분석이 아니고 겨우 Journal Entry Test와 같이 부분합이나 내서 재무제표의 결과값과 같은지만 확인하는 정도의 과정에는 Shell에서만 대부분의 작업을 하고 나서 다른데 옮기면 충분할 수도 있겠다는 판단을 하게 되었다.

이제 이렇게 해서 작성된 Shell Script를 공유하고자 한다.⁷

0. Data 가정

다음과 같은 6개 열의 최소한의 Journal Entry test를 하기 위한 Data가 있다고 가정한다.

  
  head je.txt
  

이 데이터는 열은 6개 밖에 되지 않지만 행이 매우 많아 대락 5기가 이상의 용량이라고 가정한다. SAP에서 생성된 데이터로 차대구분자의 차변은 S로 대변은 H로 표시되어 있다.(S는 독일어로 Soll(영어로 Objective), H는 독일어로 Haber(영어로 Have)를 의미한다.)

1. Shell Script를 사용하여 CSV 파일을 TSV 파일로 전환하기

쉼표로 구분된 .csv 파일을 탭으로 구분된 텍스트 파일로 변환하는 Shell Script는 다음과 같이 다운로드 받을 수 있다(Ubuntu Linux 환경에서 확인한 방법).

  
  wget https://raw.githubusercontent.com/joy-hhh/R_for_JE_test/main/sh/csvtotsv.sh
  

cat 명령어로 Script의 내용을 확인하면 어떤 인수를 순차적으로 적어 넣어야 하는지에 대하여 알 수 있다.

  
  cat csvtotsv.sh
  

#!/bin/bash

# 입력 파일의 이름
input_file="$1"

# 출력 파일의 이름
output_file="$2"

# tr 명령어로 입력 파일에서 쉼표를 탭으로 바꿉니다.
# 그 결과를 출력 파일에 씁니다.
tr ',' '\t' <"$input_file" >"$output_file"

내용에 맞게 입력 파일의 이름과 출력 파일의 이름을 나란히 써서 명령하면 지정한 출력 파일의 이름으로 탭으로 구분된 텍스트 파일이 생성된다.

  
  bash csvtotsv.sh je.csv je.txt
  

ls 명령어로 새로 생긴 파일이 있는지 확인 할 수 있다.

  
  ls
  

2. 탭으로 구분된 데이터 파일의 각 열에 대한 NA 값 계산하기

각 열에 있는 NA 값의 갯수를 세어 출력하는 Shell Script는 다음과 같이 다운로드 받을 수 있다(Ubuntu Linux 환경에서 확인한 방법).

  
  wget https://raw.githubusercontent.com/joy-hhh/R_for_JE_test/main/sh/na.sh
  

cat 명령어로 Script의 내용을 확인하면 어떤 인수를 순차적으로 적어 넣어야 하는지에 대하여 알 수 있다.

  
  cat na.sh
  

#!/bin/bash

# 입력 파일의 이름
input_file="$1"

# 열 개수 세기
colnum=`awk -F'\t' '{print NF; exit}' "$1"`

awk -F"\t" '{ for (i=1; i<='"$colnum"'; i++) { if ($i == "") { count[i]++ }}} END { for (i=1; i<='"$colnum"'; i++) { printf "%d\t", count[i] } printf "\n"}' "$input_file" 

내용에 맞게 bash와 스크립트 파일 이름, 데이터 입력 파일의 이름을 나란히 써서 명령하면 각 열의 비어 있는 값의 갯수를 출력한다.

  
  bash na.sh je.txt
  

3. 최소값과 최대값의 확인

최소값과 최대값의 확인하는 Shell Script는 다음과 같이 다운로드 받을 수 있다(Ubuntu Linux 환경에서 확인한 방법).

  
  wget https://raw.githubusercontent.com/joy-hhh/R_for_JE_test/main/sh/minmax.sh
  

Shell Script를 사용하기 위한 방법은 minmax.sh 파일 이름과 해당 전표 파일 이름을 쓰고 최소값과 최대값을 확인하고자 하는 열의 순번을 숫자로 기재하는것이다.

cat 명령어로 Script의 내용을 확인하면 어떤 인수를 순차적으로 적어 넣어야 하는지에 대하여 알 수 있다.

  
  cat minmax.sh
  

#!/bin/bash

# 입력 파일의 이름
input_file="$1"

# 출력할 열의 번호
column="$2"

# awk 명령어로 입력 파일에서 해당 열의 최소값과 최대값을 구합니다.
awk -F'\t' -v c="$column" 'NR == 1 { min=$c; max=$c } NR > 1 && $c < min { min=$c } NR > 1 && $c > max { max=$c } END { print "최소값:", min; print "최대값:", max }' "$input_file"

이 Script는 날짜열의 숫자들이 20130103이나 20131227 과 같이 숫자로 기재되어 있는 경우에 가장 작은 숫자와 가장 큰 숫자를 찾아서 Shell에 나타내 주어 전체 전표가 2023년 회계기간 안에 있는지를 확인 할 수 있게 해 준다. 이 Script를 사용하는 방법은 이러하다.

je.txt라는 탭으로 구분된 텍스트 파일에 날짜 열이 4번째 열에 있다고 가정하면

  
  bash minmax.sh je.txt 4
  

이 Script를 실행하면 4번째 열의 최소값과 최대값을 출력해 준다.

최소값: 20230103
최대값: 20231231

4. 대변 항목의 금액을 마이너스로 변경하기

차변 대변 열을 만들고 각각을 전표번호로 합하거나 계정과목별로 합해서 차변에서 대변 금액을 빼는 것이 일반적으로 엑셀 등 스프레드시트를 사용하여 계산할 때 생각하기 쉬운 방법이다.

스프레드시트에서는 눈 앞에 즉각적인 연산의 결과가 보이는 상황으로 가능하면 셀을 넓게 펼쳐서 사용하는 것이 편할 때가 있다.

그러나 명령 방식으로 연산을 수행하고자 할 때 이렇게 넓게 펼치면서 연산을 하려면 더 다양한 명령어와 기능을 필요로 할 수 있으므로 다른 방법을 곰곰히 생각해 보았다.

Journal Entry Test에서 완전성 확인 절차에 있어 가장 핵심적인 기능은 구분자에 근거하여 부분합을 내는 것이다. 전표 전체에서 전표번호에 따른 부분합을 내서 같은 전표번호끼리 각각의 차변금액에서 대변금액을 뺐을 때 0이 되는지 검증하고 또한 전표 전체에서 계정과목별로 각각의 부분합을 내서 차변에서 대변금액을 뺀 것이 시산표의 당기 변동 금액과 일치하는지 검증하는 것이 핵심이므로 구분된 기호에 따른 합계를 Summarise 하는 것이 핵심 기능이 된다.

Bash Shell Terminal에서 그룹별 부분합을 내는 것이 가능하다면 각 그룹별로 차변금액과 대변금액이 2개의 열로 구성될 필요가 없이 최종 목적이 차변에서 대변을 빼서 나온 결과를 사용할 것이므로 대변금액을 마이너스로 기재해서 그룹별 합계를 내면 최종적으로 필요한 값을 한번의 명령어로 도출할 수 있겠다는 생각에 도달하였다.

따라서, 대변 금액을 마이너스로 변경하는 명령이 필요하다.

대변 금액을 마이너스로 변경하여 새로운 파일을 생성하는 Shell Script는 다음과 같이 다운로드 받을 수 있다(Ubuntu Linux 환경에서 확인한 방법).

  
  wget https://raw.githubusercontent.com/joy-hhh/R_for_JE_test/main/sh/minus.sh
  

cat 명령어로 Script의 내용을 확인하면 어떤 인수를 순차적으로 적어 넣어야 하는지에 대하여 알 수 있다.

  
  cat minus.sh
  

#!/bin/bash

# 입력 파일의 이름
input_file="$1"

# 출력 파일의 이름
output_file="$2"

# 구분자열
column1="$3"

# 대변 구분자
h="$4"

# 대변값을 마이너스로 변경할 금액 열
column2="$5"


# awk 명령어로 입력 파일에서 해당 조건을 만족하는 행을 찾아서 여섯번째 열의 값을 음수로 바꿉니다.
# 그 결과를 출력 파일에 씁니다.
awk -F'\t' -v c1="$column1" -v h="$h" -v c2="$column2" '$c1 == h { $c2 = -$c2 } 1' OFS='\t' "$input_file" > "$output_file"

je.txt라는 탭으로 구분된 텍스트 파일에 차대변 구분자 열이 5번째 열에 있고 금액 열이 6번째 열에 있다고 가정하면

  
  bash minus.sh je.txt je_minus.txt 5 "H" 6
  

이렇게 하면 6번째 금액 열의 대변 항목이 마이너스로 변경된 je_minus.txt 파일이 생성된다.

차변항목이 플러스이고 대변 항목이 마이너스가 되도록 하고 나면 금액 열을 전부 합산하여 0이 되면 차변합계와 대변합계가 같은 것을 확인할 수 있다.

  
  awk 'BEGIN {FS=OFS="\t"} {sum += $6} END {print "Total", sum}' je_minus.tsv
  

이렇게 하면 6번째 열이 금액인 경우 해당 열의 전체 합계를 출력한다.

5. 구분자에 따른 부분합 파일 만들기

대변 금액을 마이너스로 변경한 후에 이 파일에서 전표번호와 계정과목 각 구분에 따른 새로운 파일을 생성하는 Shell Script는 다음과 같이 다운로드 받을 수 있다(Ubuntu Linux 환경에서 확인한 방법).

  
  wget https://raw.githubusercontent.com/joy-hhh/R_for_JE_test/main/sh/subtotal.sh
  

cat 명령어로 Script의 내용을 확인하면 어떤 인수를 순차적으로 적어 넣어야 하는지에 대하여 알 수 있다.

  
  cat subtotal.sh
  

#!/bin/bash

filename="$1"
group="$2"
amount="$3"
output_file="$4"


cut -f $group,$amount $filename > temp.txt

# 잘라낸 파일을 첫번째 열의 값으로 정렬합니다.
sort -k1,1 temp.txt > sorted.txt

# 정렬된 파일을 읽으면서 첫번째 열의 값이 바뀔 때마다 그룹별로 합산합니다.
# 그 결과를 출력 파일에 씁니다.
awk 'BEGIN {FS=OFS="\t"} {if ($1 == prev) {sum += $2} else {if (NR > 1) {print prev, sum}; prev = $1; sum = $2}} END {print prev, sum}' sorted.txt > $output_file

# 임시 파일을 삭제합니다.
rm temp.txt sorted.txt

je.txt라는 탭으로 구분된 텍스트 파일에 전표번호 열이 1번째 열에 있고 금액 열이 6번째 열에 있다고 가정하면

  
  bash subtotal.sh je_minus.txt 1 6 A02.tsv
  

이렇게 하면 전표번호별 구분자에 따른 6번째 금액 열의 합계가 들어가 있는 A02.tsv 파일이 생성된다.

이 A02.tsv 파일은 첫번째 열은 전표번호, 두번째 열은 전표번호의 합계로 구성되어 있으므로 위에 이미 소개한 최소값과 최대값의 확인하는 Shell Script를 사용하면 두번째 열에서 0이 아닌 값이 존재하는지 확인 할 수 있다.

시산표 검증을 하기 위한 계정별 합계도 산출해 보면

  
  bash subtotal.sh je_minus.txt 3 6 A03.tsv
  

이렇게 하면 3번째 열 계정과목코드별 구분자에 따른 6번째 금액 열의 합계가 들어가 있는 A03.tsv 파일이 생성된다. 이 파일은 행이 계정과목 갯수 만큼으로 그렇게 많지 않을 것이므로 엑셀 시산표로 옮겨서 연산이 가능하다.

6. 상대계정 분석

상대계정 분석은 프로그래밍 방식으로는 쉽게 할 수 있으나 엑셀로는 어떻게 해야 할지 좋은 방법이 떠오르지 않고 엑셀로 하고자 한다 해도 몇 차례의 귀찮은 과정을 반복해야 하는 엑셀에서 취약한 분석 방법이다.

1) 상대계정 추출

특정 계정에 대한 상대계정이 포함되어 있는 새로운 파일을 생성하는 Shell Script는 다음과 같이 다운로드 받을 수 있다(Ubuntu Linux 환경에서 확인한 방법).

  
  wget https://raw.githubusercontent.com/joy-hhh/R_for_JE_test/main/sh/relative.sh
  

cat 명령어로 Script의 내용을 확인하면 어떤 인수를 순차적으로 적어 넣어야 하는지에 대하여 알 수 있다.

  
  cat relative.sh
  

#!/bin/bash

# 입력 파일의 이름
input_file="$1"

# 필터링할 열의 번호
column1="$2"

# 필터링할 값의 정규식
regex1="$3"

# Key 값인 전표번호 열의 번호
column2="$4"

# 출력 파일의 이름
output_file="$5"


temp_file="temp.txt"

cut -f"$column2","$column1" "$input_file" > "$temp_file"

cat "$temp_file" | grep -w "$regex1" > "temp_1.txt"
cut -f"$column2" "temp_1.txt" > "temp_2.txt"
sort -u "temp_2.txt" > "temp_3.txt"

cut -f"$column2","$column1" "$input_file" > "$temp_file"

sort -t$'\t' -k1,1 "temp_3.txt" -o "temp_1.txt"
sort -t$'\t' -k1,1 "$temp_file" -o "temp_2.txt"
join -t$'\t' "temp_1.txt" "temp_2.txt" > "temp_3.txt"
cut -f2 "temp_3.txt" | sort -u > "$output_file"

# 임시 파일을 삭제합니다.
rm "temp_1.txt" "temp_2.txt" "temp_3.txt" "$temp_file"

je.txt라는 탭으로 구분된 텍스트 파일에 전표번호 열이 1번째 열에 있고 계정과목 열은 3번째 열에 있다고 가정하면

  
  bash relative.sh je_minus.txt 3 "005001YYYY" 1 B01.tsv
  

이렇게 하면 전체 전표에서 “005001YYYY” 계정이 포함되어 있는 관련 계정과목이 모두 포함된 B01.tsv 파일이 생성된다.

2) 상대계정 확인

상대계정 추출 결과를 보고 의구심이 생긴 상대계정 과목에 대하여 두 계정이 동시에 포함된 전표를 뽑아 확인하고 싶을 때 특정 계정 두 개가 동시에 포함되어 있는 전표만 뽑아낸 새로운 파일을 생성하는 Shell Script는 다음과 같이 다운로드 받을 수 있다(Ubuntu Linux 환경에서 확인한 방법).

  
  wget https://raw.githubusercontent.com/joy-hhh/R_for_JE_test/main/sh/relativeJE.sh
  

cat 명령어로 Script의 내용을 확인하면 어떤 인수를 순차적으로 적어 넣어야 하는지에 대하여 알 수 있다.

  
  cat relativeJE.sh
  

#!/bin/bash

# 입력 파일의 이름
input_file="$1"

# 계정과목열의 번호
column1="$2"

# 필터링할 값
regex1="$3"
regex2="$4"

# 전표번호 열(Key)의 번호
column2="$5"

# 출력 파일의 이름
output_file="$6"


temp_file="temp.txt"
cat "$input_file" | grep -w "$regex1" > "temp_1.txt"
cat "$input_file" | grep -w "$regex2" > "temp_2.txt"
cut -f"$column2" "temp_1.txt" | sort -u > "temp_3.txt"
cut -f"$column2" "temp_2.txt" | sort -u > "temp_4.txt"

cut -f"$column2"- "$input_file" > "$temp_file"

sort -t$'\t' -k1,1 "temp_3.txt" -o "temp_1.txt"
sort -t$'\t' -k1,1 "temp_4.txt" -o "temp_2.txt"
sort -t$'\t' -k1,1 -o "$temp_file" "$temp_file"

join -t$'\t' "temp_1.txt" "temp_2.txt" > "temp_3.txt"
join -t$'\t' "$temp_file" "temp_3.txt" > "$output_file"


# 임시 파일을 삭제합니다.
rm "temp_1.txt" "temp_2.txt" "temp_3.txt" "temp_4.txt" "$temp_file"

je.txt라는 탭으로 구분된 텍스트 파일에 전표번호 열이 1번째 열에 있고 계정과목 열이 3번째 열에 있다고 가정하면

  
  bash relativeJE.sh je_minus.txt 3 "005001YYYY" "00XYZA3100" 1 B01_test.tsv
  

이렇게 하면 전체 전표에서 “005001YYYY"와 “00XYZA3100” 계정이 동시에 포함되어 있는 관련 전표행이 모두 포함된 B01_test.tsv 파일이 생성된다.

결론

‘이 정도의 분석을 하는 것으로 충분할까?’ 하는 생각이 든다면 R과 Arrow 및 Duckdb의 세상에 대하여 학습하면 프로그래밍 언어를 활용하여 엄청나게 큰 전표 데이터에 대하여 풍부하고 다양한 분석을 수행할 수 있을것이다. 하지만 전표 데이터의 크기가 부담스럽도록 커서 이 정도의 Journal Entry Test도 하기 어려운 상황에서 Bash Shell 하나로 간단하면서도 명쾌한 Journal Entry Test를 할 수 있다는 것을 발견하여 뿌듯하고 기쁘다.

Simple is the best!

이 외에 추가적인 작업을 하고 싶다면 Shell Command와 Shell Script에 대하여 예전처럼 기초부터 차근 차근 학습하여 응용 할 수 있는 경지에 이르러야만 추가적인 절차를 만들어 낼 수 있는것은 아니다. 이제는 Chat-GPT와 Copilot이 있으므로 그들에게 물어봐가면서 만들어 나갈 수 있다.