In my blog post entitled Using ChatGPT As A Data Analysis Assistant, I described a Stock Portfolio Prediction Tracker in which I’d used the Data > From Web feature in Excel to scrape the Current Market Price of various stocks from Yahoo! Finance website.
While I was quite satisfied with scraping in the short term, I was looking to replace it with API so that I could eliminate manual intervention everytime I wanted to compute the Net Asset Value of the portfolio.
I got many good suggestions of suitable API providers from readers e.g. AlphaVantage, Finnhub, ICICIdirect Breeze, MarketStack, Twelve Data, Zerodha Kite Connect, etc.
After a quick-and-dirty research, I settled on MarketStack.
In this post, I’ll walk through the process of incorporating MarketStack API to automatically fetch the CMPs in my prediction tracker.
I signed up for MarketStack and got the API key.
I tried it on the browser:
https://api.marketstack.com/v1/eod/latest?access_key=123456789123456789abcdefghijilm&symbols=TCS.XNSE
It worked as advertised and I was able to see the EOD price of the said stock:
However, when I entered it into the Excel cell, I got an error. Per ChatGPT:
The issue you’re facing in Excel likely occurs because Excel doesn’t natively interpret JSON data from the API URL into a table format.
In other words, when I used scraping, Excel was able to pick up the content of the website as-it-is and download it into its cells whereas, when I used API, the website packaged its data in the form of JSON, which my Excel was not able to parse.
This reminded me of Byrne Hobart’s observation about scraping versus API.
When you use scraping, you can get all the info that the logged user can access whereas, when you use API, you’re limited to what the bank provides via API.
With some more back-and-forth interactions with ChatGPT, I was able to localize the problem to my rather oldish version of Excel.
In the old days of StackOverflow, I’d have given up at this point after being told by its condescending denizens to use the latest version of Excel and not waste their time.
However, ChatGPT was very empathetic (see footnote 1). It gave me several ways to check whether it was possible to go forward on my version of Excel e.g. Test another API endpoint (e.g., a public one like OpenWeatherMap or JSONPlaceholder).
When none of them worked, Chat suggested that, if I switched to Google Sheets, I’d be assured of getting the latest version since it was a SAAS software.
That was a great suggestion, one that I’m sure nobody in StackOverflow would have made.
I migrated my Excel spreadsheet to Google Sheets. I replaced the Excel formula with IMPORTDATA, the equivalent function in Google Sheets. I got an error message.
ChatGPT told me that the
… IMPORTDATA function does not support JSON files directly. Since the Marketstack API returns data in JSON format, you will need to use a custom solution like the IMPORTJSON function via a script.
… and went on to give me the code for the script and step-by-step instructions to link it to the spreadsheet by using the Extensions > Apps Script command. After I saved the script on the Apps Script Editor, I came back to the main sheet view and entered the following formula in the cell for CMP:
=IMPORTJSON(“https://api.marketstack.com/v1/eod/latest?access_key=123456789123456789abcdefghijilm&symbols=TCS.XNSE”)
It worked fine!
I noticed that the EOD price appeared on cell B8 of the output of the IMPORTJSON function. I asked ChatGPT it was possible to combine this cell address and the IMPORTJSON formula into a single formula. In addition, I wanted to avoid hardcoding the ticker symbol and asked Chat if it could parametrize the formula by making it reference the cell where the ticker symbol was entered.
Chat said yes to both questions, and gave me the following formula:
=INDEX(IMPORTJSON(“https://api.marketstack.com/v1/eod/latest?access_key=123456789123456789abcdefghijilm&symbols=” & D9), 8, 2)
B8 translated to 8,2 i.e. the eighth row and second column (à la matrix notation).
I then had Chat fetch me the ticker symbols for all the 27 scrips in my portfolio. It did a perfect job of it and I was able to build out the complete model for all the stocks.
Taking a leap of faith that the API would return the CMP for all scrips in the same cell B8, I copied this formula by dragging this cell down from the first row to the remaining 26 rows in my sheet. (As readers of Using ChatGPT As A Data Analysis Assistant might recall, that was a leap too far when I used scraping.)
When I refreshed the page, some cells had the CMP but other cells displayed an error message saying that I’d busted the rate limit of 5 API requests per second. Apparently, Google Sheets made all the 27 API calls at once, which was the root cause of this error. To solve this issue, ChatGPT told me to add an apiThrottleInterval command in the script.
I wasn’t sure if I should rely entirely on Chat at this point. I emailed the Tech Support of MarketStack. The CSR proposed the same solution as ChatGPT, so I went ahead with it.
When that failed to work, ChatGPT suggested a few more changes in the IMPORTJSON script and asked me to upgrade to the paid version of MarketStack.
I took both the actions and it worked fine! I got the CMP for all 27 scrips.
YaaY, it worked now, I got the CMP for all 27 scrips.
Mission accomplished!
Two weeks later, when I opened the sheet, I got CMPs for all but two scrips. According to the error message in the cells containing those two scrips, there was some problem with the format of the JSON object returned by the MarketStack API only for them. However, neither I nor ChatGPT could find any difference between the JSON output of the working API calls versus the errant ones. Happy that the procedure had worked once, I didn’t bother to spend any more time troubleshooting this isolated problem, and left it at that.
Lo and behold, the next time I opened the Google Sheet, the error message mysteriously vanished and I could see all the 27 CMPs!
So it’s Saul Goodman!
On this instance, I was able to get all this coding work done without coders.
OTOH, coders do way more work than what I did but OTOrH, I used ChatGPT instead of the more powerful coding assistants like Cursor, Replit, and so on.
So the jury is still out on the question I asked in my blog post Will ChatGPT Kill Coders?.
FOOTNOTE(S):
- This resonates strongly with anecdata from Air Canada and Allstate that genAI / ChatGPT is more empathetic than humans.