Concatenate function in Excel and Google Docs

Concatenate…Concatenate…

So as previously mentioned, I’m (gradually) improving at using Excel for fun and profit. Actually, in this case I’m using Google Docs, since it uses most of the same features and syntax, with a few random add-ons.

Here’s one little project that actually got somewhere. It’s a Google Docs spreadsheet where I’m taking a copy-pasted table of last year’s ex-dividend announcements in the month of May, trimmed them down to stock symbols, and then used those stock symbols to automatically generate links.

Why do this? Well, some stocks do a quick “jump” after the dividend announcement, it would be convenient to have an automatically-generated list of links to observe each stock’s performance post-announcement. These announcements usually come around the same date each year. I generated this list to test my theory, but it should be emphasized that this just spreadsheet advice, and definitely not financial advice!

How to make it:

The first step was to copy-paste the dividend calendars from last year into the spreadsheet. Because the info is already in table format, you just need to select the box that you want to become the upper-left-hand corner of the pasted information. This is repeated until you have a nice, long list of stocks and info, which can then be sorted by Excel/Google Sheets. You can also freeze the upper row of the sheet, so that you have labels as you scroll through.

Now because that particular info contains the actual symbol in parenthesis, it needs to be trimmed. My methods for this were messy, and you can see the artifact in the “O” column from me only doing one side of the symbol at a time. I’ll have to improve on this, but it can be figured out by some searching.

Once you have the stock symbols isolated, it’s time to concatenate! Using this function, it’s easy to generate URL’s that have something predictably inserted. Stock Symbols are excellent for this, as many websites use them in the URL.

For example, in the following URL is generated for the stock symbol ALX:

http://www.nasdaq.com/symbol/alx/dividend-history

So all we need is a way to attach the rest of the URL onto the stock symbol. In this specific example, the function looks something like this:
= CONCATENATE("http://www.nasdaq.com/symbol/", A2, "/dividend-history")

Note the quotation marks around the URL fragments, because those let the function know that those are string-type variables. The “A2” refers to the location of the Stock Symbol in the same row. That will be important when we copy the function into new rows, because Excel will change it to the next row’s info in that column (ie. row 3 will use its own stock symbol from A3).

Once this function is set up, it can be pasted into subsequent boxes! It can also be altered for other columns of information; in this example sheet, it’s used to generate stocks for not only Nasdaq’s Dividend History page, but also the TradingView chart and Motley Fool’s CAPS page, where their community critiques the stocks.

There are a couple other goodies, like the Current Price column, compliments of the GOOGLEFINANCE() function, that update whenever you open the spreadsheet, and the dividends/year, which is an occasionally-inaccurate function that divides the one payout by the total payouts, and then rounds to the nearest integer. This is a bit inaccurate sometimes, because the payouts can change drastically.

There are other examples where this works, but it’s easy to create your own! Just pay attention to the URL’s of the websites you visit, and sometimes you can stumble on similar possibilities!

(And again with the disclaimer, this spreadsheet is not financial advice…just a weird way to have fun.)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s