Get Financial stats with Alpha Vantage

If you want to create your stock portfolio tool, you can do it with dashdash.

For this purpose, we will be using the Alpha Vantage API. The API is very simple, so we can start right away.

We will use the first row to insert the API Key.

  • A1: API Key
  • B1: T49UXP...

B1 is your API Key, which you can get from the main page of Alpha Vantage after filling out a few questions… You don’t even need to create an account.

Moving down, we will use the first column to identify the crypto asset.

  • A3: Asset
  • A4: MSFT
  • A5: AAPL

In the 2nd column, we’ll make the request to the Alpha Vantage API. We call the endpoint using GET() to get a specific asset. Cells B4 and B5 return a JSON structure, something with lots of {} and [ ].

In the 3th column, we’ll get the Last refresh of the data, which is a field inside the JSON returned on column B. We use function PARSE() that fetches specific data inside a JSON. The results of this API have the following structure: [{},{},{}]. We’re information inside “Meta Data”, specifically the date inside “3. Last Refreshed”. Because those names have spaces in them, we need to use the parse with the following notation:

  • C3: Last refresh
  • C4: =parse(B4,"['Meta Data'].['3. Last Refreshed']")
  • C5: =parse(B5,"['Meta Data'].['3. Last Refreshed']")

In the last column, D, we will get the day close price.

  • D3: Close
  • D4: =parse(B4,"['Time Series (Daily)'].['"&C4&"'].['4. close']")
  • D5: =parse(B5,"['Time Series (Daily)'].['"&C5&"'].['4. close']")

This is what the app looks like:

This is the basic fin-tracker.

Automatically refresh data

You can automate the info refreshing. You can in principle just place a REPEAT(GET(...),60) inside each request cell and your function would repeat every 60 minutes. However, you should avoid using too many REPEAT()s as they are very expensive. So you should have only one REPEAT(1, 60), lets say in cell E1 and then point at the cell inside each request cell, for example IF(E1, GET(...), "Not Running"). Because every time E1 repeats the value 1, and 1 is TRUE, then it will refresh the data request in GET().

1 Like

i am getting this error

@amit1234 can you send us the formula you used in C4, so we can evaluate, please?