MayBeMayBe

MayBeMayBe

Posts Tagged ‘VBA

MS Excel – calculate YoY and Merge two tables with different time series…,,

with 2 comments

 

 

..

..

MS Excel을 사용할 때, 값들이 주기적으로 저장되지 않았을 때 YoY 값을 계산하기는 힘이 들다. 가령 날짜별로 흩뿌려진 테이블인 경우에는 주기성이 없기 때문에..

..

그리고, 두개의 테이블이 있는데 두 테이블이 모두 시간측이 달라서 하나의 테이블로 합쳐서 새로운 테이블로 만들기도 힘이 든 경우도 많다.

예전에는 이것을 해결하기 위해서 VBA MACRO를 직접 만들고 얼마전까지 사용했었다.

..

https://maparam.wordpress.com/tag/excel/

https://maparam.wordpress.com/tag/vba/

..

그렇지만 내가 VBA MACRO 같은 것을 그렇게 좋아하는 것도 아니고……(보안 문제 때문에 특히 그렇다. 그리고 VBA Macro 등은 다른 사람들이 편히 쓰기에도 무리가 있고….)

..

다른 쉬운 방법이 없을까…를 생각하다가 문들 떠올랐다.

..

..

1. YoY 계산하기….

아래와 같은 테이블이 있다고 하자.

..

Excel 001

..

위와 같은 테이블은 1962년 01월 02일 부터 2013년 12월 24일정도까지 미국 재무부 국채 값에 대한 일일 금리 값들이다.

참고로 미국 fred인 경우에는 친절하게, 그리고 편하게 YoY 값들도 뽑을 수 있다. 그렇지만 아닌 경우도 상당히 많으니…..

모든 것은 응용이고 어떻게 쓸 것인가 문제다..

..

다시, 본론으로 들어가면…

위 그림과 같이 날짜별로 값들이 정렬되어 있는 경우, 어떻게 YoY 값들을 구할 것인가인데….

생각보다 쉬운 문제였다.

MS Excel에서 제공하는 몇개의 함수를 사용하면 된다..

..

..

Excel 002

..

위에 있는 그림에서 F2 Cell(1번)을 보면 식(2번)이 있다.

2번식은 다음과 같이 되어 있다.

= DATE((YEAR(A2)-1), MONTH(A2), DAY(A2))

..

위 식이 뜻하는 것은 A2 Cell(1962-01-02)에 있는 값에서 연도만 1을 빼고 월과 일은 같이 쓰겠다는 것이다. 즉 A2에 있는 1962년 1월 2일에 대한 바로 1년 전 값을 돌려주는 식이다.

결과값은 당연히 F2 Cell에 있는 것 처럼 1961년 01월 02일이 될 것이다.

F열은 F2 Cell에서 구한 값들을 확장하면 되는 것이고…

..

날짜에서 기준열이 되는 A열에 대한 1년 전 값들을 F열에 구했으니 VLookup 함수를 사용하면 된다.

..

Excel 003

..

위에 있는 G2 Cell 값에 대한 수식을 보면 다음과 같다.

=VLOOKUP(F2, $A$2:$D$25000, 2, 0)

..

F2 Cell에 있는 값을 테이블 A2:D25000 중에서 찾는데, 테이블 인덱스 2번째 열 값을 리턴하라는 것이다.

테이블 A2:D25000에서 2번째열(MS Excel에서는 열이 1부터 시작함….)은 위 그림에서 B열에 해당한다.

그러면, F2 cell이 가르키는 값은 1961년 1월 2일이니, 테이블 A2:D25000 중에서 1961년 1월 2일에 해당하는 값을 찾는데 그런 값이 없다면 #N/A(not available..?)이 리턴된다.

위에서 리턴 된 값을 모든 행에 대해서 확장을 한다면 다음과 같은 그림을 구할 수 있다.

..

Excel 004

..

위 그림에서 파란 값은 원래 값이고 빨간 색은 원래 있던 값에서 YoY로 계산한 것이다.

참고로 YoY 값은 G열에서 리턴한 값에다가 B열에 있는 값을 비교하여 계산하면 된다.

=(B2-G2)/G2

..

Excel 005

..

위는 1 ~ 17행까지와 256 ~ 281행까지 있는 테이블을 두개로 분활해서 보여주는 것인데, 263행 F열에 있는 값이 1962년 1월 2일 값을 찾아서 리턴 한 값을 원래 있던 값과 비교하여 보여주는 것이다.

두 값이 같다..

..

H열은 YoY로 계산한 값이다…

..

이렇게 하면 상대적으로 쉽게 날짜별로 되어 있는 엑셀 값에서 YoY를 구할 수 있다.

.

..

..

2.

시간 측이 서로 다른 두 테이블을 하나의 날짜 기준으로 통합하기…

..

..

아래 그림 1, 2와 같이 시간축(열A, 열G)이 서로 두개 이상의 테이블을 하나의 기준축으로 합칠 때….

..

Excel 006

..

위에 있는 두개의 테이블을 보면 시간축이 서로 달라서 한번에 두 테이블을 합치기 힘이 들다..

그렇지만 위와 같은 것도 Vlookup 함수를 사용하면 편하게 하나의 기준 값으로 합칠 수 있다.

..

Excel 007

..

A~C열에 있는 테이블과 G~H에 있는 두 테이블을 E열로 합친 결과가 위와 같다.

E2 Cell값 수식을 보면 다음과 같다.

 

=VLOOKUP(A2, $G$2:$H$24957, 2, 0)

 

위에 있는 식은 테이블 G2:H24957 중에서 A2에 있는 값을 E열로 리턴하라는 식이다.

A2는 위 그림에서 1962년 1월 2일 이다.

