Microsoft Excel: Using CONCATENATE To Create an Impossible Lookup Table in Seconds

5,200 Stock Symbols — I Want Four URL Links

Image for post
Image for post
Automated Dashboard Stock Activity Report

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…).
Image for post
Image for post
Symbol Iist
  • 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:

Image for post
Image for post
SEC company page CONCATENATE Process

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

Image for post
Image for post

“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!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store