Automate Data From an API

Hi wonder if anyone can assist

I have a spreadsheet (attached ) which collects start and end times for ISS Passes - a user manually enters,start and End times in to Col D and E

Col, F,G,H and I are calculated within excel

Col I produces a web reference like

https://api.wheretheiss.at/v1/satell...s=1593336173.5

If paste this into a browser it will return information in this format

[{"name":"iss","id":25544,"latitude":48.179453792556,"longitude":-6.2230817001613,"altitude":422.01090410959,"velocity":27593.956540979,"visibility":"daylight","footprint":4517.6799214176,"timestamp":1593336173,"daynum":2459028.8908912,"solar_lat":23.24364499358,"solar_lon":40.127070726464,"units":"kilometers"}]

I then manually cope the Latitude and longitude information into col J and K

I do this paste into the browser and then copy and paste browser output to the xls


Great for a few rows but eventually I will have hundreds of rows, I'm thinking how to use the Date From Web Function within excel to automate this process line by line. Any pointers really appreciated

Regards
John B

John,

what you need isn't really clear, as how you described it. yes you can get data from the web via excel, however it doesn't have the ability, as far as I know, for you to simply push a button and get the program to go to ''x'' number of pages in one operation and DL the info from each webpage. however, you could probably get that process done by writing a loop in code and using the get data from web feature, over and over again.

what do you mean by this?

Col I produces a web reference like

furthermore, what do you mean by this?

If paste this into a browser it will return information in this format

[{"name":"iss","id":25544,"latitude":48.179453792556,"longitude":-6.2230817001613,"altitude":422.01090410959,"velocity":27593.956540979,"visibility":"daylight","footprint":4517.6799214176,"timestamp":1593336173,"daynum":2459028.8908912,"solar_lat":23.24364499358,"solar_lon":40.127070726464,"units":"kilometers"}]

if you're saying that the data returned will be like in the URL you posted, those two formats and return data values are *not* the same between the 2.

Hi Adam

Sorry if i was not clear enough

so each row produces a unique URL ie:https://api.wheretheiss.at/v1/satell...s=1593336173.5 and each unique will bring back the unique results ie:

[{"name":"iss","id":25544,"latitude":48.179453792556,"longitude":-6.2230817001613,"altitude":422.01090410959,"velocity":27593.956540979,"visibility":"daylight","footprint":4517.6799214176,"timestamp":1593336173,"daynum":2459028.8908912,"solar_lat":23.24364499358,"solar_lon":40.127070726464,"units":"kilometers"}]

and from these i want to extract these latitude":48.179453792556,"longitude":-6.2230817001613

I am more than happy to do a few actions for each row to get the results and have them added to col j and K - currently I paste the unique URL into my web browser and then copy and paste the results into col j and k in the spreadsheet

I have never really played around with macros but maybe thats a simpler route to follow ?


Anyway hope this makes it a little clearer ?

Regards

John

well John,

I ran on a test on a page that you posted. this one: https://api.wheretheiss.at/v1/satell...s=1593336173.5

and I went through the motions with powerquery. now keep in mind, I'm no PQ expert. however, after doing the basic moves with the tool, the following images depicts what came out of it. PQ is looking at the data as JSON format. and the source code of that page suggests that it IS actually that type. and it is certainly NOT in a table format via HTML, which is probably why I didn't get actual values parsed out of it. Now of course, I used PQ's splitter as did not specify a delimiter. and that could've caused the result i got. because JSON data is always delimited by something. Have you done this already?

hi i tried something similar and it did list the results which I could see in a table, i think i did a close and load function, this may well be the way for me and as its all in excel i can record a macro do do it, i will try this and let you know

thanks

John

yes you can record it. and yes, I did the "close and load" too

Hi
So i managed to records the steps into a macro

1 - Copy the url from I3

2 - Paste the url into data from Web (basic)

3 - Click on Record

4 - Click on convert to table

5 - Click on Close and Load

6 - copy lat and lng from the new table into cells J3 and K3

7 - Delete the sheet that held the imported data

8 - cut row 3 from sheet Source and past into sheet updated



however when I run the macro Radio it stops with an error as its hard coded the url pastred in Step 1, I had hoped it would record that the url came from I3 rather than the actual url


I think im close but help would be appreciated


Regards
John B

Hi thats interesting can this line strURL = "https://api.wheretheiss.at/v1/satellites/25544/positions?timestamps=1593336173.5" be changed to get the URL from a Cell thats on a different sheet ?

John

@haluk

Thanks works a treat, I have added a second macro which then moves the data around and it seem to work a treat


I have noticed an error (my doing) on the source sheet when i concat in col I and add col H its not exact it seems to add .5 sometimes consequently the data returned is not as expected


John

Actually, your data in Column H has a formatting to show "0-zero" decimal places. If you change this to "2 decimal places", there will be no difference between Columns H and I.

@Haluk

Thanks but still a little confused

Row 3 Col H is 1593759673 and that is the correct number

Row 3 Col I shows 1593759673 but If I click on the cell it shows 1593759672.5 which is incorrect and so the incorrect value is passed to the URL etc


John B

Well, in cell H3 try this and drag the formula down ...

Please Login or Register  to view this content.

Worked a treat - thank you for your help its really appreciated

Thanks to all for the help

You're welcome...

Hi @ck76 the code you produced does not seem to work with Excel 2010 - is there anything i can do to fix this ?

Regards
John B