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 - 13 July 2015

   Set WS = Worksheets("Investment Details")
   Set htm = CreateObject("htmlFile")
   'Set XML = CreateObject("msxml2.xmlhttp") 'Removed on v2.1
   Set XML = CreateObject("winhttp.winhttprequest.5.1") 'Added on v2.1
   
   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
           
           'Download the correct price list'
           XML.Open "GET", "http://www.fundsupermart.com.my/main/fundinfo/dailyPricesHistory.tpl?id=" & fundName, False
           On Error GoTo EndHandler: ' terminate if error in connecting
           
           XML.send
           'If XML.ReadyState = 4 And XML.Status = 200 Then 'Removed on v2.1
           If XML.Status = 200 Then 'Added on v2.1
               htm.body.innerHTML = XML.responsetext
               
               On Error GoTo ErrHandler:
               'On Error Resume Next:
                   'Get all 4 cells in the first row of data'
                   Set FirstRow = htm.getElementsByTagName("tr")(2).Children
                   
                   'Update date value from 4th table cell of first row'
                   WS.Cells(i, 4).Value = FirstRow(3).innerText
                   
                   'Update NAV value from the DIV element in the 3rd table cell of first row'
                   WS.Cells(i, 9).Value = FirstRow(2).FirstChild().innerText
                   
               ' successful update counter +1
               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
       End If
   Next i
   
   '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
   
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