이렇게 구한 E2 Cell을 확장하면 나머지 날짜에 대해서도 구할 수 있다.

..

..

위에서 왼쪽과 오른쪽에 있는 두개의 테이블을 합치다 보면 두 테이블에 없는 날짜인 경우를 생략하는 문제가 있을 수 있는데, 그것은 조금만 응용하면 되는 문제..

여하튼, 위와 같은 방법을 사용하면 날짜 기준선이 어긋나서 하나의 테이블로 편하게 볼 수 없던 문제도 어느 정도 해결할 수 있다.

..

..

참고로 VLookup 함수만 있는 것이 아니라,

https://maparam.wordpress.com/2013/05/30/ms-excel-index-match-vlookup-hlookup/

위 링크에 있는 것 처럼 Index, Match 함수를 사용해도 된다..

..

여하튼 응용일 뿐이니..

.

 

..

Advertisements

Written by MayBeMayBe

December 26, 2013 at 22:39

Posted in GarbageInGarbageOut

Tagged with ,

MS Excel – Index, Match, vLookup, hLookup…

with one comment

 

 

 

..

http://support.microsoft.com/kb/214142

..

http://office.microsoft.com/en-us/excel-help/index-HP005209138.aspx

..

Index function

http://office.microsoft.com/en-us/excel-help/index-function-HA102752976.aspx?CTT=1

..

Match function

http://office.microsoft.com/en-us/excel-help/match-function-HP010342679.aspx?CTT=1

..

Excel Index Match vLookup function

Excel Index Match vLookup function

..

vLookup, hLookup 함수가 있는 것은 어렴풋이 알고 있었는데, 2차원 배열 형태로 검색을 하고 싶다면 어떻게 해야할까…. 궁금하였었다.

..

위는 코드명과 회사명에 맞는 2차원 배열 검색에 대한 결과 값을 보여 주는 예….

..

이런 것을 욉고 다닐 수는 없기애 기록 차원에서….

..

=VLOOKUP(C21, $B$2:$F$16, 5, 0)

에서 5는 5번째 열…..에 있는 값을….

..

‘=INDEX($B$2:$F$16, MATCH(C21, $B$2:$B$16, 0), MATCH(D21, $B$2:$F$2, 0))

에서는 Cell C21(코드값), D21(회사명)에 맞는 값을 찾아내서 결과를 돌려 주고 있다…

..

..

=========================================================

*추가..

그런데 이런 2차원 배열 형태로 검색하는 것도 많이 사용할 텐데, 왜 따로 함수를 하나 만들지 않았을까…..

필요성을 느끼지 못하는 것인가, 아니면 있는데 내가 모르는 것인가……

..

단일 함수 형태로 제공할 것 같은데….

없다면 사람들이 많이 요구할 것 같고…

..

 

 

 

Written by MayBeMayBe

May 30, 2013 at 23:44

Posted in GarbageInGarbageOut

Tagged with ,

MSDN – Excel 2010에서 VBA 사용 시작

leave a comment »

 

 

..

..

MSDN – Excel 2010에서 VBA 사용 시작

http://msdn.microsoft.com/library/ee814737(v=office.14)

..

=========================================================

Office 2003, Office 2007, Office 2010에서 VBA가 큰 기능 차이가 없을 듯.

버전이 올라가면서 새로이 추가되거나 빠진 기능들이 생기겠지만, 사람들이 많이 사용하는 기본 기능에는 큰 차이가 없다고 생각하는게 좋아 보인다.

..

https://maparam.wordpress.com/tag/vba/

 

..

MSDN이 그래도 문서는 방대하고 잘 되어 있다.

..

 

 

Written by MayBeMayBe

April 29, 2013 at 13:51

Posted in GarbageInGarbageOut

Tagged with

VBA – 간단한 함수 실행 및 간단한 예제…..,,

leave a comment »

 

 

..

 

https://maparam.wordpress.com/tag/vba/

..

 

 

이 과정이 필요한가 싶지만, 간단하게라도 설명을 해 두는 것이 여러모로 도움이 더 될것 같다. 디버거 설정과 사용법이 어떤 면에서는 더 중요한 것일테지만 아무래도 그 것은 시중에 나와 있는 책이나 다른 사이트에서 도움을 구하는 것이 좋을 것 같다.

..

VBA 001

..

위에 있는 그림은 Excel과 같은 MS Office를 설치할 때 나오는 과정 중 일부이다. 위에서 강조한 것 처럼 VBA를 사용할려면 아마도 Visual Basic for Applications, 줄여서 VBA를 설치해야할 것이다.(설치를 하지 않아도 되는지는 확인해 보지 않아서 모른다.)

..

처음에 설치할 때 기본으로 설치가 되게 설정이 되어 있는 것으로 알고 있다. 만약 설치가 되지 않았다면 Upgrade 형태로 추가 설치하면 될 것이다.

..

VBA 002

엑셀문서를 열었을 때 모습이다. 위에 있는 그림처럼 메뉴에 있는 “개발 도구”를 누르면 Visual Basic, 매크로 같은 세부 항목을 볼 수 있다.

참고로 위에서 “매크로 보안”이라고 느낌표가 있는 주의 표시가 보이는데, 그 버튼을 누르면 아래와 같다.

..

VBA 002 - 1

엑셀이나 기타 Office에 들어 있는 매크로는 컴퓨터 보안 문제를 일으킬 수 있다. 그것에 대해 미리 주의할 점을 보여주는 것으로 이해하면 될 것이고….

가급적이면 위와 같은 “기본값”을 앞으로도 계속 사용하는 것을 권장하고 싶다. 보안 문제라는 것이 생각보다 까다로운 문제다. 나중에 자신이 어느 정도 전체적으로 감을 잡는다면 각 문서에서 자신에게 필요한 설정값을 유지하면 되겠지만…

