Simple Financial SpreadSheets – Kids Stuff

Simply Intelligent Technical Analysis and Trading Strategies

Simple Financial SpreadSheets – Kids Stuff

10 February 2009 1,466 views 20 Comments

Excel Web Query – What in the world is that? If you are like the other 99.9% of MS Excel users, you probably have never heard of microsoft excel web queries

Excel web queries are powerful! Web queries strips web pages of useless stuff and gives you access to the essential information. You can then use Excel formulas (like =A1/B2) to work directly with the data you’ve downloaded. Even one can build realtime data without prior knowledge of Programming.

Web queries can be used for importing stock quote data into Excel. For rapidly changing data like stock quotes, the web queries can be set to automatically refresh every 1,3,5,20,… minutes.

With a web query, you can set up a program that will automatically gather up-to-the second refreshed data from the web every time you open the spreadsheet. Here is an example to build a simple application that extracts index data from nseindia.com and auto refreshes every 1 minute.

To start, use Data – Import External Data – New Web Query.

Webquery

A mini-browser opens inside of Excel. Initially, it will display your default Internet Explorer home page.

Use the mini-browser to navigate to the web page containing data that you would like to download. type www.nseindia.com into the address bar in the Excel browser. Click Go, the page loads. You will notice that there are many yellow arrows on the page. These arrows point to each table on the page. The nseindia.com page has a lot of tables. Some web pages might just have one arrow, to grab the whole page. Click on a yellow arrow to select that table. Once you select the table(s), the yellow arrow(s) turn to green checkmarks. When you click the Import button, the Import Data dialog is displayed. By default, the data will be imported at the current cell pointer location.

Auto Refresh Feature

Move the cell pointer so that it is selecting one of the cells inside the imported data range. And Now go to

Data->Import External Data->Data Range Properties

There are many good settings here. In this case, I would suggest:

Refresh every 1 minute
Refresh data on file open
Insert entire rows for new data, clear unused cells
Fill down Formulas in columns adjacent to Excel data

With these settings, your Excel Sheet will automatically update the data every 1 minute from the web .

Its one among the kids stuff…….. Be a good kid!!!!

20 Comments »

geniusjaggu said:

thanx rajandran for this article

# 11 February 2009 at 4:47 am

MJ said:

thanks a lot yaar

# 11 February 2009 at 4:53 am

Naren said:

good artcle. but i cannot download the nseindia page. i am using office 2000. do ypu need to upgrade?.naren309@gmail.com

# 11 February 2009 at 5:13 am

Mustafa said:

one of the best updates in your blog… you have shared something which is going to ease life of many.. keep sharing ways and methods you use.

# 11 February 2009 at 7:42 am

Rajandran said:

@Naren.It works good with Office 2003 and Office 2007.Try with it

# 11 February 2009 at 7:46 am

Viralpala said:

Great Rajendra,Good Work,I really like your Excel Spread Sheet : Viral Pala

# 11 February 2009 at 9:24 am

Rajandran said:

Thanks Every one for your Encouragement

# 11 February 2009 at 11:28 am

Mustafa said:

Rajendra,Thanks once again. Few queries…Can we import data data from multiple sources into one single spreadsheet? Also can you suggest any online resources to enhance this knowledge.

# 12 February 2009 at 3:19 pm

Anonymous said:

Thanx dude.. very well taught!!..you made my day!!Cheers!!

# 13 February 2009 at 8:03 am

Rajandran said:

@MustafaYes you can get data from multiple sources.Its afterall a easy game

# 13 February 2009 at 10:04 am

Nishant said:

Thanx alot sir for sharing such a wonderful knowledge… Its really being very very informative..

# 18 February 2009 at 1:50 pm

Anonymous said:

Thanks a lot man UR post is of great help.

# 24 February 2009 at 7:55 pm

choudri said:

GOODLUCK TO SMART KIND AND GOOD HEARTED TEACHER CREECH KID Choudri

# 27 February 2009 at 12:44 pm

Anonymous said:

How does one import data for 2-3 stocks please.I got the nse page but how to get a specific stocks data and then from there into amibroker?Thanks in advance

# 20 March 2009 at 10:57 am

Nandi said:

Thanks Rajendran for listing this link in vfmdirect forumBala

# 20 March 2009 at 1:31 pm

men said:

Hi,Rajendran how to get the data for 2-3 stocks and then get this data into amibroker.Thanks,Sudhin

# 21 March 2009 at 11:36 am

abbas said:

hi this is good,if we give 1 minute refresh.old data delet and new data there.how to save old data in spreedsheet?

# 4 December 2009 at 6:55 am

Amirishali said:

Rajandran,
You are genius as always.But very few people share it with others & ur one of them.I’m still not succeeded in this exercise.No values coming into cells.It will be too much asking for u but what r the next steps.

Amirishali

# 12 February 2010 at 11:39 pm

How to Convert your MS Excel into Dynamic Nifty Heat Map | Marketcalls said:

[...] is a simple Web Query Based Excel Spreadsheet, Which converts your MS Excel into Dynamic Nifty Heat Map with an auto [...]

# 3 March 2010 at 12:46 am