Understanding and comparing stock market figures

[sandip.dhamapurkar]

Hello everybody, I am new to this forum and new to Stock market as
well. I am working on automating my manual work that I do everyday to
understand and compare stock market figures. One of my friend told me
that it can be done in excel and I can take help from the experts on
this site. It would be of great help if somebody can show me
directions as to how I can use excel to automate my daily manual work.

I don't know if this is possible. I want to go to a website and pick
up some values from there with the help of excel programming. There
would be a unique number in each url that would represent a company.
The unique number would always be in column "A" and the name of the
company will be in column "B"

In my example, cell "A3" contains number 17040007 that represents a
company "Cinevistaas Ltd.", cell "A4" contains number 17080029 that
represents a company "Jagran Prakashan", cell "A5" contains number
17020012 that represents a company "Jain Irrigation". I have 33
companies where I want to update the figures on a regular basis.

I want to go to the site below and get the "Last Traded", "Change",
"Volume", "Prev Close", "Day's H/L (Rs)", "Mkt Cap (Rs Cr)", "52wk H/L
(Rs)" and "PE ratio" figures (Scroll below for PE ratio) and put it
under appropriate headings in the excel file, format of which is shown
below.
http://money.rediff.com/money/jsp/company.jsp?companyCode=17040007
(The number 17040007 should be picked up from cell A3)

Once the above is done, I want to go to the site given below and get
the "Sales", "EPS (Rs)", "Net profit/loss" and "NPM (%)" figures in
the last five quarters and input it in the same excel sheet in
appropriate columns.
http://money.rediff.com/money/jsp/co_results_q.jsp?companyCode=17040007

Do the same process again and get the "Sales", "EPS (Rs)", "Net profit/
loss" and "NPM (%)" figures in the last five half-yearly period.
http://money.rediff.com/money/jsp/co_results_hy.jsp?companyCode=17040007

Do the same process again and get the "Sales", "EPS (Rs)", "Net profit/
loss" and "NPM (%)" figures in the last five years.
http://money.rediff.com/money/jsp/co_results_annual.jsp?companyCode=17040007

Once everything is done move to the other company, ID given in cell
"A4"

Excel sheet headings: from column "A" to column "BR"
"Company ID"
"Company name"
"Last Traded"
"Change"
"Volume"
"Prev Close"
"Day's H/L (Rs)"
"Mkt Cap (Rs Cr)"
"52wk H/L (Rs)"
"PE ratio"
"Q_Sales Sep '07"
"Q_Sales Jun '07"
"Q_Sales Mar '07"
"Q_Sales Dec '06"
"Q_Sales Sep '06"
"Q_EPS (Rs) Sep '07"
"Q_EPS (Rs) Jun '07"
"Q_EPS (Rs) Mar '07"
"Q_EPS (Rs) Dec '06"
"Q_EPS (Rs) Sep '06"
"Q_Net profit/loss Sep '07"
"Q_Net profit/loss Jun '07"
"Q_Net profit/loss Mar '07"
"Q_Net profit/loss Dec '06"
"Q_Net profit/loss Sep '06"
"Q_NPM (%) Sep '07"
"Q_NPM (%) Jun '07"
"Q_NPM (%) Mar '07"
"Q_NPM (%) Dec '06"
"Q_NPM (%) Sep '06"
"H_Sales Sep '07"
"H_Sales Mar '07"
"H_Sales Sep '06"
"H_Sales Mar '06"
"H_Sales Sep '05"
"H_EPS (Rs) Sep '07"
"H_EPS (Rs) Mar '07"
"H_EPS (Rs) Sep '06"
"H_EPS (Rs) Mar '06"
"H_EPS (Rs) Sep '05"
"H_Net profit/loss Sep '07"
"H_Net profit/loss Mar '07"
"H_Net profit/loss Sep '06"
"H_Net profit/loss Mar '06"
"H_Net profit/loss Sep '05"
"H_NPM (%) Sep '07"
"H_NPM (%) Mar '07"
"H_NPM (%) Sep '06 "
"H_NPM (%) Mar '06"
"H_NPM (%) Sep '05"
"A_Sales Mar '07"
"A_Sales Mar '06"
"A_Sales Mar '05"
"A_Sales Mar '04"
"A_Sales Mar '03"
"A_EPS (Rs) Mar '07"
"A_EPS (Rs) Mar '06"
"A_EPS (Rs) Mar '05"
"A_EPS (Rs) Mar '04"
"A_EPS (Rs) Mar '03"
"A_Net profit/loss Mar '07"
"A_Net profit/loss Mar '06"
"A_Net profit/loss Mar '05"
"A_Net profit/loss Mar '04"
"A_Net profit/loss Mar '03"
"A_NPM (%) Mar '07"
"A_NPM (%) Mar '06"
"A_NPM (%) Mar '05"
"A_NPM (%) Mar '04"
"A_NPM (%) Mar '03"

Thank you
Sandy

 

[Don Guillett]

This is the sort of thing I do for clients often. The basic way I do this is
to goto the web site>right click on the desired table>import to excel. Once
imported>data>import external data>edit query>etc. Then record a macro doing
that>incoporate into a loop using your variable number
http://money.rediff.com/money/jsp/co_results_hy.jsp?companyCode=17040007
"URL;http://money.rediff.com/money/jsp/current_stat.jsp?companyCode=" &
mycodenum

 

[sandip.dhamapurkar]

When I go to the website and right click on the desired table, I do
not get the option "Import to excel" The options which I get are Back,
Forward, Reload, Stop, Bookmark this page, Save page as, Send link,
View background image, Select all, View page source, View page info.

Moreover, you told me how to do it (incorporate into a loop), I don't
know how to do this since I am not much into excel programming.

Thanks
Sandy

 

[Don Guillett]

Excel version?
At the table, Click your mouse>then right click

 

[sandip.dhamapurkar]

Browser : Firefox 2.0.0.11
Excel : 2002 (10.2614.2625)

 

[Don Guillett]

I've not used firefox so can't tell you. Try it the old fashioned way
data>import external data>new web query>type in your url>go>import.

 

[Don Guillett]

I sent you a file

 

[sandip.dhamapurkar]

I was researching on the same thing till now but I get the entire
webpage in excel. Do you mean to say I import the entire webpage on a
temporary sheet and then transfer data from there? If yes, I need
programming statements, not sure how to do it. Another question is, I
have to do the same thing for 33 companies, that is even more
difficult. Iam left out with only two things now, either do it
manually the way I was doing it or check for some kind of a software
which can interact with website and copy the data what I want which I
believe is very difficult to get.

Thank you so much for putting in your efforts.

 

[sandip.dhamapurkar]

Dear Don,

Thank you. I saw the file but again. It has a small table imported
from a web query. I would stress on the same point that being a
beginner with excel programming and queries, I don't know how you did
it and how do I go further from here. Any help from hereon would be
appreciated.

Thank you
Sandy

 

[sandip.dhamapurkar]

Dear Don,

I am still struggling a lot. Confused with loops and how to edit query
programatically. Can you help little bit more?

Regards,
Sandy

 

[Don Guillett]

Send you latest wb to my private email