How to use Wisesheets – The Ultimate Guide

Wisesheets tutorial

Wisesheets is the most powerful stock add-in to help you retrieve all the essential stock data you need to make great investment decisions. From real-time stock data to company financials, using Wisesheets, you can get all the stock data you need in your Excel or Google Sheets spreadsheet. This guide will teach you how to use Wisesheets to make better investment decisions and save time by not copying/pasting stock data.

Table of Contents

  1. Historical Stock Data in Excel and Google Sheets
    1. Statement Dump
    2. The WISE function
    3. Large Data Requests Option
    4. Cell Referencing
    5. Historical quarterly dividend payments
    6. Historical dividend payment history
    7. Segment & geographic revenue breakdowns
    8. Historical price data
  2. Real-Time Stock Data
    1. WISEPRICE function
    2. Refreshing Live Stock Data
    3. Cell Referencing
    4. TTM Key Metrics
    5. Company profile information
  3. Final words

If you prefer a video version, see the video below.

Historical Stock Data in Excel and Google Sheets

Currently, Wisesheets offers two ways to get historical stock data:

Statement Dump

When you log into Wisesheets, you will see the statement dump window that looks like the image below. Here you can get stock financial statements, key metrics, and growth metrics going back 19 years and 72 quarters. Simply enter the stock ticker or company name you'd like to retrieve data for, select annual or quarterly, select standardized financials or SEC as reported financials, and get all of the information at once in different tabs of your spreadsheet.

Wisesheets financials

Keep in mind we recommend using standardized financials because they are better for comparing financial performance across companies. Reported SEC financials are also helpful to better understand the specific items and amounts reported to the SEC. SEC statement dump is in BETA as the financial items are not spaced or capitalized yet, and the items may be out of order. This will be addressed in future updates where financial statement notes and segmented revenue information is likely to be included.

The WISE function

Instead of getting all the stock data at once, you can get specific items from the financial statements using the WISE function. This allows you to build custom models that enable you to analyze a stock in any way you'd like, such as building stock dashboards, DCFs, or any other stock analysis model.

The WISE function syntax is =WISE("ticker", "parameter/s", "period/s", "quarter")

For a visual tutorial on cell referencing, press the tutorial button on the WISE function menu.

Wisesheets WISE function
WISE function tutorial

The first parameter is where you enter the company's stock ticker you are looking to get data for. For example, Apple's ticker is "AAPL". Note that for stock exchanges outside of the USA, you might need to add an extension. For example, Shopify's (listed on the Toronto Stock Exchange) ticker is "SHOP.TO". If you are unsure about your stock's ticker, use the same convention as Yahoo Finance. For a list of stock exchanges covered and their respective extension, see the stock exchanges covered section.

The second parameter is for the data point you would like to retrieve from the stock you entered. For a full list of data points available, click here. Currently, the data available includes all items listed under the income, statement, balance sheet, cash flow statements, key metrics, growth metrics section, and more. Again, capitalization and spacing do not matter, but the spelling must be the same.

The third parameter is the period you'd like to get the data from. Here you can select either a specific year, "TTM" (which returns the results from the sum of the last 4 quarters), "LY" (Last Fiscal Year), or "LQ" (Last Fiscal Quarter). You can also use "LY-1", "LY-2", "LY-3"… and "LQ-1", "LQ-2", "LQ-3", etc., to get the data from previous fiscal quarters or years.

The fourth parameter is optional. If it is not entered, the function will return the data for the selected period. However, if you'd like to retrieve quarterly data, you need to use "Q1", "Q2", "Q3," or "Q4". These quarterly periods are based on the company's fiscal years, which may or may not be aligned with the current calendar year.

Altogether here is an example of a simple function call =WISE("AAPL", "revenue", 2020, "Q4")

*Note if you are thinking of requesting large volumes of data, we have a better option for you below.

Large Data Requests Option

Instead of making too many simultaneous function requests, we have developed a way for you to get multiple stock data points at once.

