Api in every row of excel.

Question

Hi&nbsp;&nbsp;Currently I have an excel file with the following columns.&nbsp;MFund Code&nbsp; &nbsp; &nbsp;I Bought at&nbsp; &nbsp; I Mutual fund name&nbsp; &nbsp; &nbsp; &nbsp;I latest Price&nbsp; &nbsp; &nbsp;112092&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; I 100.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;I Fidelity world fund&nbsp; &nbsp; &nbsp; I 112.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;I&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100.0056432&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; I 25.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;I Stanley index fund&nbsp; &nbsp; &nbsp; &nbsp;I 20.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; I&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25.00&nbsp;There is a website which provides access to latest price through API.&nbsp;There arre many Mfundcodes so I use a text file to be read in a python program and extract the data.&nbsp;Following is the main part of the program.&nbsp;for x in f:# Add number variable to the api call&nbsp; &nbsp;x = x[:6]&nbsp; &nbsp;api = f"https://api.mfapi.in/mf/{x}/latest"# Get text data from api call&nbsp; &nbsp;idata = requests.get(api).text;&nbsp; # Convert json data into dictionary format&nbsp; &nbsp;d = json.loads(idata)# Get the first record of data&nbsp; &nbsp;&nbsp; &nbsp;if not d['data']:&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; exit&nbsp; &nbsp;else:&nbsp; &nbsp; &nbsp; l = d['data'][0]&nbsp; &nbsp; &nbsp; k.write(f"{x} {l['nav']} {l['date']} 
")&nbsp;Later I import this text file into the excel for further calculations.&nbsp;&nbsp;I was wondering if there is a way to have these values load into the excel columns&nbsp;regardsJyothsna

m_tarler · Accepted Answer

you can use Excel built in tools to pull the data. you can create an external link using Data->get data from webpage and pull a table of data but not sure how well that would work here.

alternatively, you can also use inline formula like this:

=--TEXTBEFORE(TEXTAFTER(WEBSERVICE("https://api.mfapi.in/mf/"&$A2&"/latest"),"nav"":"""),"""}],")

where A2 is where the Mfund Code is located

NOTE: I do NOT see that Stanley Index fund anywhere in that api

Forum Discussion

Api in every row of excel.

2 Replies