Sub GetFundPrices()
' ORIGINALLY CREATED BY idyllrain @ Lowyat.net
' ORIGINAL SOURCE(S):
'   - https://forum.lowyat.net/index.php?showtopic=3633445&view=findpost&p=75357733
'   - https://forum.lowyat.net/index.php?showtopic=3633445&view=findpost&p=75383114
' UPDATED BY polarzbearz @ Lowyat.net - 16 September 2015
' UPDATED BY idyllrain @ Lowyat.net - 23 December 2015
   
' Get current state of various Excel settings
   screenUpdateState = Application.ScreenUpdating
   statusBarState = Application.DisplayStatusBar
   eventsState = Application.EnableEvents
   displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting
   
' Turn off some Excel functionality temporarily to speed up the macro
   Application.ScreenUpdating = False
   Application.DisplayStatusBar = False
   Application.EnableEvents = False
   ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
   
   Set ws = Worksheets("Investment Details")
   Set Connection = CreateObject("winhttp.winhttprequest.5.1") 'Added on v2.1
   'Connection.Option(WinHttpRequestOption_SecureProtocols) = 2048
   
   Dim i As Long
   Dim lastRow As Long: lastRow = ws.Range("A:A").Find(What:="0", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
   xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Row
   
'Executing logs placeholder
   Dim logs As String
   logs = "Fund update has been successfully executed!" & vbNewLine & "Please refer to the status logs below:" & vbNewLine & vbNewLine
   logs = logs & vbTab & "LINE" & vbTab & "FSM CODE" & vbTab & "STATUS"
   
'Set a Number of Updates counter, starting from zero before going into the loop
   Dim successfulUpdate As Long
   Dim totalUpdate As Long
   sucessfulUpdate = 0
   totalUpdate = 0
   
' Regular Expression setup (For Windows only)
   Dim regEx As New RegExp
   regEx.Global = False
   regEx.MultiLine = True
   regEx.IgnoreCase = False
   Dim xsrfPattern As String: xsrfPattern = "message.{3}(.{36})"
   Dim priceDatePattern As String: priceDatePattern = "latestNavPrice.+?showDate.:(\d+).+?bidPrice.:([0-9.]+)"
   Dim regmatches As Object
   
' Get XSRF Token
   Dim xsrf_token As String: xsrf_token = ""
   Connection.Open "POST", "https://www.fundsupermart.com.my/fsmone/rest/csrf/get-new-csrf-token"
   Connection.Send
   If Connection.Status = 200 Then
       With regEx
           .pattern = xsrfPattern
           If regEx.Test(Connection.ResponseText) Then
               Set regmatches = regEx.Execute(Connection.ResponseText)
           End If
           xsrf_token = regmatches(0).SubMatches(0)
       End With
   End If
   
' Process each row that starts with a first cell value of "1"
   For i = 1 To lastRow
       If ws.Cells(i, 1).Value = "1" Then
           totalUpdate = totalUpdate + 1
           
           Dim fundName As String
           fundName = ws.Cells(i, 3).Value
           
           Connection.Open "POST", "https://www.fundsupermart.com.my/fsmone/rest/fund/get-factsheet?paramSedolnumber=" & fundName, False
           Connection.SetRequestHeader "X-XSRF-TOKEN", xsrf_token
           
           On Error GoTo EndHandler: ' terminate if error in connecting
           Connection.Send
           If Connection.Status = 200 Then
               With regEx
                   .pattern = priceDatePattern
                   If regEx.Test(Connection.ResponseText) Then
                       Set regmatches = regEx.Execute(Connection.ResponseText)
                       ws.Cells(i, 4).Value = ((regmatches(0).SubMatches(0) + 28800000) / 86400000) + 25569 'Update date value'
                       ws.Cells(i, 9).Value = regmatches(0).SubMatches(1) 'Update NAV'
                       successfulUpdate = successfulUpdate + 1
                       logs = logs & vbNewLine & vbTab & i & vbTab & Left(fundName & Space(15), 15) & vbTab & "OK!"
                   Else
                       logs = logs & vbNewLine & vbTab & i & vbTab & Left(fundName & Space(15), 15) & vbTab & "ERROR!"
                       ws.Cells(i, 4).Value = "=TODAY()"
                   End If
               End With
           End If
       End If
   Next i
       
' Restore Excel state
   Application.ScreenUpdating = screenUpdateState
   Application.DisplayStatusBar = statusBarState
   Application.EnableEvents = eventsState
   ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting
       
' Add final line to logs and display to user
   logs = logs & vbNewLine & vbNewLine & "If there are errors in updating the Fund Price, please double check on the FSM Codes and try again."
   MsgBox logs
   Exit Sub
       
EndHandler:
   MsgBox ("Unable to connect to FSM website. Please try again later.")
       
End Sub