How do I populate an Excel field with changing web data?

Question

I'm building a list on Excel 2011 (mac version). The first column is a list of websites. The second column is a list of corresponding Alexa Global ranks.

The Problem: Alexa rankings change on a daily basis and my list needs to stay up-to-date. Can I link Excel to Alexa, so that each time a website's Alexa rank changes, it's corresponding Excel field changes accordingly?

Ex of a website's Alexa page: http://www.alexa.com/siteinfo/cnn.com

Answer 1

You could get external data from the web, using Excel's inbuild ability to do so. However this would be extremely time consuming.

If you did this for your example above you would have to create a worksheet with CNN's Alexa page linked to it. Then you would have to find the cell that contained the text "Global rank iconX Y" where X and Y are numeric values. These values are the Global ranking and change in the last 3 months respectively.

In this example the cell is A87. Then it's just of cleaning up the redundant data from the cell using a formula like:

=LEFT(SUBSTITUTE(A87, "Global rank icon", ""), FIND(" ", SUBSTITUTE(A87, "Global rank icon", "")))

This leaves you just needing to create a macro on the workbook open event:

Private Sub Workbook_Open()

    ActiveWorkbook.RefreshAll

End Sub