Untitled

avatar
unknown
plain_text
2 years ago
14 kB
9
Indexable
Private Sub MOF16E()
If (Trim(sline) <> "YSORT|TYPE|PORTFOLIO_TYPE|PORTFOLIO|GROUP_CODE|GROUP_DESCRIPTION|SECURITY_CODE|SUB_ASSET_TYPE|RESIDENCE|DESCRIPTION|CURRENCY|NOM_CUR_POSITION|NOM_VALUE_DATED|COUPON|MATURITY_DATE|HIST_CUR_PRICE|HIST_VAL_PRICE|AM_COS_CUR|AM_COS_DAT|val_cost_cur|VAL_COST_DAT|MARKET_VALUE_CUR|MARKET_VALUE_DAT|UN_PL_CUR|UN_PL_DAT|EST_INC_CUR|EST_INC_VAL|MARKET_PRICE|COUPON_PYMT_DATE|ACC_INT_CUR|ACC_INT_DAT|ISSUE_PER_CUR|ISSUE_PER_VALUE|TIER1_CUR_POS|TIER1_VALUE_DATED|DAT_LAST_TRD|LAST_TRAD|NB_PAYMENT|RATING|SUM_NOM_CUR_POS|SUM_NOM_VAL_DAT|INDUSTRY_CODE|TOTAL_ISSUED") Then

