Google Sheets: Import Yahoo Finance Key Statistics Like PE ratio

The API URL is

https://query2.finance.yahoo.com/v10/finance/quoteSummary/TSLA?modules=defaultKeyStatistics
{  "quoteSummary": {    "result": [      {        "defaultKeyStatistics": {          "maxAge": 1,          "priceHint": {            "raw": 2,            "fmt": "2",            "longFmt": "2"          },          "enterpriseValue": {            "raw": 745340534784,            "fmt": "745.34B",            "longFmt": "745,340,534,784"          },          "forwardPE": {            "raw": 144.41774,            "fmt": "144.42"          },          "profitMargins": {            "raw": 0.02286,            "fmt": "2.29%"          },          "floatShares": {            "raw": 771530247,            "fmt": "771.53M",            "longFmt": "771,530,247"          },          "sharesOutstanding": {            "raw": 959854016,            "fmt": "959.85M",            "longFmt": "959,854,016"          },          "sharesShort": {            "raw": 52379526,            "fmt": "52.38M",            "longFmt": "52,379,526"          },          "sharesShortPriorMonth": {            "raw": 60620873,            "fmt": "60.62M",            "longFmt": "60,620,873"          },          "sharesShortPreviousMonthDate": {            "raw": 1609372800,            "fmt": "2020-12-31"          },          "dateShortInterest": {            "raw": 1611878400,            "fmt": "2021-01-29"          },          "sharesPercentSharesOut": {            "raw": 0.0546,            "fmt": "5.46%"          },          "heldPercentInsiders": {            "raw": 0.19985001,            "fmt": "19.99%"          },          "heldPercentInstitutions": {            "raw": 0.42529,            "fmt": "42.53%"          },          "shortRatio": {            "raw": 1.4,            "fmt": "1.4"          },          "shortPercentOfFloat": {            "raw": 0.0679,            "fmt": "6.79%"          },          "beta": {            "raw": 2.090715,            "fmt": "2.09"          },          "impliedSharesOutstanding": {},          "morningStarOverallRating": {},          "morningStarRiskRating": {},          "category": null,          "bookValue": {            "raw": 23.151,            "fmt": "23.15"          },          "priceToBook": {            "raw": 33.748005,            "fmt": "33.75"          },          "annualReportExpenseRatio": {},          "ytdReturn": {},          "beta3Year": {},          "totalAssets": {},          "yield": {},          "fundFamily": null,          "fundInceptionDate": {},          "legalType": null,          "threeYearAverageReturn": {},          "fiveYearAverageReturn": {},          "priceToSalesTrailing12Months": {},          "lastFiscalYearEnd": {            "raw": 1609372800,            "fmt": "2020-12-31"          },          "nextFiscalYearEnd": {            "raw": 1672444800,            "fmt": "2022-12-31"          },          "mostRecentQuarter": {            "raw": 1609372800,            "fmt": "2020-12-31"          },          "earningsQuarterlyGrowth": {            "raw": 1.571,            "fmt": "157.10%"          },          "revenueQuarterlyGrowth": {},          "netIncomeToCommon": {            "raw": 690000000,            "fmt": "690M",            "longFmt": "690,000,000"          },          "trailingEps": {            "raw": 0.64,            "fmt": "0.64"          },          "forwardEps": {            "raw": 5.41,            "fmt": "5.41"          },          "pegRatio": {            "raw": 6.37,            "fmt": "6.37"          },          "lastSplitFactor": "5:1",          "lastSplitDate": {            "raw": 1598832000,            "fmt": "2020-08-31"          },          "enterpriseToRevenue": {            "raw": 23.635,            "fmt": "23.64"          },          "enterpriseToEbitda": {            "raw": 174.43,            "fmt": "174.43"          },          "52WeekChange": {            "raw": 3.6852326,            "fmt": "368.52%"          },          "SandP52WeekChange": {            "raw": 0.21104872,            "fmt": "21.10%"          },          "lastDividendValue": {},          "lastDividendDate": {},          "lastCapGain": {},          "annualHoldingsTurnover": {}        }      }    ],    "error": null  }}

Setup ImportJSON

  • Google Sheets -> Tools -> Script Editor
  • Add the ImportJSON.gs script

Use the following formula for extract Forward PE for particular stock code

=VALUE(INDEX(ImportJSON(CONCATENATE("https://query2.finance.yahoo.com/v10/finance/quoteSummary/", A2, "?modules=defaultKeyStatistics"), "/quoteSummary/result/defaultKeyStatistics/forwardPE"), 2, 1))

NOTE: A2 is the stock code, e.g. TSLA.

❤️ Is this article helpful?

Buy me a coffee ☕ or support my work via PayPal to keep this space 🖖 and ad-free.

Do send some 💖 to @d_luaz or share this article.

✨ By Desmond Lua

A dream boy who enjoys making apps, travelling and making youtube videos. Follow me on @d_luaz

👶 Apps I built

Travelopy - discover travel places in Malaysia, Singapore, Taiwan, Japan.