How to create dynamic Hyperlinks
I have been using Excel to perform all my stock market record-keeping and stock-tracking activities. I use it to daily get the stock numbers using the external data functionality to know what my daily gains and losses have been. Lately I tried adding another functionality to it and during that I came across a need to use a function that helps create Hyperlinks to web pages. For this I used Hyperlink() function.
But my tool was dynamic, and similarly the hyperlink was also supposed to be dynamic. It was supposed to get me to the Yahoo Finance’s page of the company that I had selected in my tool. So, it means that the hyperlink should change with a change of company in my tool. This is how I did it:
I used the Concatenate and Hyperlink function to achieve this. For the concatenate, I actually used the Ampersand (&) rather than using the concatenate function.
If you have used Yahoo Finance, you would know that the web address naming for a company snapshot on Yahoo Finance is something like this (I am using Indian firms as I check on those only):
Bharti Airtel – http://finance.yahoo.com/q?s=BHARTIARTL.BO
Punj Lloyd – http://finance.yahoo.com/q?s=PUNJLLOYD.BO
Note the naming convention of the two links. The part “http://finance.yahoo.com/q?s=” remains common in the two links. And the end part, is actually the BSE Codes. You can get these codes from the BSE wesbite.
So, I created the formula as (please remove the spaces from the below function, except from “Home Page”) -
= Hyperlink(“http://finance.yahoo.com/q?s=” & A1, “Home Page”)
And in the cell A1, I created a dynamic lookup to the code according to the company that I selected. This dynamic lookup would pick up the BSE code from a list and would feed it the to the Hyperlink function to get to the appropriate webpage. Now, whenever I change the company that I am analyzing, I can open up it’s snapshot page without any hassles.
[...] This post was mentioned on Twitter by Dhakkanz, Excel Matic. Excel Matic said: Excel Matic How to create dynamic Hyperlinks: I have been using Excel to perform all my stock market record-keepin… http://bit.ly/bwZQPN [...]
Dear All,
Some confusions is there so please attach the screen shot for reference. System showing A1 reference is invalid hence can’t access the link.Thank You! Have a Nice day!
Hi Kumaresan,
I will attach a screen shot soon. Till that time, can you confirm if the cell A1 contains the scrip codes? I realized that I did not specifically mentioned that in my post. I will make that change also in the post.
Thanks for bringing this up!
Cheerz,
Mohit
Hi:
I came to your website looking how to reference a Excel cell. For example, if I have AAPL in cell A1, I am looking for how to create a Yahoo Finance hyperlink, http://finance.yahoo.com/q?s=aapl&ql=1. However, instead of static link of AAPL, I want to reference to cell A1.
I hope this makes sense as in the A column, I have a ticker list.
The end result I am looking for is a formula that creates a hyperlink to the yahoo finance summary page using the cells located in A1, A2, A3, etc.
Hi Tom,
Here is the function that you can simply put in cell B1 and then drag it down along the Ticker list in column A of your file. Or, you can simply use the downloadable file below. I have put n sample Tickers in the enclosed file. (The Excel workbook enclosed would require MS Office 2010)
File:
=HYPERLINK(“http://finance.yahoo.com/q?s=”&A1&“&ql=1″)
In the above I have highlighted some part of the text in BOLD and ITALICS. These parts are basically those parts of the hyperlink which are constant. The only dynamic part is the cell range (i.e. A1 in the above formula).
If you used the above formula without the function, the result will be simply a text – http://finance.yahoo.com/q?s=A1&ql=1. To make this text a hyperlink, you would need to envelope this text in HYPERLINK() function.
Leave your response!
My Google Page Rank
eBooks by Excel Matic
Recent Comments
Sponsored Links
Popular Posts
Email subscription
Useful Links
What I'm Doing...
Powered by Twitter Tools
Sponsors
Previous Posts
Categories
Users: %GUESTS_SEPERATOR%1 Guest%BOTS_SEPERATOR%
Recent Posts
Most Commented
Optimized by SEO Ultimate
Powered by WordPress | Log in | Entries (RSS) | Comments (RSS) | Arthemia theme by Michael Hutagalung