엑셀문서를 외부에서 받아서 문제가 생길 수도 있다. 그러니 기본적으로 “매크로를 제한”하는 것이 좋은 선택일 것이다.

..

..

VBA 003

위에 있는 예제 그림에서 “메뉴도구” 밑에 있는 “Visual Basic” 세부 메뉴를 누르면 위와 같은 “Microsoft Visual Basic for Applications”라는 VBA IDE(Integrated Development Enviornement)라는통합개발환경 창이 새로 열린다. 뭐 새로운 창이 하나 뜬다고 알면 되는 것이고…….

위에서

1.번이라고 표시되어 있는 것을 보면 내가 연 Excel 문서에 대한 파일 이름, 위와 같은 경우에는 “VBA Text.xlsx”에 대한 VBA 개발환경이라는 것이라고 이해하면 될것이고…

2. 번을 보면 다시 VBA Test.xlsx 문서에 대해 “Sheet1” 엑셀 쉬트(Sheet1)에 대한 창이라는 것을 알 수 있다. 위에서 “Sheet1″을 더블클릭하면 3. 번과 같은 새로운, 그러나 아무 것도 없는 창이 하나 열린다.

3. 번에다가 밑에서 보이는 것 처럼 Sub Hello() ~ End Sub 부분을 전체 복사해서 3.번 창에 붙여놓는다.


' 테스트..
' 엑셀문서에서 (1, C)에서 (5, C)까지 Hello.. 출력

Sub Hello()

' Loop 1행에서 5행까지, 각 C열에 대해 "Hello.."를 출력하게
For IndexLoop = 1 To 5
   Cells(IndexLoop, 3).ClearContents
   Cells(IndexLoop, 3).Value = "Hello.."

Next IndexLoop

End Sub

' Hello() 함수 끝...

..

4. 5.6 번은 디버거를 실행할 때 필요한 창인데, 나중에 간단히 설명하기로 하고….

7번을 보면 1 ~ 7번에 대하여 각각, 엑셀 Cells (1, 3), (2, 3), (3, 3), (4, 3), (5, 3) Cell에 대해 “Hello….”를 출력하게 하는 것이다.

For IndexLoop = 1 To 5

Cells(IndexLoop, 3).ClearContents
Cells(IndexLoop, 3).Value = “Hello..”

Next IndexLoop

은 소스에서 그것을 하라고 말하는 것이다.

..

..

VBA 004

..

위에 있는 것이 바로 위에서 설명한 부분을 말하는 것인데….

Sub Hello()라고 적힌 1.번 부분 소스를 붙여 넣기 한 다음, 3. 번에 보면 Hello..라고 (1, 3), (2, 3) Cell에 대해 출력되는 것을 확인할 수 있다.

..

VBA 005

..

위에서 설명한 것을 실행하려면, Visual Basis for Applications라는 IDE(통합개발환경) 창에서 “디버그”라는 메뉴를 누르고, “한 단계씩 코드 실행(단축키로 F8)”을 부지런히 한 10여번 누르다 보면 Excel 문서에 “Hello..”가 출력되는 것을 확인할 수 있다.

..

“디버그” 메뉴를 설명을 조금 더 자세히 설명을 해야 하는데……… 엄두가 나지 않는다.

보통, 소스를 붙여넣기기나 아니면 자신이 작성하고.. 그 작성된 소스에 대해 문제 없이 실행되는지 편하게 확인하는 곳이 “디버그(De-Bug)”, 즉 버그를 잡는다고 보면 될텐데….

문제가 있는 소스나, 어디서 문제가 있는지 파악할 때 유용하게 쓰이는 것이라고 보면 될 듯….

..

중요한 것은, 소스(위에서는 Sub Hello() 함수)를 붙여넣기 한다음…

단축키로 F8(한단계씩 코드 실행)을 누르면 VBA IDE 창에 노랑색 바탕에 노란색 화살표가 있는 것이 보인다. 그 때 다시 “Ctrl + Shift + F8 단축키(프로시저 나가기)”를 누르면 전체 함수가 실행된다…

..

설명하기 힘들다. 사실 나도 VBA에 대해 딱히 말을 할 수 있는 여지가 없어서….이기도 하고….

..

MS에서 MSDN이라는 곳이 있는데,

http://msdn.microsoft.com/en-us/library/office/aa272254(v=office.11).aspx

MSDN에 가면 생각보다 좋은 문서도 많으니 도움이 될지도…

..

VBA이든 Macro이든 간에, 반복적으로 상당히 많이 작업하는 곳에는 상당히 유용한 것은 맞는데…….

..

딱히 공부해야할 필요성이 있을까.. 싶다.

몇개 검증된 함수와 그 함수를 실행하는 방법만 알아도 되지 않을까…

대신, 항상 보안, 보안, 보안에 조심해야 한다.

남이 제공하는 함수나 소스에서 이상한 것이 있을 가능성도 많으니…. 항상 보안에 조심….

..

 

 

 

Written by MayBeMayBe

April 28, 2013 at 21:44

Posted in GarbageInGarbageOut

Tagged with

VBA, 월별로 되어 있는 테이블을 날자별로 테이블 재 작성….,,

leave a comment »

 

 

..

VBA, 월별로 되어 있는 테이블을 날자별로 테이블 재 작성….,,

..

https://maparam.wordpress.com/tag/vba/

..

VBA Ex 001

..

위에서 왼쪽에 있는 Table A와 같이 월별로 작성되어 있는 테이블을 오른쪽 Table B와 같이 일별로 테이블 재 작성…..

