' Include VBAJSON: https://medium.com/swlh/excel-vba-parse-json-easily-c2213f4d8e7a
Function getPriceStashawayMY(fundName As String) As Scripting.Dictionary
   
   'JSON PARAMETERS (v2.8)
   Dim allheaders As String
   Dim token As String
   
   Dim json As Object '(v3.1)
   Dim jsonStr As String '(v3.1)
   Dim payload As String
   Dim query, email, password, deviceCookie As String
   Dim goalId As String
   
   ' Load Parameters
   query = ThisWorkbook.Sheets("Reference Data").Range("query_overviewPerformance").Value
   email = ThisWorkbook.Sheets("Reference Data").Range("keyStashAwayUsername").Value
   password = ThisWorkbook.Sheets("Reference Data").Range("keyStashAwayPassword").Value
   deviceCookie = ThisWorkbook.Sheets("Reference Data").Range("keyStashAwayCookie").Value
   
   
   ' Convert fundName to goalId
   goalId = convertStashawayFundnametoGoalid(fundName)
   
   Set dic = CreateObject("Scripting.Dictionary")
   Set XML = CreateObject("winhttp.winhttprequest.5.1")
   XML.Open "POST", "https://api.stashaway.my/api/v2/auth/login"
   On Error GoTo ErrHandler:
   
   ' initiate login with username and password
   payload = "{""email"":""" & email & """,""password"":""" & password & """,""userData"":{""deviceCookie"":""" & deviceCookie & """}}"
   XML.SetRequestHeader "Content-Type", "application/json"
   XML.SetRequestHeader "x-client-identifier", "[email protected]"
   XML.Send (payload)
   On Error GoTo ErrHandler:
   
   ' Get all headers
   allheaders = XML.GetAllResponseHeaders
   
   ' Get JSON response (containing sensitive information!! username, personal details, etc.)
   jsonStr = XML.responseText
           
   ' Extract token value
   Set dic = CreateObject("Scripting.Dictionary")
   Set dic = VBAJSON.ParseJSON(jsonStr)
   On Error GoTo ErrHandler:
   
   'Debug.Print VBAJSON.ListPaths(dic)
   token = dic("obj.token")
   
   
   
   ' Purge jsonStr and dic off sensitive data
   jsonStr = ""
   allheaders = ""
   payload = ""
   Set dic = CreateObject("Scripting.Dictionary")
     
   
   
   ' Once we got the token can query their graphql
   payload = "{""operationName"":""overviewPerformance"",""variables"":{""displayCurrency"":""MYR"",""goalId"":""" & goalId & """},""query"":""" & query & """}"
   XML.Open "POST", "https://api.stashaway.my/graphql/overviewPerformance"
   On Error GoTo ErrHandler:
   
   XML.SetRequestHeader "Content-Type", "application/json"
   XML.SetRequestHeader "x-client-identifier", "[email protected]"
   XML.SetRequestHeader "authorization", "Bearer " & token
   XML.Send (payload)
   On Error GoTo ErrHandler:
   
   ' Get all headers
   allheaders = XML.GetAllResponseHeaders
   
   ' Get JSON response
   jsonStr = XML.responseText
       
   ' Extract JSON value
   Set dic2 = VBAJSON.ParseJSON(jsonStr)
   On Error GoTo ErrHandler:
   
   'Debug.Print VBAJSON.ListPaths(dic2)
   
   
   dic.Add "currentNav", dic2("obj.data.goal.portfolioPerformance.currentNav")
   On Error GoTo ErrHandler:
   
   dic.Add "totalDeposits", dic2("obj.data.goal.portfolioPerformance.totalDeposits")
   dic.Add "totalWithdrawals", dic2("obj.data.goal.portfolioPerformance.totalWithdrawals")
   dic.Add "netDeposits", dic("totalDeposits") - dic("totalWithdrawals")
   
   
   
   dic.Add "textCurrency", dic2("obj.data.goal.portfolioPerformance.currency")
   dic.Add "textTxAmt", dic("currentNav") / dic("netDeposits")
   dic.Add "textPrice", dic("currentNav") / dic("netDeposits")
   dic.Add "textDate", Format(dic2("obj.data.goal.portfolioPerformance.latestFxRate.quoteDate"), "dd-mmm-yyyy")
   
   
   'MsgBox "currentNav: " & currentNav & vbCrLf & _
               " | totalDeposits: " & dic("totalDeposits") & vbCrLf & _
               " | totalWithdrawals: " & dic("totalWithdrawals") & vbCrLf & _
               " | netDeposits: " & dic("netDeposits") & vbCrLf & _
               " | textPrice: " & dic("textPrice") & vbCrLf & _
               " | textDate: " & dic("textDate") & vbCrLf & _
               " | (currentNav / netDeposits): " & dic("currentNav") / dic("netDeposits")
       
       
       
   Set getPriceStashawayMY = dic
   
ErrHandler:
   Resume EndHandler
   
EndHandler:
   Exit Function
End Function

Function convertStashawayFundnametoGoalid(fundName As String) As String
   '//TODO: dynamic conversion from excel fund row into StashAway's unique goal ID
   ' pseudocode:
   ' 1) get currentRow() > fundName
   ' 2) look up reference table for fundName, capture cell position (column & row)
   ' 3) change column to the goalId with same Row, fetch its value. store as variable for this run
   Dim goalId As String


   Dim WsRefD As Worksheet '(v2.5)
   Set WsRefD = ThisWorkbook.Sheets("Reference Data") '(v2.5)

   
   For Each c In WsRefD.Range("tblMappingStashAway[StashAway Shortcode]").Cells
       ' find the matching one
       If c.Value = fundName Then '(v2.5)
           'Note: c.Row = current row value (1 ~ xxxx), but the range table uses 0  ~ xxxx
           convertStashawayFundnametoGoalid = WsRefD.Range("tblMappingStashAway[StashAway GoalId]")(c.Row - 1).Value
           Exit For
       End If
   Next
End Function

query overviewPerformance($goalId: ID!, $lookBackPeriod: LOOK_BACK_PERIOD, $displayCurrency: String) {\n  goalsCount\n  goal(_id: $goalId) {\n    _id\n    displayName\n    portfolioPerformance(displayCurrency: $displayCurrency, lookBackPeriod: $lookBackPeriod) {\n      portfolioId\n      timeWeightedReturn\n      moneyWeightedReturn\n      currentNav\n      totalDeposits\n      totalWithdrawals\n      totalDividends\n      netDeposits\n      netReturns\n      currency\n      latestFxRate {\n        quoteCcy\n        baseCcy\n        rate\n        quoteDate\n        __typename\n      }\n      __typename\n    }\n    __typename\n  }\n}\n