API from Alpha Vantage. How to download and organize data in Stata?

[Francois Durant]

Hi,

I would like to download data via API. I am using the website Alpha Vantage. I have gotten my API key and I also have a sample of the JSON output that I enclose at the end of the message in case this is useful. I have the tried to use insheetjson (from SSC) using the help file but I could not get it to work. Could you please help me? Thanks

Here is a sample of the structure of the data:

 
timestamp
open
high
low
close
adjusted close
volume
dividend amount

3/20/2020
165.31
175
135
137.35
137.35
1.11E+09
0

2/28/2020
170.43
190.7
152
162.01
162.01
8.88E+08
0.51

1/31/2020
158.78
174.05
156.51
170.23
169.7677
5.56E+08
0

12/31/2019
151.81
159.55
146.65
157.7
157.2717
4.53E+08
0

11/29/2019
144.26
152.5
142.965
151.38
150.9689
3.93E+08
0.51

10/31/2019
139.66
145.67
133.22
143.37
142.4949
5.61E+08
0

9/30/2019
136.61
142.37
134.51
139.03
138.1814
4.78E+08
0

8/30/2019
137
140.9383
130.78
137.86
137.0186
5.86E+08
0.46

7/31/2019
136.63
141.675
134.67
136.27
134.9748
4.85E+08
0

Sample of the JSON output:

 
 "Meta Data": {         "1. Information": "Monthly Adjusted Prices and Volumes",         "2. Symbol": "MSFT",         "3. Last Refreshed": "2020-03-20",         "4. Time Zone": "US/Eastern"     },     "Monthly Adjusted Time Series": {         "2020-03-20": {             "1. open": "165.3100",             "2. high": "175.0000",             "3. low": "135.0000",             "4. close": "137.3500",             "5. adjusted close": "137.3500",             "6. volume": "1112254000",             "7. dividend amount": "0.0000"         },