vba – Best way to make a function that imports, parses, and then filters a JSON String for a specific item/key?

I would like to make a function that takes a stock’s ticker and retrieves historical financial statement data from it.

I can successfully import a string with a Get HttpRequest – with the string being:

{“2021-12-31”:{“date”:”2021-12-31″,”filing_date”:”2022-02-18″,”currency_symbol”:”USD”,”researchDevelopment”:null,”effectOfAccountingCharges “,”:null,”incomeBeforeTax”:”133731000.00″,”minorityInterest”:”0.00″,”netIncome”:”135443000.00″,”sellingGeneralAdministrative”:”240256000.00″,”sellingAndMarketingExpenses”:”333453000.00″,”grossProfit:null, “reconciledDepreciation”:”7541000.00″,”ebit”:”137005000.00″,”ebitda”:”144546000.00″,”depreciationAndAmortization”:”7541000.00″,”nonOperatingIncomeNetOther”:null,”operatingIncome”:null,”otherOperatingExpenses”:null, “interestExpense”:”3274000.00″,”taxProvision”:”-1712000.00″,”interestIncome”:”50588000.00″,”netInterestIncome”:”47314000.00″,”extraordinaryItems”:null,”nonRecurring”:null,”otherItems”:null ,”incomeTaxExpense”:”-1712000.00″,”totalRevenue”:”841439000.00″,”totalOperatingExpenses”:null,”costOfRevenue”:null,”totalOtherIncomeExpenseNet”:”-7150000.00″,”discontinuedOperations”:null,”netIncomeFromContinuingOps”:” 135443000.00″,”netIncomeApplicableToCo mmonShares”:”135443000.00″,”preferredStockAndOtherAdjustments”:null},”2020-12-31″:{“date”:”2020-12-31″,”filing_date”:”2021-03-18″,”currency_symbol” :”USD”,”researchDevelopment:null,”effectOfAccountingCharges”:null,”incomeBeforeTax”:”5950000.00″,”minorityInterest”:”404000.00″,”netIncome”:”5983000.00″,”sellingGeneralAdministrative”:”83190000.00″,” sellingAndMarketingExpenses”:”99659000.00″,”grossProfit”:null,”reconciledDepreciation”:”2278000.00″,”ebit”:”14380000.00″,”ebitda”:”16658000.00″,”depreciationAndAmortization”:”2278000.00″,”nonOperatingIncomeNetOther”:null “,”operatingIncome”:null,”otherOperatingExpenses”:null,”interestExpense”:”8026000.00″,”taxProvision”:”371000.00″,”interestIncome”:”12842000.00″,”netInterestIncome”:”4816000.00″,”extraordinaryItems”:null ,”nonRecurring”:null,”otherItems”:null,”incomeTaxExpense”:”371000.00″,”totalRevenue”:”227601000.00″,”totalOperatingExpenses”:null,”costOfRevenue”:null,”totalOtherIncomeExpenseNet”:null,”discontinuedOperations” :null,”netIncomeFromCon tinuingOps”:”5579000.00″,”netIncomeApplicableToCommonShares”:”135443000.00″,”preferredStockAndOtherAdjustments”:null},”2019-12-31″:{“date”:”2019-12-31″,”filing_date”:”2019- 12-31″,”currency_symbol”:”USD”,”researchDevelopment”:null,”effectOfAccountingCharges”:null,”incomeBeforeTax”:”-4946000.00″,”minorityInterest”:”1026000.00″,”netIncome”:”-466000.00″ “,” sellingGeneralAdministrative”:”56812000.00″,”sellingAndMarketingExpenses”:”93175000.00″,”grossProfit”:null,”reconciledDepreciation”:”774000.00″,”ebit”:”26093000.00″,”ebitda”:”26867000.00″,”depreciationAndAmortization” :”774000.00″,”nonOperatingIncomeNetOther”:null,”operatingIncome”:null,”otherOperatingExpenses”:null,”interestExpense”:”26485000.00″,”taxProvision”:”74000.00″,”interestIncome”:”30827000.00″,”netInterestIncome” :”4342000.00″,”extraordinaryItems”:null,”nonRecurring”:null,”otherItems”:null,”incomeTaxExpense”:”74000.00″,”totalRevenue”:”163818000.00″,”totalOperatingExpenses”:null,”costOfRevenue”:null ‘totalOtherIncomeExpenseNet’:null “,”discontinuedOperations”:null,”netIncomeFromContinuingOps”:”-5020000.00″,”netIncomeApplicableToCommonShares”:”-466000.00″,”preferredStockAndOtherAdjustments”:null},”2018-12-31″:{“date”:”2018-12- 31″,”filing_date”:”2018-12-31″,”currency_symbol”:”USD”,”researchDevelopment”:null,”effectOfAccountingCharges”:null,”incomeBeforeTax”:”-11218000.00″,”minorityInterest”:”12001000.00 “,”netIncome”:”-12319000.00″,”sellingGeneralAdministrative”:”35236000.00″,”sellingAndMarketingExpenses”:”63633000.00″,”grossProfit”:null,”reconciledDepreciation”:”314000.00″,”ebit”:”14164000.00″,” ebitda”:”14478000.00″,”depreciationAndAmortization”:”314000.00″,”nonOperatingIncomeNetOther”:null,”operatingIncome”:null,”otherOperatingExpenses”:null,”interestExpense”:”26483000.00″,”taxProvision”:”0.00″,” interestIncome”:”37314000.00″,”netInterestIncome”:”10831000.00″,”extraordinaryItems”:null,”nonRecurring”:null,”otherItems”:null,”incomeTaxExpense”:null,”totalRevenue”:”96066000.00″,”totalOperatingExpenses” :null,”costOfRevenu e”:null,”totalOtherIncomeExpenseNet”:null,”discontinuedOperations”:null,”netIncomeFromContinuingOps”:”-11218000.00″,”netIncomeApplicableToCommonShares”:”-12319000.00″,”preferredStockAndOtherAdjustments”:null},”2017-12-31″: {“date”:”2017-12-31″,”filing_date”:”2017-12-31″,”currency_symbol”:”USD”,”researchDevelopment”:null,”effectOfAccountingCharges”:null,”incomeBeforeTax”:” -8855000.00″,”minorityInterest”:”12001000.00″,”netIncome”:”-7717000.00″,”sellingGeneralAdministrative”:”25663000.00″,”sellingAndMarketingExpenses”:”33838000.00″,”grossProfit”:null,”reconciledDepreciation”:”93000.00″ “,”ebit”:”1709000.00″,”ebitda”:”1802000.00″,”depreciationAndAmortization”:”93000.00″,”nonOperatingIncomeNetOther”:null,”operatingIncome”:null,”otherOperatingExpenses”:null,”interestExpense”:”9420000.00″ “,”taxProvision”:”6000.00″,”interestIncome”:”15548000.00″,”netInterestIncome”:”6128000.00″,”extraordinaryItems”:null,”nonRecurring”:null,”otherItems”:null,”incomeTaxExpense”:”6000.00″ “,”totalRevenue”:”55970000.00″ ,”totalOperatingExpenses”:null,”costOfRevenue”:null,”totalOtherIncomeExpenseNet”:null,”discontinuedOperations”:null,”netIncomeFromContinuingOps”:”-8861000.00″,”netIncomeApplicableToCommonShares”:”-7717000.00″,”preferredStock}AndnullAd}

But am having issues pulling beyond one year’s worth of data using this approach:

https://www.mrexcel.com/board/threads/vba-api-how-to-return-more-than-32767-characters.1166944/

Unfortunately, I do not know how to use it to output an array of values ​​as opposed to just one line.

Ideally, I would love to be able to pull multiple years’ data for “totalRevenue”. I use TimHall’s VBA-JSON currently.

What I would currently like is a variant of the function that enables multiple lines without the need for knowing the (“date”) part of the function.

I have seen dictionary approaches, filter string approaches, but am having difficulties tying both of each back into the formula. I am largely looking for an approach that does not make me have to rename the dates – and that could instead use a fill to where the only customizable field will be the metric (eg, totalRevenue, ebit…).

Any help would be phenomenal.

It would ideally come out like this with the use of a function.

Leave a Comment