밑에 있는 VBA 소스 중에서 ExpandMonthlyTableToDaily()함수를 실행하면 됨….

..

위에서 왼쪽에 있는 테이블은 한국은행 eCOS에서 구한 값인데 월별로 되어 있다. 즉 1997년 7월 ~ 2013년 03월까지 생산자 물가지수, 소비자 물가지수 원계열 값과 생산자물가지수(YoY), 소비자물가지수(YoY)에 대한 테이블이다.

이 테이블을 1997년 07월 01일 ~ 2013년 03월 31일까지 날짜별로 다시 풀어 작성한 것이 오른쪽에 있는 테이블.

이 테이블은 ExpandMonthlyTableToDaily() 함수를 실행하면 됨…

..

이렇게 한 것은 조금 더 오래된 몇개 값들을 비교확인할려고 했던 것인데…….

결과는 그다지 뭐…

..

..

..


' Function
'
' Microsoft Excel Visual Basic Reference
' http://msdn.microsoft.com/en-us/library/office/aa272254(v=office.11).aspx
'
' Office client development
' http://msdn.microsoft.com/en-us/library/bb726434(v=office.12).aspx

Sub ExpandMonthlyTableToDaily()

'================================================================
'
' Table 행 수 : RowCount
' Table 열 수 : ColumnCount
' 날짜별로 Table을 재 생성할 때 놓일 열 Offset : OffsetOutput
'
'
'================================================================
RowCount = 124
ColumnCount = 3

OffsetOutput = 2
'================================================================

' Date
' http://msdn.microsoft.com/en-us/library/office/aa164570(v=office.10).aspx
'
' MSDN ==> Collection
' http://msdn.microsoft.com/en-us/library/yb7y698k(v=vs.80).aspx
' Vector
Dim ValuationVector As New Collection
Dim strDate As String

' Read Time table
For IndexTable = 2 To RowCount
   strDate = Cells(IndexTable, 1).Value
   ValuationVector.Add strDate, CStr(IndexTable)
Next IndexTable

Dim strYear As String
Dim strMonth As String
Dim strNextMonth As String
Dim StrDay As String

Dim strEndDate As Date

Dim strStartDate As String
strStartDate = "2003/12/31"

Dim strIndexDate As Date

Dim SourceRange As Range
Dim DestinationRange As Range

Dim RowIndex As Integer
RowIndex = 2

' Real Value
Dim ValueIndex As Integer
ValueIndex = 0

' Copy Data from original Source.
Dim ColumnStart As Integer
Dim ColumnEnd As Integer

ColumnStart = ColumnCount + OffsetOutput
ColumnEnd = ColumnStart + ColumnCount

If (ValuationVector.Count <> 0) Then
   Range(Cells(1, 1), Cells(1, ColumnCount)).Select
   Selection.Copy

   ' Destionation Table...
   Range(Cells(1, ColumnStart + 1), Cells(1, ColumnEnd)).Select
   Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone
End If

ColumnStart = ColumnStart + 1

' For Each Vector elements
For IndexVector = 1 To ValuationVector.Count
   strDate = ValuationVector(IndexVector)
   strYear = Year(strDate)
   strMonth = Month(strDate)

   intCurrentMonth = CInt(strMonth)
   intNextMonth = intCurrentMonth + 1

   ' Integer to String
   strNextMonth = CStr(intNextMonth)
   strStartDate = DateSerial(Year(strDate), strMonth, "01")
   strEndDate = DateSerial(Year(strDate), strNextMonth, "01")

   ' Consider the end of the monthly day, it could be 29 or 30 or 31.
   strEndDate = DateAdd("d", -1, strEndDate)
   ValueIndex = IndexVector

   ' Fill the data
   For strIndexDate = strStartDate To strEndDate

      ' SourceRange = Range(Cells(RowIndex, 2), Cells(RowIndex, ColumnCount)).Copy
      ' DestinationRange = Range(Cells(RowIndex, ColumnStart + 1), Cells(RowIndex, ColumnEnd))

      ' Copy from Original Table
      Range(Cells(ValueIndex + 1, 2), Cells(ValueIndex + 1, ColumnCount)).Select
      Selection.Copy

      ' Destionation Table...
      Range(Cells(RowIndex, ColumnStart + 1), Cells(RowIndex, ColumnEnd)).Select
      Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone
      Cells(RowIndex, ColumnStart).Value = strIndexDate

      RowIndex = RowIndex + 1

   Next strIndexDate

   ' Insert Wait function.
   ' Thread Switching...?
   ' If (RowIndex Mod 100 = 0) Then
   Application.Wait Now + TimeValue("00:00:01")
   ' End If

Next IndexVector

End Sub
' End Function

..

..

..

..

 

 

Written by MayBeMayBe

April 27, 2013 at 22:10

Posted in GarbageInGarbageOut

Tagged with ,

VBA, 분기별로 되어 있는 테이블을 날짜순으로 테이블 재 작성…

leave a comment »

..

..

..

https://maparam.wordpress.com/tag/vba/

..

Time Table에 분기별로 값들이 정렬되어 있는 것을 날짜별로 풀어쓰는 스크립트…

..

VBA Ex

VBA Ex

..

..

가령 위에 있는 그림에서, Table A와 같은 테이블이 있을 때 A열을 보면 각 분기별로 정리되어 있는 표가 있다. 이것을 각 분기 시작일로 다시 정리해서 테이블을 작성한 것이 Table B와 같다.

열A와 열 M을 비교하면, 열A에서는 2000/03/31(1Q)로 되어 있고 그에 맞는 값들이 옆 열에 정리되어 있는데 이것을 열M과 같기 각 분기 시작일로 확장을 해서 테이블을 작성한 것이다..