This functionality is very simple as you saw before the WISE function syntax is =WISE("ticker", "parameter/s", "period/s", "quarter")

All you need to do is enter a range of cells instead of a singular parameter or enter a range of periods instead of a single period. You can see how that works in this example:

WISE function parameters

As you can see, the function is getting all these key metrics for Apple across the different periods in a single function call. This saves you a lot of time and makes your spreadsheet faster.

Note that if the key metrics were the columns and the periods the rows, the function would still return the data in the format you need.

Cell Referencing

Instead of manually writing the function parameters in the function, you can reference cells that contain the parameters you are looking for. In this example, you can see a model build to retrieve a company's revenue over 3 years.

Wisesheets income statement model

Cell locking is very useful when you want to drag the function across cells and get the data you need. To lock cells horizontally, use $ before the cell letter, to lock cells vertically, use $ after the cell letter, to lock cells entirely, use the $ before and after the cell letter.

Historical quarterly dividend payments

Using the WISE function, it is simply to get past stock and ETF dividend payments. You can get individual quarterly dividend payments as follows:

=WISE("ticker", "dividend", "LQ")

This will return the latest quarterly dividend paid by a stock or ETF.

Dividends in Excel

Moreover, you can get multiple historical dividends or their sum by entering multiple quarterly periods in the WISE function.

Historical dividend payments sheet

As you can see, this returns the past 4 dividend payments. You can also warp this into a SUM function to get the SUM of the past 4 dividend payments in one cell like this:

=SUM(WISE("aapl","dividend",{"lq","lq-1","lq-2","lq-3"}))

Lastly, you can get the sum of the dividend payments for a particular year by entering the following function command:

=WISE("ticker", "dividend", year/s)

dividend payments excel

This will provide you with the sum of the dividend payments made in that year regardless of the payment frequency of the ETF or stock.

Historical dividend payment history

If you are looking for stock's dividend data on Excel or Google Sheets, you will love this. With the =WISEPRICE function, you can get all the dividend data you need for the companies you need in a single function call. All you have to do is enter =WISEPRICE("ticker", "dividend")

Dividend stock data on Excel
The image is cropped but the data goes back to 1985

As you can see, this will return the date, dividend, adjusted dividend, payment date, and declaration date.

Segment & geographic revenue breakdowns

Looking to get a specific company’s revenue breakdown across key business segments and geographic markets?

Using the WISE function, you can get this data on a quarterly and annual basis.

To get segment revenue breakdowns, all you have to do is use the function as follows =WISE(“ticker”, “segment revenues”, period).

For example, to get apple’s revenue breakdown per segment in 2020, you can use =WISE(“aapl”, “segment revenues”, 2020)

Apple Segment revenues Excel

You can also substitute the specific year for “LY”, “LY-1”, “LY-2” etc, which provides you with the latest fiscal year data or the previous fiscal years.

The same concept applies to geographic revenues, except you need to use “geographic revenues” as the parameter. For example, to get apple’s geographic revenue breakdown 3 quarters ago, you can use this function call =WISE(“aapl”, “geographic revenues”, “LQ-2”)

Apple geographic revenues Excel

Due to some technical details for quarterly data, you can only use the LQ, LQ-1, etc, period type for this data.

*This feature is only for companies that report to the SEC since the data comes from these reports.

Historical price data

You can also retrieve historical price data for stocks and ETFs using Wisesheets via the =WISEPIRCE function.

The formula syntax works as follows: =WISEPRICE("ticker", "parameter", "number of days", "start date", "end date").

The list of available historical parameters includes:

  • Open
  • High
  • Low
  • Close
  • AdjClose
  • Volume
  • Unadjusted Volume
  • Change
  • Change Percent
  • Vwap
  • Label
  • ChangeOverTime

The number of days parameter is optional. If you do enter a value like 30 it will return the last 30 business days worth of historical data for whatever parameter you've selected along with the respective date.

For example =WISEPRICE("AAPL", "Close", 30)

