Do you want to be able to find the best stocks to invest in on your own? With a custom Excel stock screener, you can! In this guide, we will walk you through the steps of creating your own screener in Excel. This is a great way to find stock investment opportunities faster and improve your investment performance. Let's get started!
How to get stock data in Excel?
The first step to building your Excel stock screener is getting the necessary data into Excel. There are many ways in which you can get stock data in Excel. You can check out this detailed guide we have written on the subject. However, the best way is to use Wisesheets and Excel Stocks to get all the stock data you need without wasting time copy-pasting data for every company you analyze.
Getting the stock data in Excel depends on the type of data you are looking for but below are a few examples that will help you create your own fundamental stock screener in Excel.
Financials and key metrics in Excel
Unfortunately, Excel stocks does not cover stock financials and key metrics, but luckily you can get this data and more using Wisesheets. Using the WISE function, you can choose what data you want to get with coverage for all these financial statement numbers and key metrics.
The syntax of the function is simple: =WISE("ticker", "parameter/s", "period/s", "quarter [optional]").
For example, to get the free cash flow, net income, and revenue of all these companies, you can enter the following formula on your spreadsheet.
Keep in mind, in this case, "LY" (Last fiscal year) is used as the period to compare the companies more accurately; however, you can also use "TTM" (trailing twelve months) and "LQ" (last fiscal quarter).
Similarly, using the WISE function like this, you can get important key metrics such as PE, ROE, debt to equity, and PB ratio on a TTM basis.
Real-time stock data in Excel
Once you have all the financial and key metric data that you need in Excel, it's time to add real-time stock price data to the mix. To do this, you can use the WISEPRICE function.
The syntax of the WISEPRICE function is =WISEPRICE("ticker/s", "parameter/s"), where the parameter can be any of the following:
- 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
For example, to get the current market cap, changes percentage, name, and price of all these companies, you can enter the following formula:
As you can see, the best part is that you can get all the real-time stock data you need in one function call by referencing all the stocks you'd like to get data for.
Creating your own custom Excel stock screener
Now that you know how to get all the data you need into Excel, it's time to start creating your own custom Excel stock screener.
You can organize your stock screener, especially the type of data you decide to include to compare companies.
The screener we will build includes the following data:
- Financials: free cash flow, revenue, net income.
- Key metrics: PE, ROE, debt to equity, and PB ratio
- Real-time data: name, price, changes, percentage market cap.
Finding a list of companies to analyze
The first step is to find a list of companies that you want to analyze. Depending on your investment strategy, you may want to compare companies in the same sector and industry or any other criteria. Getting stock lists is simple; all you have to do is google "list of publicly traded companies in Excel," and you will get a ton of results.
For example, we want to find a list of all the companies in the healthcare sector, so we can do a google search like this.
Once you have your list of companies, it's time to add all the data we talked about before to Excel and start analyzing it.
Separating the screener's data
Since real-time data requires the use of the WISEPRICE function, while the remaining data requires the WISE function. You will need to make your data requests in two parts.
First, using the WISEPRICE function, you can get all the real-time data you need for our healthcare industry stock like this.
Once you have the real-time data set up, the next step is to add the remaining data.
You can get all of this data per each company at once using the WISE function, as you can see below.
TTM is used to compare the companies because it reflects the last 12 months of financial performance for all the companies and removes any seasonality effects.
You can simply drag this function down with the proper function to get the fundamental stock data for all the companies.
Refresh the stock data
One of the best parts about building your own stock screener with Wisesheets is that you can refresh the data whenever you like in one click.
Once you have the data set up as outlined above, simply navigate to the WISERPICE function menu of the add-on and press the refresh data button.
This will update all your real-time data at once, so you always have your information up to date.
Alternatively, you can also navigate to a cell that contains real-time data, click on it and then press enter. This will cause the cell to reload the formula and refresh the data.
How to add Excel formulas to the screener
The best part about an Excel screener is that you can customize it any way you want using the built-in Excel functionality. Therefore you can add graphs, formulas, conditional formatting, etc. For our stock screener, let's say we want to highlight companies that have an ROE higher than 15% and are trading at a PE lower than 10. Using the IF and AND functions, you can highlight these companies as follows
As you can see, the IF and AND functions allow you to test for specific conditions in the data and return any message or value you'd like.
Now you can apply conditional formatting to highlight the cells that meet these criteria or leave it as it is.
Filter stocks by criteria that's important to you
The best part about an Excel stock screener is that you can use it to filter stocks by any criteria that's important to you.
The best way to do this currently is to copy-paste the data into another tab of your spreadsheet as values only. This will allow you to filter through the values without the Wisesheets formulas running every time you apply a filter.
Once you are set up in this way, you can begin to apply filter criteria. So, for example, if we want to find all the companies in our healthcare industry list with a PE below 10 and an ROE above 15%, we can simply apply filters.
You can also sort your stock screener values based on the highest to lowest. For example, display the stocks with the highest ROE first.
As you can see, using Excel, you can easily filter stocks by any criteria you want and find the best investment opportunities.
Save your screeners for future reference
After you have your Excel stock screener set up the way you want, you will probably want to save it for future reference.
The best way to do this is to save the Excel file somewhere on your computer. Then, every time you want to use the screener, refresh the data and copy-paste the data as values only in another spreadsheet tab so you can do any filtering you'd like.
An even better excel stock screener
We know how important stock screening is for you to find great investment opportunities by applying your own investment criteria. For that reason, at Wisesheets, we are working on building a dedicated Excel stock functionality that will allow you to get up and running in seconds.
If you would like to get notified about this upcoming functionality, please sign up for our newsletter by creating a free Wisesheets account here.
Conclusion
In conclusion, building your own Excel stock screener is a great way to find investment opportunities that match your specific criteria.
Not only does this save you time from having to look through all the stocks manually, but it also allows you to customize your searches any way you want.
All you need is a list of ticker symbols and some basic Excel knowledge.
So what are you waiting for? Get started today and find your next great investment opportunity!
Happy investing!