..

그림에서 OffsetOutput ==> 2라고 되어 있는 것은, 소스에서 OffsetOutput 부분에서 설정한 값이다. 즉, 테이블을 새로이 만들 때 어느 정도 떨어져서 테이블을 작성할 것인가를 나타내는 값….

..

이렇게 할려면 밑에 있는 ValuationTableQuarterly() 함수를 Excel 에서 실행하면 된다.

 

 

 

..


' Function
'
' Office client development
' http://msdn.microsoft.com/en-us/library/bb726434(v=office.12).aspx

Sub ValuationTableQuarterly()

'================================================================
'
' Table 행 수 : RowCount
' Table 열 수 : ColumnCount
' 날짜별로 Table을 재 생성할 때 놓일 열 Offset : OffsetOutput
'
'
'================================================================
RowCount = 53
ColumnCount = 10

OffsetOutput = 2
'================================================================

'
'
' MSDN ==> Collection
' http://msdn.microsoft.com/en-us/library/yb7y698k(v=vs.80).aspx
' Vector
Dim ValuationVector As New Collection
Dim strDate As String

' Read Time table
For IndexTable = 2 To RowCount
   strDate = Cells(IndexTable, 1).Value
   ValuationVector.Add strDate, CStr(IndexTable)
Next IndexTable

Dim strYear As String
Dim strMonth As String
Dim StrDay As String

' http://msdn.microsoft.com/en-us/library/office/aa164570(v=office.10).aspx
Dim strTempDate As Date
strTempDate = #12/31/2007#

Dim strEndDate As Date

Dim strStartDate As String
strStartDate = "2003/12/31"

Dim strIndexDate As Date

Dim SourceRange As Range
Dim DestinationRange As Range

Dim RowIndex As Integer
RowIndex = 2

' Real Value
Dim ValueIndex As Integer
ValueIndex = 0

' Copy Data from original Source.
Dim ColumnStart As Integer
Dim ColumnEnd As Integer

ColumnStart = ColumnCount + OffsetOutput
ColumnEnd = ColumnStart + ColumnCount

If (ValuationVector.Count <> 0) Then
   Range(Cells(1, 1), Cells(1, ColumnCount)).Select
   Selection.Copy

   ' Destionation Table...
   Range(Cells(1, ColumnStart + 1), Cells(1, ColumnEnd)).Select
   Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone
End If

ColumnStart = ColumnStart + 1

' Vector
For IndexVector = 1 To ValuationVector.Count
   strDate = ValuationVector(IndexVector)
   strYear = Year(strDate)
   strMonth = Month(strDate)

   intMonth = CInt(strMonth)
   Select Case intMonth
      ' 1Q
      Case 1 To 3
         strStartDate = DateSerial(Year(strDate), "01", "01")
         strEndDate = DateSerial(Year(strDate), "03", "31")
         ValueIndex = IndexVector

      ' 2Q
      Case 4 To 6
         strStartDate = DateSerial(Year(strDate), "04", "01")
         strEndDate = DateSerial(Year(strDate), "06", "30")
         ValueIndex = IndexVector

      ' 3Q
      Case 7 To 9
         strStartDate = DateSerial(Year(strDate), "07", "01")
         strEndDate = DateSerial(Year(strDate), "09", "30")
         ValueIndex = IndexVector

      ' 4Q
      Case 10 To 12
         strStartDate = DateSerial(Year(strDate), "10", "01")
         strEndDate = DateSerial(Year(strDate), "12", "31")
         ValueIndex = IndexVector

   End Select

   For strIndexDate = strStartDate To strEndDate

      ' SourceRange = Range(Cells(RowIndex, 2), Cells(RowIndex, ColumnCount)).Copy
      ' DestinationRange = Range(Cells(RowIndex, ColumnStart + 1), Cells(RowIndex, ColumnEnd))

      ' Copy from Original Table
      Range(Cells(ValueIndex + 1, 2), Cells(ValueIndex + 1, ColumnCount)).Select
      Selection.Copy

      ' Destionation Table...
      Range(Cells(RowIndex, ColumnStart + 1), Cells(RowIndex, ColumnEnd)).Select
      Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone
      Cells(RowIndex, ColumnStart).Value = strIndexDate

      RowIndex = RowIndex + 1

   Next strIndexDate

   ' Insert Wait function.
   ' Thread Switching...?
   ' If (RowIndex Mod 100 = 0) Then
   Application.Wait Now + TimeValue("00:00:01")
   ' End If

Next IndexVector

End Sub
' End Function

..

함수 ValuationTableQuarterly() 설명…

위에 있는 함수를 보면, 아래 그림과 같이 되어 있는 부분이 있다.

..

VBA Ex

VBA Ex

RowCount는 Table A에서 행의 수를 나타내는 것이고, ColumnCount는 Table A에서 열의 수를 나타낸다. 그리고 OffsetOutput 값은 제일 위에 있는 그림에서 설명한 것 처럼, Table A를 날짜별로 풀어서 테이블을 다시 작성할 때 Table A에서 어느 정도 떨어져서 새로운 테이블을 만들 것인가를 나타낸다.

..

위에 있는 세개의 값(RowCount, ColumnCount, OffsetOutput 값을 자신이 가지고 있는 테이블에 맞게 수정해 준 다음 ValuationTableQuery() 함수를 실행하면 된다.

그러면, 제일 위에 있는 그림에서 Table B와 같은 표를 구할 수 있다.

..

==================================================================

이렇게 굳이 표를 구하는 이유는, 시계열을 길게하여 Valuation을 편하게 살펴보자는 목적인데…..

..

VBA Ex

VBA Ex

위에 있는 그림을 보면..

1.번이라고 적힌 부분은 날짜이고 2.번이라고 적힌 부분은 HTS에서 구할 수 있는 것이다. 그리고 3번으로 적힌 부분은 제일 위에 있는 그림에서 구한 값들이다.

HTS에서 구한 값과 바로 위에서 구한 테이블을 합성(?, 이라기 보다는 조합…)하면 위와 같은 표를 만들 수 있다.

4.번으로 적힌 부분은 손으로 직접 구한 것이다.

이렇게 HTS에서 구한 Table과 바로 위에서 구한 Table에서 날짜기준이 서로 달라 손 쉽게 하나의 테이블로 합성(?, 조합)하기 어려웠던 문제는 예전에 글을 올렸으므로 그것을 참고하면 될 듯…

..

https://maparam.wordpress.com/tag/vba/

..

..

바로 위에 있는 그림에서 4. 번 부분을 다시 설명해야 한다.

PER = Price/EPSt인데, 위에 있는 그림처럼 Price 값이나 EPSt 값이 테이블에 있는 모든 날짜에 대해 설정되어 있는 것은 아니다. 그래서 이런 값들을 바로 나눈다면 NULL(“”) 값이 Excel Table에 남게 되는 문제가 있다. (PBR, Dividend Yield, ROE…)

처음에는 이것도 프로그램으로 처리를 할까 하다가, 생각해 보니 Excel에 있는 조건함수(IF)가 생각이 났다.

..

VBA Ex

VBA Ex

..

위에 있는 그림에서, L열을 보면 PER에 해당하는 수식 값인 것을 알 수 있다.

PER는 Price(열 B)에서 EPSt(열 H)를 나누면 되는 값인데, 위에서도 설명했듯이 모들 날짜에 대해 Price 값이나 EPSt값이 설정되어 있지 않다. 이렇게 PER를 구해서 열 L을 구하게 되면 NotAValue 같은 값들이 Table에 남게 되는 문제가 있다.

그리셔 생각한 것이 아래와 같은 Excel 조건 실행 수식이다.

 

 

=IF(AND(NOT($B2=””),NOT(H2 =””)), $B2/H2, L3)

 

$B2열이 NULL(“”)이 아닐때와 H2열이 NULL(“”)이 아닐 때만(AND),  B2/H2를 실행하라는 뜻이다.

그리고……..!

위에 있는 IF 수식에서 제일 마지막에 있는 L3를 설명하자면……

B2와 H2에 대해 나누기를 실행한 값은 L2 Cell에 저장된다. 만약 둘 중(H2나 B2)에 하나라도 NULL이라면 값이 저장되지 않는데 이것이 눈에는 보이지 않지만 Excel에는 내부적으로 쓰이는 값이다. 가경 NotAValue라고 하자…..

이런 NotAValue 값이 눈에는 보이지 않지만 Chart를 그리게 된다면 Chart에서는 이런 NotAValue 값을 0으로 처리해 버려서 Chart가 이상하게 보이는 문제가 있다. 그것을 해결하기 위해 임시라도 IF문에서 조건문을 실행했을 때 FALSE(B2가 NULL이거나 H2가 NULL이거나)일 때는 값을 바로 밑에 있는 L3에서 미리 당겨온다는뜻이다. L3나 L4로 내려가다 보면 언젠가는 특정한 날짜에서 B열에 있는 값도 Null이 아니고(AND) H열에 있는 값도 NULL이 아닌 경우가 있다. 그때 값을 계속 위로 당겨온다는 뜻으로 파악하면 된다.

..

만약에 이렇게 앞으로 발생할 값을 미리 당겨오는 것이 싫다면(잘못된 값을 미리 당겨오는 것이므로) IF 조건문 수식을

 

=IF(AND(NOT($B2=””),NOT(H2 =””)), $B2/H2, “”)

 

와 같이 변경해도 된다. 위에서는 IF 조건문에서 가장 마지막 부분을(IF 조건문이 FALSE일 때) NULL(“”)처리하게 하였다. 말로 설명하는 것이 힘든데, 이렇게 구해진 열에 대해 Chart를 그려보면 무슨 말인지 알 것이다.

..

..

이와 같은 방법으로 PBR, ROE, Div Yield 열에 대해 IF 조건 수식을 집어 넣으면 모든 날짜에 대해 표를 구할 수 있다.

..

VBA Ex

VBA Ex

..

위에서 구한 테이블로 작성한 차트다.

..

..

==================================================

=IF(AND(NOT($B2=””),NOT(H2 =””)), $B2/H2, “”)

와 같이 값을 미리 당겨오지 않는경우는 아래와 같다.

VBA Ex

VBA Ex

위와 같이 열 L에 있는 테이블 값들이 아무것도 없는 것 처럼 보여도 Excel 내부에서는 NotAValue 값으로 처리하고 있어서, 이 열L에 대해 차트를 그리면 위와 같은 그림을 볼 수 있다.

 

 

..

..

 

=IF(AND(NOT($B2=””),NOT(H2 =””)), $B2/H2, L3)

와 같은 조건문 수식인 경우는… 아래와 같이..

..

VBA Ex

VBA Ex

..

두개의 그림을 비교하여 보면 차이를 알 수 있다….

 

====================================================================

요약을 한다면…

분기별로 되어 있는 표를 날짜별로 확장을 한 다음..

HTS에서 날짜별로 되어 있는 표을 구한다.

두개의 표를 하나의 표로(같은 날짜 기준으로) 합성(조합)한다.

사용한 함수는 간단히,

ValuationTableQuarterly()와 MergeTableWithDate() 두 개이다.

..

..

 

..

실행하여 보니 버그들이 몇개 보인다.

그렇지만 “응답없음(Not Responding)” 문제에 대해서는 해결한 것을 어느 정도 만족한다고 해야할까…

..

 

 

 

Written by MayBeMayBe

April 21, 2013 at 13:13

Posted in GarbageInGarbageOut

Tagged with ,

Excel Macro – 날짜가 서로 다른 두 테이블 통합, NotAValue Cell 지우기….

leave a comment »

 

 

 

..

Excel Macro – 날짜가 서로 다른 두 테이블 통합, NotAValue Cell 지우기….

..

Excel Macro(VBA??) – YoY 계산…..,,

..

테이블에서 NotAValue와 같은 값들이 엑셀 Cell내에 있어서 차트를 그릴 때, 이상하게 결과가 나오는 경우가 있다.


Sub ClearNotValueCells()

'===============================================
'
' 테이블 크기.....
' RowCount*ColumnCount table
' RowCount ==> 행
' ColumnCount ==> 열
'===============================================
Const RowCount = 4851
Const ColumnCount = 2

Dim strReadData As String
Dim IndexRow As Integer
Dim IndexColumn As Integer

IndexRow = 2
IndexColumn = 2

Do While (IndexRow <= RowCount)

   IndexColumn = 2
   Do While (IndexColumn <= ColumnCount)
      strReadData = Cells(IndexRow, IndexColumn).Value2

      ' Insert Wait function.
      ' Thread Switching...?
      If (IndexRow Mod 200 = 0) Then
         Application.Wait Now + TimeValue("00:00:01")
      End If

      ' 읽어 들인 문자가 NULL("")이면....... Cell contents를 지우자....
      'If (IsNumeric(strReadData) = False) Then
      If (strReadData = "") Then
         Cells(IndexRow, IndexColumn).ClearContents
      End If
      IndexColumn = IndexColumn + 1
   Loop

   IndexRow = IndexRow + 1
Loop
End Sub

..

..

MS Excel VBA ClearCellContents - NotAValue

..

위에 있는 그림이 그 예라고 할 수 있는데,

그림 바탕에 깔려 있는 3451행*6 열에 대한 테이블을 eCOS에서 다운로드 받아 그림을 그리면 제일 위에 있는 그림과 같이 된다. 정확한 이유는 모르지만 각 Cell에 있는 값이 아무것도 없는 것 처럼 보여도 때로는 그 Cell에 들어 있는 값이 “NotAValue”인 경우가 있나 보다.

위에 있는 ClearNotValueCells() 함수를 실행하면 위에 것 중에서 아래와 같은 그림으로 변환 된 것을 알 수 있다. NotAValue가 포함되어 있는 모든 cell에 대해 Cell.Clearcontents를 실행한 이후다….

..

주의할 점….

테이블 크기가 큰 경우, “응답없음”이라는 모래시계가 뜬다. 각 cell에 대해 검사하고 NotAValue 조건이 만족하는 것에 대해 작업을 해서 그런 것이라 생각된다.

무슨 뜻이냐하면, 이런 작업을 하기 전에 항상….. 항상….. 항상……! 백업을 받고 작업하라는 뜻이다. 귀중한 Excel file에 들어 있는 값들을 날릴 수 있다.

무한 루프(Infinite Loop)로 들어가게 된다면, 어쩔 수 없이 강제로 Excel을 종료해야 할 때가 있을 수 있다. 그러면 이전에 작업한 값들이 저장되지 않는다.

작업하기 전에 한상 백업을 받고…….

..

..

날자가 다른 두개의 테이블을 하나의 날자 기준으로 통합…

..

..


' Merge two table.
' 날자가 서로 다른 두개의 테이블을 하나의 날자 기준으로 통합...
  
  
Sub MergeTableWithDate()

'==============================================================
'
' RowLeftCount ==> 첫번재 테이블 행 크기.
' ColumnLeftCount ==> 첫번째 테이블 열 크기.
'
' RowRightCount ==> 두번째 테이블 행 크기
' ColumnRightCount ==> 두번째 테이블 열 크기
'
' Difference ==> 첫번째와 두번째 테이블간 거리....
'
'
'==============================================================
Const RowLeftCount = 4383
Const ColumnLeftCount = 2

Const RowRightCount = 4851
Const ColumnRightCount = 6

Const Difference = 2


'==============================================================


AdjustMaxCount = WorksheetFunction.Max(RowLeftCount, RowRightCount)
AdjustMinCount = WorksheetFunction.Min(RowLeftCount, RowRightCount)

IndexLeft = 2
IndexRight = 2

Dim LeftDate As Date
Dim RightDate As Date
Dim DiffDate As Long

Dim LeftRange As Range
Dim RightRange As Range


' 강제로 각 테이블에 있는 첫번 째 열을 "yy/mm/dd"로 변환. 일관성..
Range(Cells(1, 1), Cells(RowLeftCount, 1)).Select
Selection.NumberFormat = "yyyy/mm/dd"

IndexTemp = ColumnLeftCount + Difference + 1
Range(Cells(1, IndexTemp), Cells(RowRightCount, IndexTemp)).Select
Selection.NumberFormat = "yyyy/mm/dd"


Dim bLeftVoid As Boolean
Dim bRightVoid As Boolean

bLeftVoid = False
bRightVoid = False


Do Until ((IndexLeft > AdjustMaxCount + 1))

   ' Insert Wait function.
   ' Thread Switching...?
   If (IndexLeft Mod 200 = 0) Then
      Application.Wait Now + TimeValue("00:00:01")
   End If
  
   RightCellStartIndex = ColumnLeftCount + Difference + 1
   RightCellEndIndex = RightCellStartIndex + ColumnRightCount - 1
  
   ' Read Date value from two table
   LeftDate = Cells(IndexLeft, 1)
   RightDate = Cells(IndexRight, ColumnLeftCount + Difference + 1)
  
   ' stupid date system.....!!
   ' http://office.microsoft.com/en-001/excel-help/networkdays-HP005209190.aspx
   Dim strYear, strMonth, strDAy As String
   strYear = Year(RightDate)
   strMonth = Month(RightDate)
   strDAy = Month(RightDate)
  
  
   If ((Year(LeftDate) < "1900")) Then
      bLeftVoid = True
   End If
  
   If ((Year(RightDate) < "1900")) Then
      bRightVoid = True
   End If
  
  
   ' Exit Loop
   ' Two table's date is NULL, then exit the loop.
   If ((bLeftVoid = True) And (bRightVoid = True)) Then GoTo EXIT_LOOP
  
   ' Not good........
   If (bLeftVoid = True) Then
      Range(Cells(IndexLeft, 1), Cells(IndexLeft, ColumnLeftCount)).Select
      Selection.ClearContents
      Cells(IndexLeft, 1).Value2 = RightDate
      LeftDate = RightDate
   End If
  
   ' Not Good......!!
   If (bRightVoid = True) Then
      Range(Cells(IndexRight, RightCellStartIndex), Cells(IndexRight, RightCellEndIndex)).Select
      Selection.ClearContents
      Cells(IndexRight, RightCellStartIndex).Value2 = LeftDate
      RightDate = LeftDate
   End If

   ' Difference two date
   DiffDate = LeftDate - RightDate

   ' Left > Right
   If (DiffDate > 0) Then
      Range(Cells(IndexLeft, 1), Cells(IndexLeft, ColumnLeftCount)).Select
      Selection.Insert (xlShiftDown)
      Selection.ClearContents


      Cells(IndexLeft, 1).Value2 = RightDate
      ' Left == Right
      ElseIf (DiffDate = 0) Then
      IndexLeft = IndexLeft + 1
      IndexRight = IndexRight + 1

   ' Left < Right
   Else
      Range(Cells(IndexRight, RightCellStartIndex), Cells(IndexRight, RightCellEndIndex)).Select
      Selection.Insert (xlShiftDown)
      Selection.ClearContents
  
      Cells(IndexRight, RightCellStartIndex).Value2 = LeftDate
   End If

Loop
  
  
EXIT_LOOP:
  
End Sub

..

..

..

..

MergeTableWithDate() 함수를 실행하면 된다.

이것도 마찬가지로 테이블 크기가 큰 경우 “응답없음”이라는 결과가 나올 가능성이 크다.

..

MS Excel VBA MergeTwoTableWithDate

..

위에 있는 그림이 MergeTableWithDate() 함수를 실행하기 전 모습이다.

A Table, B테이블을 자세히 보면….

서로 날자가 다르다……….

이렇게 서로 기준이 다른 날짜를 하나의 날자로 통합하여 보여주려고 한 것이다.

참고로 위에 있는 ‘Difference”가 소스 코드에 있는 “Difference”이다.

A, B 테이블 크기는 각각에 대하여 행*열을 알 수 있다.

..

MS Excel VBA MergeTwoTableWithDate After Merge

..

..

위에 있는 그림은 함수를 실행 한 다음 모습인데, A Table과 B Table 각각에 대하여 날짜순으로 정렬되었다.

..

소스는 각각에 대하여 위에 올렸는데…

..

주의할 점..

생각보다 성능이 상당히 좋지 않다.

“응답없음(Not Responding)”이라는 메시지를 좋아하지 않는데, 툭 하면 뜬다. 그리고 연산도 상당히 오래 걸리고…

물론, 소스에 들어 있는 각각에 대하여 더 적당히 알맞는 함수등을 선택하면 성능 개선은 어느 정도 이루겠지만….

..

강조하고 싶은 것은, 작업하기 전에 항상 백업을 받고 시작하자는 것이다.

귀중한 값들이 들어 있는 파일들을 날렸다는 말을 듣고 싶지 않다.

..

..

MergeTableWithDate() 함수와 ClearNotValueCells() 함수를 구분한 것은 성능 개선 목적도 있었지만 성격이 다른 것이라 아예 구분을 하였다.

원래 값들을 받을 때부터 NotAValue와 같은 값들이 없는 것이었으면 좋겠지만, 현실이 그러하니 어쩔 수 없다..

..

시간을 좀 더 두며, 성능 개선을 이룰 수도 있겠지만 모르겠다.

어제,오늘에 걸쳐 만든 것에 대해 회의적인 생각도 든다. 성능이 형편없다면 쓸모도 당연히 작아진다.

안정성도 없고…..

“응답없음”이 무엇인가…… “응답없음”이…

..

..

참고로 성능을 조금이라도 올리고 싶으면 테이블 크기를 작게 하면 된다.

가령, 열의 수를 줄인다든가 그런 방법..

divide and conquer라고 누군가 이름을 붙이기도 하였지만…

..

특별할 것도 없는 것이지만, 소스는 위에서 복사한다음 excel에서 돌리면 될 듯…

..

참고로, Excel 내부 함수나 클래스 구조 등에 잘 모르는 상태에서 접근한 것이니 잘못 접근하고 엉뚱한 함수를 사용한 경우도 많을 것이다.

나는 지금 모르지만, 더 좋은 함수가 있을 수도 있고..

혹시 성능개선이나 문제점 등에 대해, 또 다른 접근 방법에 대해 알려 주신다면 언제나 환영…….

감사할일 일것이다.

..

..

Written by MayBeMayBe

April 14, 2013 at 14:16

Posted in GarbageInGarbageOut

Tagged with ,