excel to json table shape

Question

I have a dataset in a json file that I need to update. The data is collected from an excel add-in therefore I need to create an excel table of a certain shape to much my current json file. How should I make my table to get the bellow json file after converting my excel file to json?

The json file:

{"AAL": {"year": [2012, 2013, 2014, 2015], 
 "eps": [-5.6, -11.25, 4.02, 11.39], 
 "revenue": [24855.0, 26743.0, 42650.0, 40990.0], 
 "op_revenue": [148.0, 1399.0, 4249.0, 6204.0]}, 
 "AAP": {"year": [2012, 2013, 2014, 2015], 
   "eps": [5.29, 5.36, 6.75, 6.45], 
   "revenue": [6205.003000000001, 6493.814, 9843.860999999999, 9737.018],
   "op_revenue": [657.315, 660.318, 851.71, 825.78]}, 
 "AAPL": {"year": [2013, 2014, 2015, 2016], 
    "eps": [40.03, 6.49, 9.28, 8.35], 
    "revenue": [171000.0, 183000.0, 234000.0, 216000.0], 
    "op_revenue": [48999.0, 52503.0, 71230.0, 60024.0]}, 
 "ABBV": {"year": [2013, 2014, 2015], "eps": [2.58, 1.11, 3.15], "revenue": [18790.0, 19960.0, 22859.0], "op_revenue": [5664.0, 3411.0, 7537.0]}, "ABC": {"year": [2013, 2014, 2015, 2016], "eps": [1.88, 1.22, -0.62, 6.73], "revenue": [87959.16699999999, 120000.0, 136000.0, 147000.0], "op_revenue": [898.3989999999999, 782.091, 422.22, 1525.774]}}

Answer 1

And why not edit JSON using a page like this ? Btw if you like it, mind ticking accept button under voting. Using nice answer how to iterate table made this snippet:

var jsonEl = document.getElementsByName("data")[0];
function saveJSON() {
    var table = document.getElementsByName("editor")[0].firstElementChild;
    var header = ["Ticker"], comp = {};
    for (var i = 0, row; row = table.rows[i]; i++) {
        var companyTicker = row.cells[0].innerText;
        for (var j = 1, col; col = row.cells[j]; j++) {
            if (i) {
                comp[companyTicker] = comp[companyTicker] || 0;
                json[companyTicker][header[j]][comp[companyTicker]] = getNumber(col.firstElementChild.value);
            } else {
                header.push(col.innerText);
            }
        }
        if(i) comp[companyTicker]++;
    }
    jsonEl.value = JSON.stringify(json, null, 2);
    var el = document.getElementsByName("editor")[0];
    el.removeChild(el.firstElementChild);
}
function buildTable(data) {
    var table = [], header = ["Ticker"];
    table.push(header);
    for (var comp in data) {
        var company = [];
        company.push(comp);
        for (var figures in data[comp]) {
            var column = header.indexOf(figures);
            if (column < 0) {
                header.push(figures);
                column = 0;
            }
            company.push(data[comp][figures]);
        }
        table.push(company);
    }
    var html = "<table border=1><tr><th>" + table[0].join("</th><th>") + "</th></tr>\r\n";
    for (var row = 1; row < table.length; row++) {
        var mainRow = table[row];
        for (var subRow = 0; subRow < mainRow[1].length; subRow++) {
            html += "<tr><th>" + mainRow[0] + "</th>";
            for (var col = 1; col < mainRow.length; col++) {
                html += "<td><input type=number value='";
                html += mainRow[col][subRow];
                html += "'></td>";
            }
            html += "</tr>\r\n";
            mainRow = table[row];
        }
    }
    html += "</table>\r\n"
    return html;
}
function getNumber(str) {
    return parseFloat(str);
}
function readData() {
    json = JSON.parse(jsonEl.value);
    var table = buildTable(json);
    document.getElementsByName("editor")[0].innerHTML = table;
}

table {
  border-spacing: 0px; /* small tricks 2 make rounded table simply or */
}
th {
  text-align: left; /* centered looks ugly */
  padding: 2px;
  background-color: #F0F0F0;
}
td {
    padding: 0px;
    background-color: #F0FFF0;
}
input[type=number] {
  margin: 0px;
  background-color: transparent;
  border-width:0px;
  border:none;
}

<input type="button" value="BuildTable" onclick="readData()">
<input type="button" value="SaveJSON" onclick="saveJSON()"><br>
<span name=editor></span>
<textarea name="data" style="width: 100%; height: 200px;">{
    "AAL": {
        "year": [2012, 2013, 2014, 2015],
        "eps": [-5.6, -11.25, 4.02, 11.39],
        "revenue": [24855.0, 26743.0, 42650.0, 40990.0],
        "op_revenue": [148.0, 1399.0, 4249.0, 6204.0]
    },
    "AAP": {
        "year": [2012, 2013, 2014, 2015],
        "eps": [5.29, 5.36, 6.75, 6.45],
        "revenue": [6205.003000000001, 6493.814, 9843.860999999999, 9737.018],
        "op_revenue": [657.315, 660.318, 851.71, 825.78]
    },
    "AAPL": {
        "year": [2013, 2014, 2015, 2016],
        "eps": [40.03, 6.49, 9.28, 8.35],
        "revenue": [171000.0, 183000.0, 234000.0, 216000.0],
        "op_revenue": [48999.0, 52503.0, 71230.0, 60024.0]
    },
    "ABBV": { "year": [2013, 2014, 2015], "eps": [2.58, 1.11, 3.15], "revenue": [18790.0, 19960.0, 22859.0], "op_revenue": [5664.0, 3411.0, 7537.0] }, "ABC": { "year": [2013, 2014, 2015, 2016], "eps": [1.88, 1.22, -0.62, 6.73], "revenue": [87959.16699999999, 120000.0, 136000.0, 147000.0], "op_revenue": [898.3989999999999, 782.091, 422.22, 1525.774] }
}</textarea>

---

And in XL (tested on 2010) using VBA-JSON

Sub Fill()
    Dim JsonText As String
    Dim Parsed As Dictionary

    file_name = "YourFile.json"
    my_file = FreeFile()
    Open file_name For Input As my_file
    i = 1
    While Not EOF(my_file)
        Line Input #my_file, text_line
        JsonText = JsonText + text_line
        i = i + 1
    Wend

    Set Parsed = JsonConverter.ParseJson(JsonText)

    Dim ticker, third, v As Variant
    Dim dict2, dict3 As Variant
    Dim r, c, r2 As Integer

    r = 2
    For Each ticker In Parsed.Keys() ' AAL
        Set dict2 = Parsed.Item(ticker)
        ActiveSheet.Cells(r, 1).Value = ticker ' set ticker on first line
        c = 2
        For Each third In dict2.Keys() ' year
            ActiveSheet.Cells(1, c).Value = third
            Set dict3 = dict2.Item(third)
            r2 = r
            For Each v In dict3
                ActiveSheet.Cells(r2, 1).Value = ticker ' repeat ticker on next lines
                ActiveSheet.Cells(r2, c).Value = v
                r2 = r2 + 1
            Next v
            c = c + 1
        Next third
        r = r2
    Next ticker

    'Sheets("example").Range(Cells(1, 1), Cells(Parsed("values").Count, 3)) = Values
End Sub

Started with specs\VBA-JSON - Specs.xlsm
Add new sheet, left it active and run this macro.
This line ActiveSheet.Cells(r2, 1).Value = ticker repeats ticker for next lines, for simlicity suppose same order of 3rd level records.

Result in Excel: