Microsoft Excel: Using CONCATENATE To Create an Impossible Lookup Table in Seconds
5,200 Stock Symbols — I Want Four URL Links
What’s The Problem To Be Solved?
A project I am working on entails looking at a selected portfolio of equities and creating a basic automated dashboard.
The “summary” page section that I want to automate is shown above.
What I need is a LOOKUP list to create a Dynamic Hyperlink. So I created a “MASTER” lookup list of over 5,200 stock symbols (the selected equities COULD BE any group of stocks — might as well cover all possibilities!).
Using Excel’s CONCATENATE capabilities, this took about three minutes.
Without the ability to use CONCATENATE, I would not be able to effectively — timewise — complete this task.
Here’s How It Is Done
- Start with a list of symbols in Excel — set up the list as an Excel Data Table. This list was sourced from the NASDAQ website using Excel Power Query to retrieve it (more on that later…).
- I picked four website links I want for each security (SEC Reporting, Reuters Business news, Google Finance page, and SeekingAlpha page). These are four very useful sites to quickly get more insight and information on a Company if the need arises.
- A sample URL for each website is reflected below:
https://www.sec.gov/cgi-bin/browse-edgar?CIK=AAPL&owner=exclude&action=getcompany
https://www.reuters.com/finance/stocks/company-news/AAPL
https://finance.google.com/finance?q=AAPL
https://seekingalpha.com/symbol/AAPL
Notice in each URL, the only variable that likely changes is the stock symbol. So if I can automate replacing that symbol in each URL, I can make quick work of the list of 5,200 symbols.
CONCATENATE Saves The Day
The CONCATENATE function in Excel provides the ability to connect strings of text into one text string.
Let’s use the SEC link above as an example:
- Break the URL string into 3 components — isolating the stock symbol, so I can automate that piece.
https://www.sec.gov/cgi-bin/browse-edgar?CIK=
AAPL
&owner=exclude&action=getcompany
TIP: A shortcut to using the actual CONCATENATE function is to use the “&” symbol (it’s a shortcut and way easier!)
“&” operator to concatenate strings in Excel
In Microsoft Excel, & operator is another way to concatenate cells. This method come in very handy in many scenarios because typing the ampersand sign (&) is much quicker than typing the word “concatenate” :) — https://www.ablebits.com/office-addins-blog/2015/07/15/excel-concatenate-strings-cells-columns/#concatenation-operator
So here we go:
The formula is here → =$H$2&[@Symbol]&$H$4
I should point out that the two text strings — beginning and end of the URL — must be an ABSOLUTE reference. The cell address cannot change all the way down through the bottom of the list. The SYMBOL, however, is RELATIVE, and will pick up the cell directly to the left as it copies down the Table.
Once I put the formula in cell B2, it instantly updates all the way down through the end of the Table (one of my favorite features of Excel data tables!).
Now, repeat this process for the other URL’s and you have a full table that can be used to look up the HYPERLINK address.
Conclusion
This is a pretty unique example of how to use the CONCATENATE capabilities in Excel, but it is a real-application scenario which makes an (relatively) impossible task very easy!
I hope this helps you gain some understanding of how you can apply these capabilities to make your Excel work easier!
About Don
“It’s time for different”
Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.
“What Do You Do?”
I frequently get this question. My response (it’s not what you think!)? Check it out here!
