一百三十一 Torture Life – TaraRec Program

Afternoon, Mr. Shoh,

After read through the TaraRec modules, almost vomit blood, I come out with below steps to modify the program.

1. Create a new table for HNK in Prematching, to put the deals and calculate the sub total principal.
‘Create HNK_Sub
Set NewTable = dbs.CreateTableDef("HNK_Sub")
    With NewTable
        .Fields.Append .CreateField("EQDLNO", dbText, 13)
        .Fields.Append .CreateField("EQCurr", dbText, 3)
        .Fields.Append .CreateField("EQPrcpl", dbText)
        .Fields.Append .CreateField("EQSDate", dbDate)
        .Fields.Append .CreateField("EQMDate", dbDate)
        .Fields.Append .CreateField("EQIntR", dbDouble)
        .Fields.Append .CreateField("EQCtpy", dbText, 30)
        .Fields.Append .CreateField("EQBasis", dbText, 6)
        .Fields.Append .CreateField("WSDLNO", dbText, 13)
        .Fields.Append .CreateField("WSCurr", dbText, 3)
        .Fields.Append .CreateField("WSPrcpl", dbText)
        .Fields.Append .CreateField("WSSDate", dbDate)
        .Fields.Append .CreateField("WSMDate", dbDate)
        .Fields.Append .CreateField("WSIntR", dbDouble)
        .Fields.Append .CreateField("WSCtpy", dbText, 30)
        .Fields.Append .CreateField("WSBasis", dbText, 6)
        .Fields.Append .CreateField("SubTotal", dbInteger, 1)
    End With
   
dbs.TableDefs.Append NewTable
   
dbs.TableDefs.Refresh
    Set dbs = Nothing

2. Create a new query for Qry-HNK in Access

3. Possibly can modify the program in matching module “Try to find equivalent”, to select all the
‘Try to find equivalent
            Dim strSQL2 As String
            Dim WSTab2 As DAO.Recordset
            strSQL2 = "SELECT Count([Deal Number]) As NumRec, First([Deal Number]) As [No] " & _
                     " FROM " & Brch & "REF" & _
                     " GROUP BY [Currency], [Maturity Date],[Principal], [Rate], [Basis Code], [+/-]" & _
                     " HAVING [Currency]=’" & ![Currency] & "’" & _
                     " AND [Maturity Date]=’" & ![next_int_date] & "’" & _
                     " AND Format([Rate], ‘0.000000’)=’" & Format(![int_rate], "0.000000") & "’" & _
                     " AND [Basis Code]=’" & ![Basis Code] & "’;"            
            Set WSTab2 = CurrentDb.OpenRecordset(strSQL2) 

4. Add-up the Principal of deals have same Currency, anyway it will comes out as 1 deal with [Deal Number], [Currency], [Sub Total Principal], [Start Date], [Maturity Date], [Interest Rate], [Counterparty], [Basis Code].

5. Tie up the calculated result into one deal and insert back into Brch REF Table (??), which table is used for matching.
‘Insert into Brch REF table
       strSQL3 = "INSERT INTO Brch & "REF" &_
            "( EQDLNO, EQCurr, EQPrcpl, EQSDate, EQMDate, EQIntR, EQCtpy, EQBasis, [Desc] ) " & _
            "SELECT [Deal Number], [Currency], [Sub Total Principal], [Start Date], [Maturity Date], [Interest Rate], [Counterparty], [Basis Code]" & _
            "FROM [Qry – HNK]" & _
            "WHERE [Qry – HNK"].[Deal Number] Is Null;"
       DoCmd.RunSQL strSQL3                                

6. Continue the matching and loop.

Please advice. Thanks.

Warmest Regards

Vivian Sun | Regional Data Centre (Singapore) | The Royal Bank of Scotland Plc | Global Banking and Markets |

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: