Need to take a JSON feed from an API and then format nicely in an Excel sheet. I have tested and I am able to issue the API call via [url removed, login to view] and capture the returning JSON response. I’d like have someone write some Excel VBA to automate this. It should work as follows:
#1. ASK FOR DATE: When I open the xlsm file, it should show a dialogue asking for the date of the data that I want, and default to the current system date. This date string needs to get inserted into the API URL call.
#2. CREATE NEW SHEET: Then when I hit enter on the date dialog box, the program will create a sheet/tab with the date as the sheet/tab name. If there is an existing sheet/tab with that name, it should delete the existing sheet/tab and create a new one.
#3. ISSUE API/GET DATA: Then the program will issue the URL to API call. (see API call at end) The data returned is in the format as in the [url removed, login to view] file (attached). There are just 3 fields/columns from the JSON response that I need. The program you write should be able to handle a response anywhere from 0-100 lines/rows, depending on the JSON data returned.
#4. FORMATTING: Then there needs to be a bit of formatting in the sheet. I have provided a sample of what I’d like the ending sheet to look like. Here are the things that need to be done: Change the name of the column headings (from the JSON response), add 4 other column headings. Add a new row 1 and insert the date that this data is for. Format the Row 2 column headings, highlight all the data and put border lines around. Format the “Start Time” column to turn the “date/time” string from the JSON response into just a 12-hr time. I have done all of these things in the attached workbook [url removed, login to view] so you can see for yourself.
API call: Here is the API call. There are some private keys, so I can’t post a working URL here, but you have the format of the API and you have the JSON response in the attached file.
[url removed, login to view]:00:00-04:00&endTime=2016-02-28T23:30:00-04:00
11 freelancere byder i gennemsnit $39 på dette job
Good excel skills. Focus on details that help you to entry correct data. Clear and formatted excel tables will help you to manage your data more effectively and efficiently.