Partial JSON File Import into Stata

[Giles German]

Dear Stata forum,

I want to download the following JSON file into stata "https://query1.finance.yahoo.com/v8/finance/chart/AAPL?symbol=AAPL&period1=1501560000&period2=999999 9999&interval=3mo&events=div%2Csplit"

It is just the standard yahoo api and I have run into problems using the insheetjson command I keep getting the following error variable result not found


I have been using the following code...

clear

 gen amount = ""
 gen date = ""
  
 insheetjson result amount date using "https://query1.finance.yahoo.com/v8/finance/chart/AAPL?symbol=AAPL&period1=1501560000&period2=9999999999&interval=3mo&events=div%2Csplit", table(dividends) col("date" "amount")

This is the bit of the JSON file I am interested in...

{"dividends":{"1509508800":{"amount":0.63,"date":1 510324200},"1517461200":{"amount":0.63,"date":1518 186600},"1501560000":{"amount":0.63,"date":1502371 800},"1525147200":{"amount":0.73,"date":1526045400 }} Thanks

Giles

[Giles German]

I have also had a go at doing this i the way the Robert Picard shows in one of his posts but it is still not working. Can you help please Robert?

Giles

clear
local vnames date1501560000 date1509508800 
foreach v in `vnames' {
    gen `v' = ""
}

* loop over all observations; query and store the response

tempfile fromapi
local url `"https://query1.finance.yahoo.com/v8/finance/chart/AAPL?symbol=AAPL&period1=1501560000&period2=9999999999&interval=3mo&events=div%2Csplit"'

copy `"`url'"' "`fromapi'", replace
    
scalar response = fileread("`fromapi'")
    
    foreach v in `vnames' {
        if ustrregexm(response,`""`*v'":"(.+?)""') {
            local s = ustrregexs(1)
            replace `v' = `"`s'"' in `i'
        }
    }

[Robert Picard]

I'm not familiar with the Yahoo API and finance data so this is unchartered waters for me. If you reformat the data you are interested in to be more readable, you get:

{"dividends":{
"1509508800":{"amount":0.63,"date":1510324200},
"1517461200":{"amount":0.63,"date":1518186600},
"1501560000":{"amount":0.63,"date":1502371800},
"1525147200":{"amount":0.73,"date":1526045400}}},

I don't quite understand the two set of dates but I'll assume from #2 that you want to target 1501560000 and 1509508800. In the following example, the date to the left is stored in the variable period and the one to the right in the variable date. I also assume that you will want to repeat the query for several ticker symbols. So the first part is to form all pairwise combination of ticker and desired dates using cross. Then runby (from SSC) is used to query the API once per ticker and extract the desired information for each value of period.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str4 ticker
"AAPL"
"KO"
"TXN"
"DIS"
end
save "tickers.dta", replace

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 period
"1501560000"
"1509508800"
end
cross using "tickers.dta"
sort ticker period


program drop _all
program get_data
    local ticker = ticker[1]
    local query "https://query1.finance.yahoo.com/v8/finance/chart/`ticker'?symbol=`ticker'&period1=1501560000&period2=9999999999&interval=3mo&events=div%2Csplit"
    copy "`query'" "response.txt", replace
    scalar response = fileread("response.txt" )
    
    gen amount = ustrregexs(1) if ustrregexm(response,`"""' + period + `"":\{"amount":(.+?),"')
    gen date   = ustrregexs(1) if ustrregexm(response,`"""' + period + `"":\{"amount":.+?,"date":(.+?)\}"')
end

runby get_data, by(ticker) verbose

list, sepby(ticker)

and the list results:

. list, sepby(ticker)

     +-------------------------------------------+
     |     period   ticker   amount         date |
     |-------------------------------------------|
  1. | 1501560000     AAPL     0.63   1502371800 |
  2. | 1509508800     AAPL     0.63   1510324200 |
     |-------------------------------------------|
  3. | 1501560000      DIS                       |
  4. | 1509508800      DIS     0.84   1512743400 |
     |-------------------------------------------|
  5. | 1501560000       KO     0.37   1505395800 |
  6. | 1509508800       KO     0.37   1512052200 |
     |-------------------------------------------|
  7. | 1501560000      TXN     0.62   1509370200 |
  8. | 1509508800      TXN     0.62   1517322600 |
     +-------------------------------------------+

.

[Giles German]

This is brilliant thanks Robert. You should make an ado file with this as it works so well.