Daten aus Liste auslesen

Wenn du dir nicht sicher bist, in welchem der anderen Foren du die Frage stellen sollst, dann bist du hier im Forum für allgemeine Fragen sicher richtig.
Antworten
Mike440
User
Beiträge: 1
Registriert: Samstag 10. Juli 2021, 13:00

Hallo Zusammen,

eins vorneweg, ich bin wirklich ein blutiger Anfänger mit Programmierung und Python.
Ich stehe vor einem Problem und trotz Recherche im Internet habe ich keine Lösung gefunden.
Vielleicht kann ich hier Hilfe bekommen.

Ich hole mir über eine Webseite JSON Daten, aus diesen Daten möchte ich dann bestimmte Werte auslesen und in eine Excel Tabelle schreiben.

Das gelingt mir bei folgender Datenstruktur problemlos.

[ {
"symbol" : "AAPL",
"price" : 146.0899,
"beta" : 1.208152,
"volAvg" : 82857860,
"mktCap" : 2437889916928,
"lastDiv" : 0.835,
"range" : "89.145-146.57",
"changes" : 1.5899,
"companyName" : "Apple Inc.",
"currency" : "USD",
"cik" : "0000320193",
"isin" : "US0378331005",
"cusip" : "037833100",
"exchange" : "Nasdaq Global Select",
"exchangeShortName" : "NASDAQ",
"industry" : "Consumer Electronics",
"website" : "http://www.apple.com",
"description" : "Apple Inc. designs, manufactures, and markets smartphones, personal computers, tablets, wearables, and accessories worldwide. It also sells various related services. The company offers iPhone, a line of smartphones; Mac, a line of personal computers; iPad, a line of multi-purpose tablets; and wearables, home, and accessories comprising AirPods, Apple TV, Apple Watch, Beats products, HomePod, iPod touch, and other Apple-branded and third-party accessories. It also provides AppleCare support services; cloud services store services; and operates various platforms, including the App Store, that allow customers to discover and download applications and digital content, such as books, music, video, games, and podcasts. In addition, the company offers various services, such as Apple Arcade, a game subscription service; Apple Music, which offers users a curated listening experience with on-demand radio stations; Apple News+, a subscription news and magazine service; Apple TV+, which offers exclusive original content; Apple Card, a co-branded credit card; and Apple Pay, a cashless payment service, as well as licenses its intellectual property. The company serves consumers, and small and mid-sized businesses; and the education, enterprise, and government markets. It sells and delivers third-party applications for its products through the App Store. The company also sells its products through its retail and online stores, and direct sales force; and third-party cellular network carriers, wholesalers, retailers, and resellers. Apple Inc. was founded in 1977 and is headquartered in Cupertino, California.",
"ceo" : "Mr. Timothy Cook",
"sector" : "Technology",
"country" : "US",
"fullTimeEmployees" : "100000",
"phone" : "14089961010",
"address" : "1 Apple Park Way",
"city" : "Cupertino",
"state" : "CALIFORNIA",
"zip" : "95014",
"dcfDiff" : 89.92,
"dcf" : 147.279,
"image" : "https://financialmodelingprep.com/image-stock/AAPL.png",
"ipoDate" : "1980-12-12",
"defaultImage" : false,
"isEtf" : false,
"isActivelyTrading" : true
} ]

Mit folgendem Code schreibe ich die Daten dann in Excel.

# company profile laden
urlprofile = requests.get("https://financialmodelingprep.com/api/v ... PL?apikey=")

# companyprofile parsen
profile = urlprofile.text
companyprofile_ = json.loads(profile)
companyprofile = companyprofile_[0]

sheet = xw.Book().sheets[0]
sheet.range('A1').value = companyprofile['symbol']
sheet.range('B1').value = companyprofile['companyName']
sheet.range('C1').value = companyprofile['exchangeShortName']
sheet.range('D1').value = companyprofile['description']

Die Werte schreibt er einwandfrei in Excel.

Nun zum Problem

Bei folgender Liste macht er das nicht vermutlich weil das eine mehrstufige Liste ist und ich nicht die richtige Methode verwenden.

[ {
"date" : "2021-03-27",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2021-04-29",
"acceptedDate" : "2021-04-28 18:02:54",
"period" : "Q2",
"revenue" : 89584000000,
"costOfRevenue" : 51505000000,
"grossProfit" : 38079000000,
"grossProfitRatio" : 0.425064743704232917,
"researchAndDevelopmentExpenses" : 5262000000,
"generalAndAdministrativeExpenses" : 0,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 5314000000,
"otherExpenses" : 460000000,
"operatingExpenses" : 10576000000,
"costAndExpenses" : 62081000000,
"interestExpense" : 670000000,
"depreciationAndAmortization" : 2797000000,
"ebitda" : 30300000000,
"ebitdaratio" : 0.338230041078764088,
"operatingIncome" : 27503000000,
"operatingIncomeRatio" : 0.307007947847829976,
"totalOtherIncomeExpensesNet" : 460000000,
"incomeBeforeTax" : 28011000000,
"incomeBeforeTaxRatio" : 0.312678603322021798,
"incomeTaxExpense" : 4381000000,
"netIncome" : 23630000000,
"netIncomeRatio" : 0.263774781210930509,
"eps" : 1.40999999999999992,
"epsdiluted" : 1.39999999999999991,
"weightedAverageShsOut" : 16753476000,
"weightedAverageShsOutDil" : 16929157000,
"link" : "https://www.sec.gov/Archives/edgar/data ... -index.htm",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 210327.htm"
}, {
"date" : "2020-12-26",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2021-01-28",
"acceptedDate" : "2021-01-27 18:03:06",
"period" : "Q1",
"revenue" : 111439000000,
"costOfRevenue" : 67111000000,
"grossProfit" : 44328000000,
"grossProfitRatio" : 0.397778156659697235,
"researchAndDevelopmentExpenses" : 5163000000,
"generalAndAdministrativeExpenses" : 0,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 5631000000,
"otherExpenses" : -64000000,
"operatingExpenses" : 10794000000,
"costAndExpenses" : 77905000000,
"interestExpense" : 638000000,
"depreciationAndAmortization" : 2666000000,
"ebitda" : 36200000000,
"ebitdaratio" : 0.324841393049112082,
"operatingIncome" : 33534000000,
"operatingIncomeRatio" : 0.300917991008533836,
"totalOtherIncomeExpensesNet" : -64000000,
"incomeBeforeTax" : 33579000000,
"incomeBeforeTaxRatio" : 0.301321799370058929,
"incomeTaxExpense" : 4824000000,
"netIncome" : 28755000000,
"netIncomeRatio" : 0.258033543014564049,
"eps" : 1.69999999999999996,
"epsdiluted" : 1.67999999999999994,
"weightedAverageShsOut" : 16935119000,
"weightedAverageShsOutDil" : 17113688000,
"link" : "https://www.sec.gov/Archives/edgar/data ... -index.htm",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 201226.htm"
}, {
"date" : "2020-09-26",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2020-10-30",
"acceptedDate" : "2020-10-29 18:06:25",
"period" : "Q4",
"revenue" : 64698000000,
"costOfRevenue" : 40009000000,
"grossProfit" : 24689000000,
"grossProfitRatio" : 0.381603759003369514,
"researchAndDevelopmentExpenses" : 4978000000,
"generalAndAdministrativeExpenses" : 4936000000,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 4936000000,
"otherExpenses" : -8000000,
"operatingExpenses" : 9914000000,
"costAndExpenses" : 49923000000,
"interestExpense" : 634000000,
"depreciationAndAmortization" : 2702000000,
"ebitda" : 17477000000,
"ebitdaratio" : 0.270131997897925746,
"operatingIncome" : 14775000000,
"operatingIncomeRatio" : 0.228368728554205702,
"totalOtherIncomeExpensesNet" : -8000000,
"incomeBeforeTax" : 14901000000,
"incomeBeforeTaxRatio" : 0.230316238523601974,
"incomeTaxExpense" : 2228000000,
"netIncome" : 12673000000,
"netIncomeRatio" : 0.195879316207610732,
"eps" : 0.739999999999999991,
"epsdiluted" : 0.729999999999999982,
"weightedAverageShsOut" : 17057622000,
"weightedAverageShsOutDil" : 17256521000,
"link" : "https://www.sec.gov/Archives/edgar/data ... -index.htm",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 200926.htm"
}, {
"date" : "2020-06-27",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2020-07-31",
"acceptedDate" : "2020-07-30 19:29:09",
"period" : "Q3",
"revenue" : 59685000000,
"costOfRevenue" : 37005000000,
"grossProfit" : 22680000000,
"grossProfitRatio" : 0.379994973611460174,
"researchAndDevelopmentExpenses" : 4758000000,
"generalAndAdministrativeExpenses" : 4831000000,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 4831000000,
"otherExpenses" : -158000000,
"operatingExpenses" : 9589000000,
"costAndExpenses" : 46594000000,
"interestExpense" : 697000000,
"depreciationAndAmortization" : 2752000000,
"ebitda" : 15843000000,
"ebitdaratio" : 0.265443578788640389,
"operatingIncome" : 13091000000,
"operatingIncomeRatio" : 0.219334841249895274,
"totalOtherIncomeExpensesNet" : -158000000,
"incomeBeforeTax" : 13137000000,
"incomeBeforeTaxRatio" : 0.220105554159336525,
"incomeTaxExpense" : 1884000000,
"netIncome" : 11253000000,
"netIncomeRatio" : 0.188539834129178185,
"eps" : 0.652499999999999969,
"epsdiluted" : 0.645000000000000018,
"weightedAverageShsOut" : 17250292000,
"weightedAverageShsOutDil" : 17419152000,
"link" : "https://www.sec.gov/Archives/edgar/data ... -index.htm",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 200627.htm"
}, {
"date" : "2020-03-28",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2020-05-01 00:00:00",
"acceptedDate" : "2020-04-30 18:03:10",
"period" : "Q2",
"revenue" : 58313000000,
"costOfRevenue" : 35943000000,
"grossProfit" : 22370000000,
"grossProfitRatio" : 0.383619433059523618,
"researchAndDevelopmentExpenses" : 4565000000,
"generalAndAdministrativeExpenses" : 4952000000,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 4952000000,
"otherExpenses" : -10000000,
"operatingExpenses" : 9517000000,
"costAndExpenses" : 45460000000,
"interestExpense" : 757000000,
"depreciationAndAmortization" : 2786000000,
"ebitda" : 15639000000,
"ebitdaratio" : 0.268190626446932923,
"operatingIncome" : 12853000000,
"operatingIncomeRatio" : 0.220413972870543456,
"totalOtherIncomeExpensesNet" : -10000000,
"incomeBeforeTax" : 13135000000,
"incomeBeforeTaxRatio" : 0.225249944266287111,
"incomeTaxExpense" : 1886000000,
"netIncome" : 11249000000,
"netIncomeRatio" : 0.192907241952909286,
"eps" : 0.645000000000000018,
"epsdiluted" : 0.637499999999999956,
"weightedAverageShsOut" : 17440404000,
"weightedAverageShsOutDil" : 17618764000,
"link" : "https://www.sec.gov/Archives/edgar/data ... index.html",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 282020.htm"
}, {
"date" : "2019-12-28",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2020-01-29 00:00:00",
"acceptedDate" : "2020-01-28 18:02:44",
"period" : "Q1",
"revenue" : 91819000000,
"costOfRevenue" : 56602000000,
"grossProfit" : 35217000000,
"grossProfitRatio" : 0.383548067393458869,
"researchAndDevelopmentExpenses" : 4451000000,
"generalAndAdministrativeExpenses" : 5197000000,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 5197000000,
"otherExpenses" : 89000000,
"operatingExpenses" : 9648000000,
"costAndExpenses" : 66250000000,
"interestExpense" : 785000000,
"depreciationAndAmortization" : 2816000000,
"ebitda" : 28385000000,
"ebitdaratio" : 0.309140809636349767,
"operatingIncome" : 25569000000,
"operatingIncomeRatio" : 0.278471775994075299,
"totalOtherIncomeExpensesNet" : 89000000,
"incomeBeforeTax" : 25918000000,
"incomeBeforeTaxRatio" : 0.282272732223178224,
"incomeTaxExpense" : 3682000000,
"netIncome" : 22236000000,
"netIncomeRatio" : 0.242172099456539502,
"eps" : 1.26000000000000001,
"epsdiluted" : 1.24750000000000005,
"weightedAverageShsOut" : 17660160000,
"weightedAverageShsOutDil" : 17818416000,
"link" : "https://www.sec.gov/Archives/edgar/data ... index.html",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 282019.htm"
}, {
"date" : "2019-09-28",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2019-10-31 00:00:00",
"acceptedDate" : "2019-10-30 18:12:36",
"period" : "Q4",
"revenue" : 64040000000,
"costOfRevenue" : 39727000000,
"grossProfit" : 24313000000,
"grossProfitRatio" : 0.379653341661461585,
"researchAndDevelopmentExpenses" : 4110000000,
"generalAndAdministrativeExpenses" : 4578000000,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 4578000000,
"otherExpenses" : 206000000,
"operatingExpenses" : 8688000000,
"costAndExpenses" : 48415000000,
"interestExpense" : 810000000,
"depreciationAndAmortization" : 3179000000,
"ebitda" : 18804000000,
"ebitdaratio" : 0.293628981886321028,
"operatingIncome" : 15625000000,
"operatingIncomeRatio" : 0.243988132417239212,
"totalOtherIncomeExpensesNet" : 206000000,
"incomeBeforeTax" : 16127000000,
"incomeBeforeTaxRatio" : 0.251826983135540305,
"incomeTaxExpense" : 2441000000,
"netIncome" : 13686000000,
"netIncomeRatio" : 0.213710181136789495,
"eps" : 0.762499999999999956,
"epsdiluted" : 0.757499999999999951,
"weightedAverageShsOut" : 17963248000,
"weightedAverageShsOutDil" : 18081492000,
"link" : "https://www.sec.gov/Archives/edgar/data ... index.html",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 282019.htm"
}, {
"date" : "2019-06-29",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2019-07-31",
"acceptedDate" : "2019-07-31 16:31:53",
"period" : "Q3",
"revenue" : 53809000000,
"costOfRevenue" : 33582000000,
"grossProfit" : 20227000000,
"grossProfitRatio" : 0.375903659239160748,
"researchAndDevelopmentExpenses" : 4257000000,
"generalAndAdministrativeExpenses" : 4426000000,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 4426000000,
"otherExpenses" : 43000000,
"operatingExpenses" : 8683000000,
"costAndExpenses" : 42265000000,
"interestExpense" : 866000000,
"depreciationAndAmortization" : 2933000000,
"ebitda" : 14477000000,
"ebitdaratio" : 0.269044211934806432,
"operatingIncome" : 11544000000,
"operatingIncomeRatio" : 0.214536601683733202,
"totalOtherIncomeExpensesNet" : 43000000,
"incomeBeforeTax" : 11911000000,
"incomeBeforeTaxRatio" : 0.221357022059506775,
"incomeTaxExpense" : 1867000000,
"netIncome" : 10044000000,
"netIncomeRatio" : 0.186660224126075552,
"eps" : 0.550000000000000044,
"epsdiluted" : 0.54500000000000004,
"weightedAverageShsOut" : 18282532000,
"weightedAverageShsOutDil" : 18405520000,
"link" : "https://www.sec.gov/Archives/edgar/data ... 292019.htm",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 292019.htm"
}, {
"date" : "2019-03-30",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2019-05-01 00:00:00",
"acceptedDate" : "2019-05-01 16:32:00",
"period" : "Q2",
"revenue" : 58015000000,
"costOfRevenue" : 36194000000,
"grossProfit" : 21821000000,
"grossProfitRatio" : 0.376126863742135664,
"researchAndDevelopmentExpenses" : 3948000000,
"generalAndAdministrativeExpenses" : 4458000000,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 4458000000,
"otherExpenses" : 30000000,
"operatingExpenses" : 8406000000,
"costAndExpenses" : 44600000000,
"interestExpense" : 1010000000,
"depreciationAndAmortization" : 3040000000,
"ebitda" : 16455000000,
"ebitdaratio" : 0.283633543049211401,
"operatingIncome" : 13415000000,
"operatingIncomeRatio" : 0.231233301732310603,
"totalOtherIncomeExpensesNet" : 30000000,
"incomeBeforeTax" : 13793000000,
"incomeBeforeTaxRatio" : 0.237748858053951573,
"incomeTaxExpense" : 2232000000,
"netIncome" : 11561000000,
"netIncomeRatio" : 0.199276049297595459,
"eps" : 0.617500000000000049,
"epsdiluted" : 0.614999999999999991,
"weightedAverageShsOut" : 18696284000,
"weightedAverageShsOutDil" : 18802584000,
"link" : "https://www.sec.gov/Archives/edgar/data ... index.html",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 302019.htm"
}, {
"date" : "2018-12-29",
"symbol" : "AAPL",
"reportedCurrency" : "USD",
"fillingDate" : "2019-01-30 00:00:00",
"acceptedDate" : "2019-01-30 16:31:37",
"period" : "Q1",
"revenue" : 84310000000,
"costOfRevenue" : 52279000000,
"grossProfit" : 32031000000,
"grossProfitRatio" : 0.379919345273395825,
"researchAndDevelopmentExpenses" : 3902000000,
"generalAndAdministrativeExpenses" : 4783000000,
"sellingAndMarketingExpenses" : 0,
"sellingGeneralAndAdministrativeExpenses" : 4783000000,
"otherExpenses" : 143000000,
"operatingExpenses" : 8685000000,
"costAndExpenses" : 60964000000,
"interestExpense" : 890000000,
"depreciationAndAmortization" : 3395000000,
"ebitda" : 26741000000,
"ebitdaratio" : 0.317174712371011747,
"operatingIncome" : 23346000000,
"operatingIncomeRatio" : 0.276906654014944853,
"totalOtherIncomeExpensesNet" : 143000000,
"incomeBeforeTax" : 23906000000,
"incomeBeforeTaxRatio" : 0.283548807970584738,
"incomeTaxExpense" : 3941000000,
"netIncome" : 19965000000,
"netIncomeRatio" : 0.236804649507768944,
"eps" : 1.05499999999999994,
"epsdiluted" : 1.04499999999999993,
"weightedAverageShsOut" : 18943280000,
"weightedAverageShsOutDil" : 19093008000,
"link" : "https://www.sec.gov/Archives/edgar/data ... index.html",
"finalLink" : "https://www.sec.gov/Archives/edgar/data ... 292018.htm"
} ]

Ich versuche das mit folgendem Code.

# income statement quaterly laden
urlincome = requests.get("https://financialmodelingprep.com/api/v ... 00&apikey=")
#income statement quaterly parsen
income = urlincome.text
incomestatementq_ = json.loads(income)
incomestatementq = incomestatementq_[0]

#income statement in excel schreiben

sheet.range('A3').value = incomestatementq['period']
sheet.range('A4').value = incomestatementq['eps']
sheet.range('A6').value = incomestatementq['period']
sheet.range('A7').value = incomestatementq['revenue']

Hier schreibt er noch in das Feld A3 die Periode Q2 und bricht dann mit folgendem Fehler ab:

Traceback (most recent call last):
File "C:\Users\agena\PycharmProjects\fmp\main.py", line 35, in <module>
sheet.range('A4').value = incomestatementq['eps']
KeyError: 'eps'

Wie bekomme ich die Daten aus der Liste in Abhängigkeit vom Datum oder der Periode ausgelesen?

Ich will beispielsweise der Wert revenue in di Excel Tabelle schreiben und zwar für alle Perioden.

Das Ergebnis solle also so aussehen

Periode:2021-03-27
Revenue:89584000000

Periode:2020-12-26
Revenue:64698000000

Periode:2020-09-2
Revenue:64698000000
.
.
.
USW.

Wie kann ich das erreichen?

Vielen Dank im Voraus.

Grüße

Michael
Antworten