Data Import and Analysis using External Data from a Website

[sswang]

Hello Forum,

I was wondering if anyone has suggestions on the best way to work with external data from a website using MS Excel 2016.

I have tried below, and I was wondering if there is a more efficient way for data import/refresh from websites and if there is any sample VBA code or macro for data analysis for this type of data.

1. To import the USA stock market information into Excel, I went to MS Excel>Data>Get External Data>From Other Sources>From XML Data Import>entered URL: "wwwmarketwatch.com/tools/marketsummary?screener=nyse"
2. To have the information automatically refresh from the website in Excel, I enabled background refresh, refresh every 60 minutes, and refresh data when opening the file
3. Then I added the Asia and Europe market information on the same Excel worksheet by using the XML Data Import
4. So far I've added Data Bars (conditional formatting) in the % Chng. column (I'm wondering what I can do to analyze this data set)

If there's an existing thread with similar discussions, please point me to the thread.
Appreciate your suggestions!

[MarvinP]

Hi sswang,

Are you using the Stock Data Type included in the newer versions of Excel? See:
https://support.office.com/en-us/art...8-f1a89e210877 or
https://www.thespreadsheetguru.com/b...trics-to-excel
Marv

[sswang]

Thank you MarvinP! I really appreciate your reply. This is really cool, but I couldn't get this to work in MS Excel 2016 on my computer. Guess it's time to upgrade.

[alansidman]

Since you are not on 365, here is a link to a work around. I use this myself as I am running 2019

http://www.michael-saunders.com/stoc...ages/info.html

[sswang]

This is awesome! Thank you so much