Do you want to invest in ETFs but don't know where to start? Are you overwhelmed by all of the options out there? Don't worry, we're here to help. In this blog post, we will show you how to create an ETF screener on Excel and Google Sheets. With these tools, you can quickly and easily find the best ETFs for your portfolio. Let's get started!
What is an ETF, and how does it work?
An ETF (Exchange Traded Fund) is a collection of investments, such as stocks, bonds, or commodities. The ETF works by tracking the performance of a particular index or basket of assets and then providing investors with exposure to those assets without having to buy them individually. This makes investing in an ETF much more accessible than buying and selling individual stocks or bonds.
Once you invest in a particular ETF, you are essentially buying shares in that fund. Although these shares can be bought and sold on the stock exchange, just like shares of any other company over time, ETFs have been shown to have higher returns and lower risks compared to mutual funds.
The benefits of using an ETF screener
An ETF screener is a great way to find the perfect fund for you. It allows you to quickly and easily compare different ETFs based on their performance, fees, and other characteristics. This can help you select the best ETF for your portfolio.
With a growing number of ETFs tracking different markets and asset classes, it can be difficult to find the right ETF for you without a screener. A good ETF screener allows you to quickly narrow down your search by specifying different criteria, such as risk level, fees, diversification, or even geographic exposure.
After finding the right ETF, you can help you set up alerts for when the ETF's performance reaches certain levels so that you can make the most of your investments.
How to build an ETF screener in Excel or Google Sheets?
The most challenging part about building a good ETF screener in Google Sheets and Excel is to find a reliable data source that provides you with all the adat you need for the ETFs you are interested in. However, once you have the right data, it is very easy to use filters and tables on your spreadsheet to narrow down your ETF search. Each of these steps will be discussed below.
Find an ETF list
The first step is to find an ETF list that you are interested in. We recommend typing into Google the type of ETFs you are interested in investing, followed by the word "ETF list." For example, let's say you want to invest in technology ETFs. In that case, you would Google "Technology ETF list."
After that, you can simply copy-paste the list of ETFs into your spreadsheet.
Adding real-time pricing data
Once you have the ETF list ready, one of the types of data to add which is really valuable is real-time pricing data. This allows you to see the current price of an ETF and also track its performance over time. To add pricing data, we recommend using Wisesheets, which allows you to get the data right in Excel or Google Sheets at once like this:
The best part is that you can refresh the data any time by pressing the refresh data button on the Wisesheets add-on.
Adding dividend data
Another great data source to add is dividend data. This allows you to see how much of a return an ETF pays out in the form of dividends. To add dividend data, you can use the =WISE function from Wisesheets, which allows you to get the dividend yield on a real-time basis and dividends paid in any particular year or period.
As you can see by entering =WISE("etf ticker", "dividend yield", "ttm"). You can get the respective dividend yield for your entire ETF list.
Another useful command is to enter =WISE("etf ticker", "dividend", year) to get the sum of the dividends paid per share for that particular year.
Adding ETF-specific data
While it is nice to e able to have price and dividend data, one of the most essential data points to add is ETF-specific data. This includes metrics like expense ratios, net asset values, asset class, and assets under management. This data is also available through Wisesheets; you can see all that is available here.
In this case, we will use the =WISEFUNDS function to get the expense ratio and net asset value of the ETFs on our list.
Adding spreadsheet formulas or conditional formatting
With all the data available on your stock list, you can add criteria formulas such as If statements. For example, if the expense ratio is higher than x%, then return "bad ETF." You can also color the cell red using conditional formatting to further help with your analysis.
Filtering through your ETF screener
Once your screener is ready with all the ETFs and data you want, you are free to apply any filters. Simply select all of the screener's data and click on filter. This will allow you to sort the screener list by dividend yield, expense ratios, price, or any other category you'd like.
The best part about this is that you can add and remove ETFs, and your screener will allow you to find the best investment opportunities for you.
*By default, when you filter, each cell will reload, and so will the formulas with the data. In order to void an automatic refresh every time you apply a filter. You can go to settings and set up your calculations to manual to avoid this tedious task.
How to screen for the best ETFs for your portfolio?
With all the ETFs added to our list and the data sources in place, we can now start filtering for the best ETFs for our portfolio.
First of all, let's filter by volume. This will help us find more liquid ETFs and make it easier to trade in a timely manner without having to worry about large spreads or slippage. We can also sort ETFs by expense ratio to find the ETFs with lower costs and better returns.
Secondly, we can filter ETFs with higher dividend yields to ensure we are getting more of our money back in the form of dividends while holding ETFs in our portfolio. Finally, we can add criteria such as asset class or assets under management to further customize our ETF screener.
Applying these filters allows you to easily find ETFs that meet your criteria and make the best investments for your portfolio.
Conclusion
Creating an ETF screener on Excel and google sheets is a great way to gain more control over your investments. With Wisesheets, you can easily find ETFs with the data sources and formulas mentioned above. Furthermore, you are free to apply any filters that make sense for your investing style or portfolio goals.
So go ahead and start creating your ETF screener and find the best ETFs for your portfolio.
To your investing success!