Apple historical stock price

In cases where you want to get this data for a specific period, like between January 1, 2022 and January 30, 2022 you can leave this parameter blank.

You have the option to enter a specific date range from which to get data. Make sure to use the yyyy-mm-dd format.

For example, if you are looking to get Apple's volume between March 1st and March 30th in 2022 you need to enter the following function command in a cell =WISEPRICE("AAPL", "volume", , "2022-03-01", "2022-03-30")

Apple's volume Excel

You can also use cell referencing to enter multiple parameters at once in the function and reference cells that are in the date format to ensure you are getting the data for the correct time period.

Real-Time Stock Data

Besides being able to retrieve historical stock data, you can also get real-time stock data for the following parameters:

  • Symbol
  • Name
  • Price
  • Changes Percentage
  • Change
  • Day Low
  • Day High
  • Year High
  • Year Low
  • Market Cap
  • Price Avg 50
  • Price Avg 200
  • Volume
  • Avg Volume
  • Exchange
  • Open
  • Previous Close
  • EPS
  • PE
  • Shares Outstanding
  • Earnings announcement
  • Timestamp

WISEPRICE function

The WISEPRICE function is used to get the live stock data. The syntax for this function is =WISEPRICE("ticker/s", "parameter/s")

Similar to the WISE function, the first parameter is where you enter the stock ticker/s of the company you are looking to get data for. The same rule from above for stock exchanges outside of the USA applies.

The second parameter/s is where you select your data point. The list of available parameters is available above. Keep in mind, that capitalization and spacing do not matter, but the spelling must be the same.

Refreshing Live Stock Data

One of the benefits of the WISEPRICE function is that you can refresh the data at any point by pressing the refresh button on the WISEPRICE function window. This will allow you to refresh the data for cells containing the WISEPRICE formula and get the data you need updated.

Wisesheets real-time stock data on Google Sheets

Cell Referencing

Similar to the WISE function, instead of manually writing the function parameters in the function, you can reference cells that contain the parameter/s and ticker/s you are looking for. In this example, you can see a model build to retrieve multiple stock's live prices, pe, and market cap.

Live stock data Google Sheets

Note that if the tickers were the columns and the parameters the rows, the function would still return the data in the format you need

TTM Key Metrics

Getting stock TTM key metrics which some change all the time based on factors like the stock price has never been easier. Via the =WISE function you can get access to the following metrics:

  1. Revenue Per Share
  2. Net Income Per Share
  3. Operating Cash Flow Per Share
  4. Free Cash Flow Per Share
  5. Cash Per Share
  6. Book Value Per Share
  7. Tangible Book Value Per Share
  8. Shareholders Equity Per Share
  9. Interest Debt Per Share
  10. Market Cap
  11. Enterprise Value
  12. Pe Ratio
  13. Price To Sales Ratio
  14. Pocfratio
  15. Pfcf Ratio
  16. Pb Ratio
  17. Ptb Ratio
  18. Ev To Sales
  19. Enterprise Value Over EBITDA
  20. Ev To Operating Cash Flow
  21. Ev To Free Cash Flow
  22. Earnings Yield
  23. Free Cash Flow Yield
  24. Debt To Equity
  25. Debt To Assets
  26. Net Debt To EBITDA
  27. Current Ratio
  28. Interest Coverage
  29. Income Quality
  30. Dividend Yield
  31. Payout Ratio
  32. Sales General And Administrative To Revenue
  33. Research And Ddevelopement To Revenue
  34. Intangibles To Total Assets
  35. Capex To Operating Cash Flow
  36. Capex To Revenue
  37. Capex To Depreciation
  38. Stock Based Compensation To Revenue
  39. Graham Number
  40. Roic
  41. Return On Tangible Assets
  42. Graham Net Net
  43. Working Capital
  44. Tangible Asset Value
  45. Net Current Asset Value
  46. Average Receivables
  47. Average Payables
  48. Average Inventory
  49. Days Sales Outstanding
  50. Days Payables Outstanding
  51. Days Of Inventory On Hand
  52. Receivables Turnover
  53. Payables Turnover
  54. Inventory Turnover
  55. Roe
  56. Capex Per Share

