1

I have two objects and I want to calculate the difference in values for each value.

The code is now:

Function CALBETA(CodeOne As String, CodeTwo As String) As Double
Dim onedata As Object
Dim Twodata As Object

Set onedata = ConnectToBloombergTwo(CodeOne)

Set Twodata = ConnectToBloombergTwo(CodeTwo)

End Function

They are based on another function:

Public Function ConnectToBloombergOne(Code As String) As Object

    Dim sUrl As String
    Dim rawJson As Dictionary
    Dim dataRequest As WinHttp.WinHttpRequest
    Dim Json As Object
    Dim FetchedData As String

    sUrl = "http://www.bloomberg.com/markets/api/bulk-time-series/price/" & Code & "?timeFrame=3_YEAR"

    Set dataRequest = New WinHttp.WinHttpRequest

    With dataRequest
        .Open "GET", sUrl, True
        .Send
        .WaitForResponse
        FetchedData = .ResponseText
    End With

    FetchedData = Right(FetchedData, Len(FetchedData) - 1)
    FetchedData = Left(FetchedData, Len(FetchedData) - 1)

    Set Json = JsonConverter.ParseJson(FetchedData)

    Set ConnectToBloombergOne = Json.Item("price")

End Function

So, the idea is to compare two different stocks from Bloomberg. Therefore, I need to be able to calculate the difference in the stock price.

How can I do that?

mgae2m
  • 1,134
  • 1
  • 14
  • 41
Jacob Kildetoft Henriksen
  • 11
  • 1
  • Which particular properties of those objects do you want to find the difference in? Also, your `CALBETA` function is invoking the `ConnectToBloombergTwo` function, but you have shown the `ConnectToBloombergOne` function. What is the difference between the one you are calling and the one you have posted? – YowE3K Sep 25 '17 at 08:50
  • Want to finde the difference in stock price on the difference days: Json.Item("price") Ahh. It's a mistake. ConnectToBloombergTwo just gets 5 years instead of 3 years. – Jacob Kildetoft Henriksen Sep 25 '17 at 10:37
  • What object does `Json.Item("price")` return? This is not a VBA question, but a JSON question, specifically whatever JSON library you are using with Excel. – John Alexiou Sep 25 '17 at 13:01
  • Related, or possible duplicate??? [How to parse JSON with VBA without external libraries](https://stackoverflow.com/q/19360440/380384) – John Alexiou Sep 25 '17 at 13:03
  • Possible duplicate of [VBA getting values from a collection?](https://stackoverflow.com/questions/26229563/vba-getting-values-from-a-collection) – John Alexiou Sep 25 '17 at 13:08
  • You do can as follows: (That is VBA) Set pricedata = ConnectToBloomberg(Code) For Each Item In pricedata Count = Count + 1 DailyReturn = Item("value") - LastPrice If LastPrice > 0 Then DailyReturnPercent = DailyReturn / LastPrice End If – Jacob Kildetoft Henriksen Sep 25 '17 at 18:10

0 Answers0