REM TD Canada Trust macro.txt
REM 19Mar2013  www.BillHowell.ca
REM used with /media/bill/e7a697e3-8b2b-4c53-aa7a-a2c4ec83b050/bill/Investments/TD Canada Trust/TD Canada Trust.ods
REM           Sheets "Analysis" and "Transactions"


REM  ****************************************
REM Globals


Dim Doc             As Object
Dim Sheet_Analysis  As Object
Dim Sheet_Transact  As Object
Dim Item_sum_debit  As Real
Dim Item_sum_credt  As Real
Dim String_partial  As String


REM ******************************************
Sub Main

Dim Cell_itemm       As Object 
Dim Cell_debit       As Object
Dim Cell_credt       As Object
Dim col_anal_credt   As Integer
Dim col_anal_debit   As Integer
Dim col_anal_itemm   As Integer

Dim row_anal_start   As Integer
Dim row_anal_curnt   As Integer

Doc            = ThisComponent 
Sheet_Analysis = Doc.Sheets.getByName("Analysis")
Sheet_Transact = Doc.Sheets.getByName("Transactions")

REM  Remember - zero indexed columns!!

col_anal_credt  =  4 - 1
col_anal_debit  =  3 - 1
col_anal_itemm  =  2 - 1 
row_anal_start  = 12 - 1

For row_anal_curnt = row_anal_start To 100  
   Cell_itemm = Sheet_Analysis.getCellByPosition(col_anal_itemm, row_anal_curnt)
   Cell_debit = Sheet_Analysis.getCellByPosition(col_anal_debit, row_anal_curnt)
   Cell_credt = Sheet_Analysis.getCellByPosition(col_anal_credt, row_anal_curnt)

   IF String_len > 0 THEN 
      CALL Item_Transactions 
      Cell_debit.Value = Item_sum_debit
      Cell_credt.Value = Item_sum_credt
      END IF

   Next row_anal_curnt

End Sub




REM ******************************************
Sub Item_transactions

Dim Cell_credt       As Object
Dim Cell_dater       As Object 
Dim Cell_debit       As Object
Dim Cell_itemm       As Object 

Dim col_tran_credt   As Integer
Dim col_tran_dater   As Integer
Dim col_tran_debit   As Integer
Dim col_tran_itemm   As Integer

Dim row_tran_start   As Integer
Dim row_tran_curnt   As Integer

Dim String_len       As Integer
Dim String_out       As String

REM  Remember - zero indexed columns!!

col_tran_credt  =  6 - 1
col_tran_dater  =  3 - 1
col_tran_debit  =  5 - 1
col_tran_itemm  =  4 - 1 

row_tran_start  =  5 - 1

Item_sum_debit = 0
Item_sum_credt = 0

For row_tran_curnt = row_tran_start To 10000 
   Cell_credt = Sheet_transact.getCellByPosition(col_tran_credt, row_tran_curnt)
   Cell_dater = Sheet_transact.getCellByPosition(col_tran_dater, row_tran_curnt)
   Cell_debit = Sheet_transact.getCellByPosition(col_tran_debit, row_tran_curnt)
   Cell_itemm = Sheet_transact.getCellByPosition(col_tran_itemm, row_tran_curnt)

   IF Cell_dater.String = "End of list" THEN  
      EXIT FOR 
      ELSE
         Date_tran = Cell_dater.Value 
         IF (AND (date_start <= date_tran) (date_tran <= date_end)) THEN 
            String_itemm = Cell_itemm.String
            String_partial_in = Search(String_partial, String_itemm, 1)
            IF (NOT isfault String_partial_in) THEN 
               Item_sum_debit = Item_sum_debit + Cell_debit.Value 
               Item_sum_credt = Item_sum_credt + Cell_credt.Value 
               END IF
            END IF
         END IF        

   Next row_tran_curnt

End Sub


REM enddoc