1

I'm trying to get stock prices on Tokyo exchange using VBA. I have searched for quite a number of API but none of them has Tokyo exchange's data, except finance.google.com. But google finance has stopped working and no longer return data in json format so i have to make use of what's left, to extract data from the string returned.

Below is the example of the string returned.

EXCHANGE%3DTYO
MARKET_OPEN_MINUTE=540
MARKET_CLOSE_MINUTE=900
INTERVAL=86400
COLUMNS=DATE,CLOSE,HIGH,LOW
DATA_SESSIONS=[MORNING,540,690],[AFTERNOON,750,900]
DATA=
TIMEZONE_OFFSET=540
a1522216800,2313,2324,2240
1,2298,2350,2242
2,2325,2336,2300
5,2252,2333,2246
6,2229,2257,2216 <--
7,2222,2238,2214
8,2262,2267,2220
9,2293,2313,2249
12,2338,2351,2300
13,2373,2392,2351
14,2386,2396,2323
15,2368,2395,2335
16,2391,2398,2375
19,2420,2439,2389
20,2378,2419,2363
21,2411,2428,2389
22,2384,2422,2381
23,2407,2426,2386
26,2437,2438,2389
27,2454,2468,2443
28,2453,2456,2418
29,2437,2422,2443 <<--

The URL for your reference http://finance.google.com/finance/getprices?x=TYO&q=9143&i=86400&f=d,c,h,l

a1522216800 is the timestamp and the first number of the following lines are intervals in days. 1 being 1 day after a1522216800. The timestamp after the alphabet "a" seems to change with days whenever I call that URL.

I use InStr() looking for "a" and Mid() to extract the timestamp and convert it into date format and add the day interval to the extracted date to get my specific date. Here's the problem, for example, I want to get 29 days from the timestamp, indicated by <<--, when I use the same InStr() to look for "29," VBA returned string position at <--, as they have the same "29," in that line. How can I get my intended date?

My life would be easier if the response from finance.google.com is in json format. I don't suppose there is a way to convert these string into json are they?

T.PC
  • 59
  • 8
  • Try to look for `29,` with the coma? – Foxfire And Burns And Burns Apr 26 '18 at 09:26
  • [Use a regex](https://stackoverflow.com/q/22542834/11683) in the multiline mode and look for a match in the beginning of a line. Then again, you might want to [stop using it altogether](https://stackoverflow.com/a/46073520/11683). – GSerg Apr 26 '18 at 09:31
  • 1
    I am presuming that there is a new line character at the end of each line and that's why the data is displayed in different lines? If so, use `Split` to capture each line in an array (separating them by the new line character). You can then get the characters until there is a **,** in each item in an array. Use this to check your number – Zac Apr 26 '18 at 10:09
  • @Foxfire And Burns And Burns If I search for "29", VBA would return random string position as there are many numbers there and surely there are some "29" hidden somewhere around. That is what I thought at first. So I included the comma thinking very surely it would get me to the last line and here I am with this problem. What's the odds eh – T.PC Apr 26 '18 at 12:17
  • @Zac The response text returned to VBA is not display as above but in a single line. (Weird it is displayed as above when I pass it into MsgBox but appear as a single line string in local window) So I can't use `Split` as there is no obvious delimiter separating the "29" – T.PC Apr 26 '18 at 12:32
  • There has to be something that causing it to display the way it is. There might be a hidden character? If you can identify what that character is, you could use that in your `Split` – Zac Apr 26 '18 at 13:05

1 Answers1

0

Take a look at the below example:

Sub Test()

    Dim s
    Dim a

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://finance.google.com/finance/getprices?x=TYO&q=9143&i=86400&f=d,c,h,l", False
        .send
        s = .responseText
    End With
    a = Split(s, vbLf & "29,", 2)
    a = Split(a(1), vbLf, 2)
    a = Split(a(0), ",")

    MsgBox Join(a, vbCrLf)

End Sub
omegastripes
  • 12,351
  • 4
  • 45
  • 96