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
' Last Updated - 23 Dec 2018

  ' 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
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.EnableEvents = False
  ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting

   Set WS = Worksheets("Investment Details")
   
   Dim i As Long
   Dim lastRow As Long
   'Search for the last line item (column A) with value 0
   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
   
   'From first row all the way to the last row with line item = 0
   For i = 1 To lastRow
       'If this row is a group header row (Column A has a value of 1)'
       If WS.Cells(i, 1).Value = "1" Then
       
           ' total update counter +1
           totalUpdate = totalUpdate + 1
           
           'Get the fund code'
           Dim fundName As String
           fundName = WS.Cells(i, 3).Value
           
           On Error GoTo ErrHandler:
               'Download the correct price list'
               content = DownloadFromFSMMac(fundName)
               
               'Update date value from 4th table cell of first row'
               Dim datestring As String
               datestring = Split(content, ";")(1)
               
               WS.Cells(i, 4).Value = ((datestring + 28800000) / 86400000) + 25569
               
               'Update NAV value from the DIV element in the 3rd table cell of first row'
               WS.Cells(i, 9).Value = Split(content, ";")(0)
               
           successfulUpdate = successfulUpdate + 1
           logs = logs & vbNewLine & vbTab & i & vbTab & Left(fundName & Space(15), 15) & vbTab & "OK!"
SkipBlocks:
'skip everything above from "On Error GoTo ErrHandler:" block
       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
   WS.Range("portfolioLastUpdate").Value = Date
   Exit Sub
   
ErrHandler:
   ' fail counter, messages
   logs = logs & vbNewLine & vbTab & i & vbTab & Left(fundName & Space(15), 15) & vbTab & "ERROR!"
   
   'reset date field to TODAY() - Update date value from 4th table cell of first row'
   WS.Cells(i, 4).Value = "=TODAY()"
   Resume SkipBlocks:
   
EndHandler:
   MsgBox ("Unable to connect to FSM website. Please try again later.")
   
End Sub

Function DownloadFromFSMMac(sCode As String) As String

   Dim sCmd As String
   Dim sResult As String
   Dim sXSRFToken As String
   Dim lExitCode As Long

   ' Idyllrain-Leverage OSX perl commands because there is no built-in support for the HTML object nor regular expressions
   sCmd = "perl -MLWP::UserAgent -le '$l=""https://www.fundsupermart.com.my/fsmone/rest/"";$u=LWP::UserAgent->new(ssl_opts=>{verify_hostname=>0},cookie_jar=>{});$u->post($l.""csrf/get-new-csrf-token"")->decoded_content()=~/(?<=\""message\"":\"")([a-f0-9\-]{36})/;$u->default_header(""x-xsrf-token""=>$1);$u->post($l.""fund/get-factsheet?paramSedolnumber="".shift)->decoded_content()=~/latestNavPrice.+?showDate.:(\d+).+?bidPrice.:([0-9.]+)/;print $2."";"".$1;' " + sCode
   sResult = execShell(sCmd, lExitCode)

   DownloadFromFSMMac = sResult

End Function

Function DownloadFromFSMMac(sCode As String) As String

   Dim sCmd As String
   Dim sResult As String
   Dim lExitCode As Long

   ' Idyllrain-Leverage OSX perl commands because there is no built-in support for the HTML object nor regular expressions
   sCmd = "perl -MLWP::UserAgent -le '$l=""https://www.fundsupermart.com.my/fsmone/rest/"";$u=LWP::UserAgent->new(ssl_opts=>{verify_hostname=>0},cookie_jar=>{});$u->post($l.""fund/get-factsheet?paramSedolnumber="".shift)->decoded_content()=~/latestNavPrice.+?showDate.:(\d+).+?bidPrice.:([0-9.]+)/;print $2."";"".$1;' " + sCode
   sResult = execShell(sCmd, lExitCode)

   DownloadFromFSMMac = sResult

End Function