Untitled

mail@pastecode.io avatar
unknown
plain_text
2 months ago
14 kB
1
Indexable
Never
Private Sub MOF16E()
Dim lines() As String
Dim line As String
Dim i As Long

lines = Split(sline, vbLf)

For i = LBound(lines) To UBound(lines)
If (Trim(lines(i)) <> "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(lines(i), "|")
                    
            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] = Format(Get_number(Trim(Tabit(11))), "#.00")
                    Sql_Rst![MOF16E_NOM_VALUE_DATED] = Format(Get_number(Trim(Tabit(12))), "#.00")
                    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
            Next i
End Sub
Leave a Comment