Tabit = Split(sline, "|")
                    
            If UBound(Tabit) > 0 Then
                 Sql_Rst.AddNew
                    CC = CC + 1
                    Sql_Rst![MOF16E_batchdate] = Format(Logsdate, "yyyy/mm/dd")
                    Sql_Rst![MOF16E_counter] = CC
                    If Trim(Tabit(0)) <> "" Then
                    Sql_Rst![MOF16E_sort] = Get_number(Trim(Tabit(0)))
                    Else
                    Sql_Rst![MOF16E_sort] = "1"
                    End If
                    Sql_Rst![MOF16E_type] = Trim(Tabit(1))
                    Sql_Rst![MOF16E_portfolio_type] = Trim(Tabit(2))
                    Sql_Rst![MOF16E_portfolio] = Trim(Tabit(3))
                    Sql_Rst![MOF16E_group_code] = Trim(Tabit(4))
                    Sql_Rst![MOF16E_group_description] = Trim(Tabit(5))
                    Sql_Rst![MOF16E_security_code] = Trim(Tabit(6))
                    Sql_Rst![MOF16E_sub_asset_Type] = Trim(Tabit(7))
                    Sql_Rst![MOF16E_residence] = Trim(Tabit(8))
                    Sql_Rst![MOF16E_description] = Trim(Tabit(9))
                    Sql_Rst![MOF16E_CURRENCY] = Trim(Tabit(10))
                    Sql_Rst![MOF16E_NOM_CUR_POSITION] = Get_number(Trim(Tabit(11)))
                    Sql_Rst![MOF16E_NOM_VALUE_DATED] = Get_number(Trim(Tabit(12)))
                    Sql_Rst![MOF16E_coupon] = Get_number(Trim(Tabit(13)))
                    If Trim(Tabit(14)) <> "" Then
                    Sql_Rst![MOF16E_MATURITY_DATE] = DateSerial(Mid(Trim(Tabit(14)), 1, 4), Mid(Trim(Tabit(14)), 6, 2), Mid(Trim(Tabit(14)), 9, 2))
                    Else
                    Sql_Rst![MOF16E_MATURITY_DATE] = "1900/01/01"
                    End If
                    Sql_Rst![MOF16E_hist_cur_price] = Get_number(Trim(Tabit(15)))
                    Sql_Rst![MOF16E_hist_val_price] = Get_number(Trim(Tabit(16)))
                    Sql_Rst![MOF16E_am_cos_cur] = Get_number(Trim(Tabit(17)))
                    Sql_Rst![MOF16E_am_cos_dat] = Get_number(Trim(Tabit(18)))
                    Sql_Rst![MOF16E_val_cost_cur] = Get_number(Trim(Tabit(19)))
                    Sql_Rst![MOF16E_val_cost_dat] = Get_number(Trim(Tabit(20)))
                    Sql_Rst![MOF16E_market_price] = Get_number(Trim(Tabit(27)))
                    Sql_Rst![MOF16E_market_value_cur] = Get_number(Trim(Tabit(21)))
                    Sql_Rst![MOF16E_market_value_dat] = Get_number(Trim(Tabit(22)))
                    Sql_Rst![MOF16E_unpl_cur] = Get_number(Trim(Tabit(23)))
                    Sql_Rst![MOF16E_unpl_dat] = Get_number(Trim(Tabit(24)))
                    If Trim(Tabit(28)) <> "" Then
                    Sql_Rst![MOF16E_COUPON_PYMT_DATE] = DateSerial(Mid(Trim(Tabit(28)), 1, 4), Mid(Trim(Tabit(28)), 6, 2), Mid(Trim(Tabit(28)), 9, 2))
                    Else
                    Sql_Rst![MOF16E_COUPON_PYMT_DATE] = "1900/01/01"
                    End If
                    Sql_Rst![MOF16E_EST_CUR] = Get_number(Trim(Tabit(25)))
                    Sql_Rst![MOF16E_EST_DAT] = Get_number(Trim(Tabit(26)))
                    Sql_Rst![MOF16E_acc_cur] = Get_number(Trim(Tabit(29)))
                    Sql_Rst![MOF16E_acc_dat] = Get_number(Trim(Tabit(30)))
                    Sql_Rst![MOF16E_issue_cur] = Get_number(Trim(Tabit(31)))
                    Sql_Rst![MOF16E_issue_dat] = Get_number(Trim(Tabit(32)))
                    Sql_Rst![MOF16E_tier_cur] = Get_number(Trim(Tabit(33)))
                    Sql_Rst![MOF16E_tier_dat] = Get_number(Trim(Tabit(34)))
                    If Trim(Tabit(35)) <> "" Then
                    Sql_Rst![MOF16E_dat_last_trd] = DateSerial(Mid(Trim(Tabit(35)), 1, 4), Mid(Trim(Tabit(35)), 6, 2), Mid(Trim(Tabit(35)), 9, 2))
                    Else
                    Sql_Rst![MOF16E_dat_last_trd] = "1900/01/01"
                    End If
                    Sql_Rst![MOF16E_last_trad] = Get_number(Trim(Tabit(36)))
                    Sql_Rst![MOF16E_NB_PAYMENT] = Get_number(Trim(Tabit(37)))
                    Sql_Rst![MOF16E_RATING] = Trim(Tabit(38))
                    
                    Sql_Rst![MOF16E_SUM_POS] = Get_number(Trim(Tabit(39)))
                    Sql_Rst![MOF16E_SUM_VAL] = Get_number(Trim(Tabit(40)))
                    
                    
                    Sql_Rst![MOF16E_INDUSTRY_CODE] = Trim(Tabit(41))
                    Select Case Sql_Rst![MOF16E_INDUSTRY_CODE]
                        Case 10, 11, 20, 30, 40, 130, 140, 160
                            Sql_Rst![MOF16E_GROUP_INDUSTRY] = 1
                        Case 70, 75, 80, 90, 91, 100, 110
                            Sql_Rst![MOF16E_GROUP_INDUSTRY] = 4
                        Case 50, 51, 52, 53, 54, 55, 60, 61, 62, 170, 171, 172, 297
                            Sql_Rst![MOF16E_GROUP_INDUSTRY] = 2
                        Case 92, 93, 105, 120, 125, 131, 150, 180, 190, 200, 201, 202, 203, 204, 205, 210, 220, 230, 240, 250, 260, 270, 275, 280, 285, 290, 291, 292, 293, 294, 295, 296, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 310, 312, 314, 316, 320, 330, 332, 340, 350, 360, 362, 370, 380, 390, 400, 401, 410, 420, 430, 440, 442, 450, 460, 470, 480, 490, 496, 500, 501, 502, 503, 504, 505, 506, 510, 520, 530, 540, 550, 560, 570, 576, 578, 580, 590, 596, 600, 610, 620, 630, 640, 650, 660
                            Sql_Rst![MOF16E_GROUP_INDUSTRY] = 3
                    End Select
                    
                    
                    Select Case Sql_Rst![MOF16E_sub_asset_Type]
                        Case 100, 110, 120, 130, 140, 150, 160, 170, 180, 182, 190, 200, 210, 232, 233, 235
                            Sql_Rst![MOF16E_GROUP_SAT] = 2
                        Case 183, 234
                            Sql_Rst![MOF16E_GROUP_SAT] = 4
                        Case 191, 192, 220, 230, 231
                            Sql_Rst![MOF16E_GROUP_SAT] = 5
                        Case 238, 240, 250, 260
                            Sql_Rst![MOF16E_GROUP_SAT] = 1
                        Case 270, 272, 273, 280, 290, 300, 310, 320, 330, 340
                            Sql_Rst![MOF16E_GROUP_SAT] = 6
                        Case 101, 102
                            Sql_Rst![MOF16E_GROUP_SAT] = 3
                    End Select
                    
                    Sql_Rst![MOF16E_TOTALISSUE] = Get_number(Trim(Tabit(42)))
                    
                    
                    Sql_Rst.Update
               
               Else
               
                If Trim(Mid(sline, 7, 40)) <> "" Then
                    Sql_Rst.AddNew
                    CC = CC + 1
                    Sql_Rst![MOF16E_batchdate] = Format(Logsdate, "yyyy/mm/dd")
                    Sql_Rst![MOF16E_counter] = CC
                    If Trim(Mid(sline, 1, 5)) <> "" Then
                    Sql_Rst![MOF16E_sort] = Get_number(Trim(Mid(sline, 1, 5)))
                    Else
                    Sql_Rst![MOF16E_sort] = "1"
                    End If
                    Sql_Rst![MOF16E_type] = Trim(Mid(sline, 7, 40))
                    Sql_Rst![MOF16E_portfolio_type] = Trim((Mid(sline, 49, 40)))
                    Sql_Rst![MOF16E_portfolio] = Trim(Mid(sline, 91, 10))
                    Sql_Rst![MOF16E_group_code] = Trim(Mid(sline, 103, 5))
                    Sql_Rst![MOF16E_group_description] = Trim(Mid(sline, 110, 40))
                    Sql_Rst![MOF16E_security_code] = Trim(Mid(sline, 152, 20))
                    Sql_Rst![MOF16E_sub_asset_Type] = Trim(Mid(sline, 175, 3))
                    Sql_Rst![MOF16E_residence] = Trim(Mid(sline, 180, 3))
                    Sql_Rst![MOF16E_description] = Trim(Mid(sline, 185, 50))
                    Sql_Rst![MOF16E_CURRENCY] = Trim(Mid(sline, 237, 3))
                    Sql_Rst![MOF16E_NOM_CUR_POSITION] = Get_number(Trim(Mid(sline, 242, 19)))
                    Sql_Rst![MOF16E_NOM_VALUE_DATED] = Get_number(Trim(Mid(sline, 263, 19)))
                    Sql_Rst![MOF16E_coupon] = Get_number(Trim(Mid(sline, 285, 19)))
                    If Trim(Mid(sline, 306, 4)) <> "" Then
                    Sql_Rst![MOF16E_MATURITY_DATE] = DateSerial(Trim(Mid(sline, 306, 4)), Trim(Mid(sline, 310, 2)), Trim(Mid(sline, 312, 2)))
                    Else
                    Sql_Rst![MOF16E_MATURITY_DATE] = "1900/01/01"
                    End If
                    Sql_Rst![MOF16E_hist_cur_price] = Get_number(Trim((Mid(sline, 319, 20))))
                    Sql_Rst![MOF16E_hist_val_price] = Get_number(Trim(Mid(sline, 341, 20)))
                    Sql_Rst![MOF16E_am_cos_cur] = Get_number(Trim(Mid(sline, 363, 20)))
                    Sql_Rst![MOF16E_am_cos_dat] = Get_number(Trim(Mid(sline, 385, 20)))
                    Sql_Rst![MOF16E_val_cost_cur] = Get_number(Trim(Mid(sline, 407, 20)))
                    Sql_Rst![MOF16E_val_cost_dat] = Get_number(Trim(Mid(sline, 429, 20)))
                    Sql_Rst![MOF16E_market_price] = Get_number(Trim(Mid(sline, 451, 20)))
                    Sql_Rst![MOF16E_market_value_cur] = Get_number(Trim(Mid(sline, 473, 20)))
                    Sql_Rst![MOF16E_market_value_dat] = Get_number(Trim(Mid(sline, 495, 20)))
                    Sql_Rst![MOF16E_unpl_cur] = Get_number(Trim(Mid(sline, 517, 20)))
                    Sql_Rst![MOF16E_unpl_dat] = Get_number(Trim(Mid(sline, 539, 20)))
                    If Trim(Mid(sline, 562, 4)) <> "" Then
                    Sql_Rst![MOF16E_COUPON_PYMT_DATE] = DateSerial(Trim(Mid(sline, 562, 4)), Trim(Mid(sline, 566, 2)), Trim(Mid(sline, 568, 2)))
                    Else
                    Sql_Rst![MOF16E_COUPON_PYMT_DATE] = "1900/01/01"
                    End If
                    Sql_Rst![MOF16E_EST_CUR] = Get_number(Trim(Mid(sline, 575, 20)))
                    Sql_Rst![MOF16E_EST_DAT] = Get_number(Trim((Mid(sline, 597, 20))))
                    Sql_Rst![MOF16E_acc_cur] = Get_number(Trim(Mid(sline, 619, 20)))
                    Sql_Rst![MOF16E_acc_dat] = Get_number(Trim(Mid(sline, 641, 20)))
                    Sql_Rst![MOF16E_issue_cur] = Get_number(Trim(Mid(sline, 663, 20)))
                    Sql_Rst![MOF16E_issue_dat] = Get_number(Trim(Mid(sline, 685, 20)))
                    Sql_Rst![MOF16E_tier_cur] = Get_number(Trim(Mid(sline, 707, 20)))
                    Sql_Rst![MOF16E_tier_dat] = Get_number(Trim(Mid(sline, 729, 20)))
                    If Trim(Mid(sline, 751, 4)) <> "" Then
                    Sql_Rst![MOF16E_dat_last_trd] = DateSerial(Trim(Mid(sline, 751, 4)), Trim(Mid(sline, 755, 2)), Trim(Mid(sline, 757, 2)))
                    Else
                    Sql_Rst![MOF16E_dat_last_trd] = "1900/01/01"
                    End If
                    Sql_Rst![MOF16E_last_trad] = Get_number(Trim(Mid(sline, 764, 20)))
                    Sql_Rst![MOF16E_NB_PAYMENT] = Get_number(Trim(Mid(sline, 786, 3)))
                    Sql_Rst![MOF16E_RATING] = Trim(Mid(sline, 810, 35))
                    
                    Sql_Rst![MOF16E_SUM_POS] = Get_number(Trim(Mid(sline, 852, 19)))
                    Sql_Rst![MOF16E_SUM_VAL] = Get_number(Trim(Mid(sline, 874, 19)))
                    
                    
                    Sql_Rst![MOF16E_INDUSTRY_CODE] = Trim(Mid(sline, 895, 5))
                    Select Case Sql_Rst![MOF16E_INDUSTRY_CODE]
                        Case 10, 11, 20, 30, 40, 130, 140, 160
                            Sql_Rst![MOF16E_GROUP_INDUSTRY] = 1
                        Case 70, 75, 80, 90, 91, 100, 110
                            Sql_Rst![MOF16E_GROUP_INDUSTRY] = 4
                        Case 50, 51, 52, 53, 54, 55, 60, 61, 62, 170, 171, 172, 297
                            Sql_Rst![MOF16E_GROUP_INDUSTRY] = 2
                        Case 92, 93, 105, 120, 125, 131, 150, 180, 190, 200, 201, 202, 203, 204, 205, 210, 220, 230, 240, 250, 260, 270, 275, 280, 285, 290, 291, 292, 293, 294, 295, 296, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 310, 312, 314, 316, 320, 330, 332, 340, 350, 360, 362, 370, 380, 390, 400, 401, 410, 420, 430, 440, 442, 450, 460, 470, 480, 490, 496, 500, 501, 502, 503, 504, 505, 506, 510, 520, 530, 540, 550, 560, 570, 576, 578, 580, 590, 596, 600, 610, 620, 630, 640, 650, 660
                            Sql_Rst![MOF16E_GROUP_INDUSTRY] = 3
                    End Select
                    
                    
                    Select Case Sql_Rst![MOF16E_sub_asset_Type]
                        Case 100, 110, 120, 130, 140, 150, 160, 170, 180, 182, 190, 200, 210, 232, 233, 235
                            Sql_Rst![MOF16E_GROUP_SAT] = 2
                        Case 183, 234
                            Sql_Rst![MOF16E_GROUP_SAT] = 4
                        Case 191, 192, 220, 230, 231
                            Sql_Rst![MOF16E_GROUP_SAT] = 5
                        Case 238, 240, 250, 260
                            Sql_Rst![MOF16E_GROUP_SAT] = 1
                        Case 270, 272, 273, 280, 290, 300, 310, 320, 330, 340
                            Sql_Rst![MOF16E_GROUP_SAT] = 6
                        Case 101, 102
                            Sql_Rst![MOF16E_GROUP_SAT] = 3
                    End Select
                    
                    Sql_Rst![MOF16E_TOTALISSUE] = Get_number(Trim(Mid(sline, 905, 19)))
                    
                    
                    Sql_Rst.Update
                End If
                End If
                End If
End Sub
Editor is loading...
Leave a Comment