All you have to do is enter =WISE("ticker", parameter/s, "ttm"). For example =WISE("AAPL", "Dividend yield", "TTM")

Company profile information

Using the =WISEPRICE function, you can access important stock information for a company or list of companies, including:

  • Beta
  • Vol Avg
  • Last Div
  • Range
  • Currency
  • Cik
  • ISIN
  • CUSIP
  • Exchange
  • Exchange Short Name
  • Industry
  • Website
  • Description
  • CEO
  • Sector
  • Country
  • Full Time Employees
  • Phone
  • Address
  • City
  • State
  • Zip
  • Is ETF
  • Is Actively Trading
  • Is ADR
  • Is Fund

For example, you can use the following function call to get the beta, sector, and industry for this list of stocks.

Beta, sector and industry on your spreadsheet

Final words

Now you know how to get stock financials and real-time price data on Excel and Google Sheets using Wisesheets without wasting countless hours copying/pasting stock data.

We hope this helps you make better investment decisions.

We will continue updating the list of available functions, so stay tuned for more updates!

For a free Wisesheets account, click here.

Best Regards,

The Wisesheets Team

66 Responses

  1. Hi,
    I really LOVE this software and find it very helpful for retrieving my data in a quick way. Thanks for building.
    I have one request, however: I would love to be able to get the sector/subsector as well from the company, so I don't need to look it up.
    Is it possible to include this as well, similar to the "real-time stock data"? It would make this happy customer even happier.
    Cheers!

    1. Hi Robbe,

      Glad to hear you are enjoying using Wisesheets.

      Absolutely we have this data and we are planning to make it available on the functions. More importantly, we plan on releasing a screener that allows you to get financial statements and key metrics for a list of companies in the same industry, sector, and exchange.

  2. Hello, thank you again for putting this together. but i am trying to get data for Australianlisted stocks on ASX. e.g., BHP.AX.

    It gives me an error and doesn't return a value. CAn you please confirm if stocks on ASX are covered? as I can see your table shows that it is supported. It was the reason i paid for the subscription.

      1. I am also trying to get ASX data but do not seem to be able to get it to work… when I type ASX company names into the "Statement Dump" search, only dual-listings in the US come up; when I try to get ASX price quotes, I cannot find a code format that works (eg "BHP.AX" or "CBA:AU").

        1. ASX companies may not show up on the search list for statement dump but don't worry the data is available. We use the same ticker system as Yahoo Finance so you can do BHP.AX as an example 🙂

  3. This is a powerful add-on and easy to use.

    Please can "Fair Value" and "Cash Conversion Ratio" be added under Key Metrics in the near future.

  4. You actually make it seem so easy with your presentation but I to find this topic to be really one thing that I think I would never understand. It seems too complex and very vast for me. I am taking a look ahead on your subsequent submit, I will attempt to get the hold of it!

  5. I see there is an option in the dataset for "Five Y Dividend Per Share Growth Per Share", can you please advise how to pull this data?

    Thank you,

  6. Hi first of all this is great (I immediately took the paid version).

    coyld you add the buyback yield = (the repurchase of outstanding shares over the existing market cap of a company. If a company purchased 50 million dollars worth of its own stock and its market cap was 500 million, the buyback yield would be 10%)

  7. Hi, I am very interested in the wisesheets tool.
    I wanted to ask if data from european stocks are also available? Like SAP or BMW?

  8. Hi,

    How can I get historical price of particular day without the header and also how does the change percent or change over time parameters works? Can it display the change percent of a stock over a year?

    I am trying to display the price change percent of bajfinance.ns stocks on a yearly interval. I can't seem to make the code work for me.

    Kindly help.

  9. Hi there, Great plug-in. Is there an easy way to get current Enterprise Value ? Wise function pulls it from the latest period specified (LQ) and WISEPRICE function doesn't seem to have it. Thanks

  10. Hello,
    Some stocks pay monthly and others pay twice a year. Is there an easy way to get total dividends paid over the last 12 months?
    Thanks

  11. Hallo, Wisesheets is not availabel in Google Workspace Markplace. If i search, i get the message that they cant find it. Do you have a solution for that?

      1. Hi Guillermo, this is good but how to pull out the most recent ex-div or payment date by itself in a single cell?

          1. I'm using this formula for Ex-div date, what should I use for payment date?
            Thank you!

  12. Amazing Add-on, is there any way when getting historical prices to use different intervals besides days? Thanks, and I appreciate it!

  13. Hi Guillermo, I'm trying to figure out if I can retrieve analyst growth estimates for the future, it's one of the data points that I retrieve manually from Yahoo Finance, but I think it's possible through Wise Sheets but I don't know how.

    I am basically looking for analyst growth estimates for the next few years, does Wise Sheets offer this? How can I retrieve it? Thanks.

  14. Hi, I downloaded "Stock Analysis Tool" and changed a ticker "GOOG" to "MSFT". It does not get updated properly. It is referenced to ='C:\Program Files\Microsoft Office\Root\Office16\LIBRARY\Analysis\FUNCRES.XLAM'!_xldudf_WISEPRICE($E$2,B3). This appears to be incorrect. How should it be referenced so that it can get updated? Thanks

    1. Hi Frank, when that happens, you can do replace all with the text before the function like ='C:\Program Files\Microsoft Office\Root\Office16\LIBRARY\Analysis\FUNCRES.XLAM'!_xldudf_ with nothing, so all the formulas update. To avoid this, make sure to save your Excel files locally on your computer as opposed to the cloud via one drive.

  15. Hello,

    Are there any settings I can adjust for how often the data updates? Currently whenever I create a new column of formulas in my workbook, all of the other columns reload even though they are not being effected or changed. I would like to prevent this so that only the new formula pulls information and the old info stays at whatever polling rate is the standard.

    Thanks!

    1. Hi Alek, this is a default functionality by Excel we cannot change it. But you can avoid the refresh of values by copy-pasting as values only or getting new columns and moving them across the spreadsheet without adding additional columns.

  16. Excellent software and this saves lots of time in evaluating companies. You can do this for companies in Canada also. I highly recommend this for any investor who does due diligence before buying any stock.
    Thanks for the Wise Team

  17. Hi,

    Saw few people asking about the parameter of "Shares Outstanding"

    Would appreciate you could add the end of period shares outstanding, not the weighted average shares outstanding. There are different. One is a snapshot figure, the other is the average number over a period.

    This number is very important for financial modeling. Appreciate your help.

      1. For US companies, the numbers are typically at the bottom part of the first page of every 10-Q and !0-K filing. Just FYI. Thanks for the respond 🙂

  18. Hi, I'm trying to use the growth data in the WISE() function in Google Sheets. Specifically the "EBIT growth" parameter. I'm confused about what the actual data is meant to represent. Is it already in percent form, or simply a decimal ratio? In other words, does a value of 2.0 mean 2% growth or 200% growth?
    I'm also confused about the general logic of your growth data. When I query with "LQ" for last quarter, am I getting a year-over-year growth rate? based on the last quarter's results? Or am I getting a quarter-over-quarter growth rate?

    1. We are happy to help.

      Yes, they are percentage values. You will see they are essentially the same as the statement dump output for the company.

      For LQ, you get quarter-over-quarter growth.

  19. I'm currently using the trial version. I need to pull a lot of historical pricing data for stocks (e.g. 20 symbols per week, 100 weeks, all pricing points (Open-high-low-close) for every day of the week (20x100x4x5 = 40000 prices). This much data overwhelms Excel's stockhistory function. Can wisesheets handle this?

    Also, am I correct that WISEPRICE only handles text values for dates? Using values in Excel date format did not seem to work. Finally, do you have to use the INDEX function if you only want a single data point without the column headers or date row? So if I want to return the opening price of TSLA on 12/27/22, the formula is: =INDEX(WISEPRICE("TSLA","Open",,"2022-12-27","2022-12-27"),2,2)

    1. Hi Travis,

      Yes, we believe we have the capability to handle this. Many of our users request even more data. However, the best way is for you to try this and see it for yourself.

      We offer a 14-day refund period, so you can do it at no risk.

      Yes, currently, that is the case, but we are working on making it in the native excel date format. Right now, you can convert the dates to value and then use the datevalueformula to make the conversion.

      You can also specify a date like =WISEPRICE("aapl","Close",,DATE(2019,1,1),DATE(2019,1,4)) or use the Index function too.

  20. I have just started to use Wisesheets and so far it looks like a very useful tool. Here are a couple of suggestions for data items to consider for addition in the future:
    In Key Metrics, add PEG ratio (Price/Earnings to Growth Rate ratio), and Piotroski's F (note that GuruFocus reports both)
    In Analyst Estimates, add Target Price or Fair Value or Intrinsic Value

  21. Hi, I just signed up for Wisesheets. Your videos are helpful. Do you know if there is a formula for retrieving a price on a specific date. I am not looking for a range of prices. for example, YTD. Also, I want to be able to pull up many at once via rows.

    Thanks

    Steve

    1. Thank you yes you can do something like =WISEPRICE("aapl", "close", ,"01/02/2022", "01/03/2022") very soon you will be ablee to do just =WISEPRICE("aapl", "close", ,"01/02/2022", "01/03/2022") and if you want the value oly you can do =SUM(WISEPRICE("aapl", "close", ,"01/02/2022", "01/03/2022")).

  22. As a life long Refinitive/Bloomberg user I am impressed. Few simple asks: #1 can we have a bit more clarity on functions. i.e Avg Volume and Vol Avg give the same result, what is the difference between the two? #2 Would it be possible to add # of days next to each WisePrice parameter? It would be great to have Average Volume over a x number of days, same for price etc… this will allow each user to set their own parameter and use the app accordingly. #3 can we ratios in Fractions (% etc..)

    1. Thank you.Yes, it's the same volume it's just a different way of accessing the data. Yes we have that available you can do something like =AVERAGE(WISEPRICE("aapl", "volume", 30)) to get the average for the past 30 days. Yes you can format the data in Excel and Google Sheets as a percentage.

  23. hi, glad to see wisesheets continues to make progress. One question: is there any way to look up TTM-1, TTM-2, …, TTM-10 data? If I manually recreate this function, I would need to make 4 times the number of data requests (i.e. by working with quarterly data instead of annual ones)

    1. Hi, we currently don't have this available, but you can do it using the LQ, LQ-1 system using ranges to minimize the data requests. For example =WISE("aapl", "revenue", {"lq-4", "lq-5", "lq-6", "lq-7"})

  24. First off, I just want to say how incredible of a tool this is and how thankful I am to you guys for your work. With that said, I do have one question: I am trying to make a simple bar chart showing the growth/decline of employee count each year for the last 5 years or so in Excel but the formula I am using keeps saying "A value used in the formula is of the wrong data type" and I cannot figure out what I am doing wrong.

    For example, say I am trying to the the employee count for Apple at the end of 2015, the (faulty) formula I am currently using is
    =WISEPRICE("AAPL","Full Time Employees",,"12/31/2015","12/31/2015")

    I tried playing with the quotation marks in the formula and the blank part in the "number of days to end" section but haven't found a fix. Could you please tell me what I am doing wrong and how to fix it?

    Thank you in advance, and once again, keep up the good work because this tool is amazing.

    1. Thank you so much for your kind words.

      We are happy to help. The issue is that the full-time employee number is only a real-time metric available as a company profile metric.

      For this reason, we do not have this as a historical number that can be accessed.

      We have added this to the list of improvements to add.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts