How to download data?

Discuss your favourite picks, broker, and trading or investment style.
Post Reply
George$
Veteran Contributor
Veteran Contributor
Posts: 2612
Joined: 18 Feb 2005 20:46
Location: Toronto

How to download data?

Post by George$ »

I'm trying to study the stock price variability (of GE for example) over an extended time period (say 40 years) and download the data into an Excel sheet on my computer.

I've never done this before and I need help.

In going to Yahoo at http://finance.yahoo.com/q/hp?s=GE
I can get a download and load the closing (daily, weekly or monthly prices) into an Excel but the prices are not adjusted for stock splits.

I hope I don't have to do this manually - or do I?
User avatar
NormR
Veteran Contributor
Veteran Contributor
Posts: 5234
Joined: 18 Feb 2005 11:19
Contact:

Post by NormR »

I'm trying to study the stock price variability (of GE for example) over an extended time period (say 40 years) and download the data into an Excel sheet on my computer.
Use I.E. and the upgraded charts at

http://moneycentral.msn.com/investor/ch ... ?Symbol=ge

to download direct to .xls
User avatar
gummy
Veteran Contributor
Veteran Contributor
Posts: 2173
Joined: 19 Feb 2005 17:38
Location: Burlington, Ontari-ari-ari-O
Contact:

Post by gummy »

At the link you posted (to GE historical prices), it says (about the *Adj. Close):
* Close price adjusted for dividends and splits.

This spreadsheet does it ... using the prices called *Adj. Close
http://www.gummy-stuff.org/SS/simplest-download.xls

A gaggle of other, more sophisticated spreadsheets that download prices are available here:
http://www.gummy-stuff.org/SS/
They usually have names that include the word download.

There's some here, too:
kent
Newcomer
Newcomer
Posts: 4
Joined: 12 May 2005 11:14

downloading intraday data

Post by kent »

Does anyone know how to access and download intraday stock quotes on Canadian Stocks?

You can get 10 days of 5-minute data on US stocks on MSN, both the chart and exportable data.

Both Yahoo and Bigcharts have minute by minute charts for Canadian stocks, but is there a set of codes that enables you to get at the data itself, say from the Yahoo server?

MSN (via Sympatico) has minute by minute stock charts, but unlike the US site, there are no buttons or links to the data.

Any ideas would be welcome!

Kent
User avatar
Norbert Schlenker
Veteran Contributor
Veteran Contributor
Posts: 7960
Joined: 16 Feb 2005 09:56
Location: An Argument Surrounded By Water
Contact:

Post by Norbert Schlenker »

Real time, 20 minute delays, or afterwards?
Nothing can protect people who want to buy the Brooklyn Bridge.
kent
Newcomer
Newcomer
Posts: 4
Joined: 12 May 2005 11:14

Post by kent »

What I had specifically in mind was: wanting to be able to download the data which is portrayed on the minute-by-minute stock charts that appear on Yahoo or Bigcharts. This download would be done at, say the end of the day, for the previous x days, just as you can get daily historical data from Yahoo for the previous 10 or so years. [x= as long as possible]

Thus, I wasn't thinking of real-time or 20 minute delayed quotes, just 'historical" price data at a frequency higher than 'daily'. For example, on MSN, you can get and download price-volume data for the previous 10 days, given in 5 minute intervals but only for US stocks. You can do this at any time during the day and get the data making up the most recent [ie 20 minute delayed] chart.

Yahoo must have the data archived somewhere, because they are able to generate the charts [ daily or high frequency intraday] at the click of a button. But they only provide a download panel which offers you the choice of daily, weekly, or monthly. Is there a way of getting at the high-frequency data.

So, not true 'real-time streaming quotes'. for which you have to pay.

Hope this clarifies what I originally meant to explain.

Thanks

Kent
User avatar
Norbert Schlenker
Veteran Contributor
Veteran Contributor
Posts: 7960
Joined: 16 Feb 2005 09:56
Location: An Argument Surrounded By Water
Contact:

Post by Norbert Schlenker »

kent wrote:So, not true 'real-time streaming quotes'. for which you have to pay.
I think you will find that you have to pay for what you are asking for, either in time or money.

If you're willing to spend $, I can refer you to http://www.alphatrade.com, which I use for Market by Order on the TSX. Part of my package is a complete record of every trade going back a week, but Market by Order is more than what you want. As far as I can see from their pricing page, you can get as many TSX stocks as you could possibly want by trade - so organize them yourself into 1 or 5 or 10 minute intervals - for the last five trading days. That will run you US$34-44 a month as far as I can tell. Buying historical data from any other provider seems likely to cost more than that.

If you're willing to spend time and accept some slop, you can also try digitizing a chart out of Yahoo or Globeinvestor. Good digitizers are expensive pieces of software but, if you look around, you can find free- or share-ware that will do a half decent job. It's labour intensive though, so it's not suitable for high volumes or if your time is worth more than a few bucks an hour.

May I ask why you want this data in the first place? If it's for technical analysis, expect to be razzed.
Nothing can protect people who want to buy the Brooklyn Bridge.
User avatar
yielder
Veteran Contributor
Veteran Contributor
Posts: 4911
Joined: 16 Feb 2005 07:47
Location: Hastings, Ontario

Post by yielder »

expect to be razzed.
Yah but you've already acknowledged that you can't pick stocks for love nor money. So why would we expect you to be a short term trader? :wink:

A discussion on short term technical analysis based trading would be interesting.
kent
Newcomer
Newcomer
Posts: 4
Joined: 12 May 2005 11:14

Post by kent »

First, thank you to Norbert Schlenker for alerting me to data digitizing software. A quick search on Google found an excellent one: Digitizeit, [www.digitizeit.de] which I have just purchased for $50.00Cdn. It is easy to use, and digitizes data which is accurate to the penny.[At least, I grabbed the every-5 minute chart for Inco in BigCharts and digitized it in no time]

The reason for wanting 5-minute data rather than daily data is turn-around time: 12 observations per hour, 7 hours per day for 10 days gives you 840 points. That's the equivalent of about 3.5 years of daily data [840 / 252 trading days]. Thus, if you want to do projections, say the next 84 points into the future, with 5-minute data, you know exactly what happened by the end of the day, whereas if you projected out 84 points of daily data, you would have to wait about 4 months to get the same evaluation.

As for your threat / offer to razz me if I said I wanted to do technical analysis: guilty, but with extenuating circumstances!

First of all, if you look at a chart of 5-minute data, or one of daily data, without knowing which is which, you couldn't tell them apart, except for the value changes...i.e. the scaling on the vertical axis. So, whatever one does with daily data, can be done just as well, or just as poorly, with 5 minute data.

My personal interest is in the 'gummy stuff' as the creator of this website calls it. I've always been interested in something mathematicians call 'spectral analysis of time series' and statistical prediction [ aka the kind of stuff market risk management types at banks do ]. So, of course, the models are alway wanting to be fed more data. But in truth, it's really just a digitized ruler and pencil with which we draw trend lines on a paper graph.

In any event, all those sacred cows of technical analysis take on a different perspective when you move from daily to 5-minute data. The 200 day moving average seems such a well-considered long perspective, but 200 points on a 5-minute chart elapse within about 2.5 days.

In any event, a topic of possible future interest

Kent
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Post by Shakespeare »

spectral analysis of time series
Or, as a certain button used to depict, "If you can't see it, FT [Fourier Transform] it." :wink:
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
uhoh
Veteran Contributor
Veteran Contributor
Posts: 3437
Joined: 03 Dec 2005 09:44
Location: Canada

Downloading stock feeds in to Excel - a how-to guide PCmag

Post by uhoh »

In the past the subject of accessing stock feeds through excel has come up - I did a search and it was on a number of threads so am beginning a new one for the purpose of posting an article in this month's PC Magazine (the Ziff-Davis one) that details how to do this:

http://www.pcmag.com/article2/0,1895,1932777,00.asp

I have no idea if this applies to the Canadian markets.

Here is a snippet:

By Mark J. Brickley
You can add live stock data to an Excel spreadsheet.

Well, "live" might be a bit of an exaggeration—but a 15-minute delay for actual stock data isn't bad! You could use this data to determine the current value of your stock holdings, figure out your gains or losses, and do many other calculations. There are two approaches to gaining access to stock data.


The easier way takes advantage of the Smart Tag feature that arrived with Microsoft Office XP. And for those who need to keep track of more than just a few stocks, there's the extremely handy MSN MoneyCentral Stock Quotes add-in...

<snip>

If you have smart tags activated (select Tools | AutoCorrect, then the Smart Tags tab, and check Label data with Smart Tags), any stock abbreviation on your spreadsheet will be marked. Simply click the Smart Tag icon in the lower right of the cell and select Insert Refreshable Stock Price. The downside: Smart Tags drag in a huge area of data—about 12 rows by 16 columns for each stock.
[i]It could be that the purpose of my life is to serve as a warning for others[/i] ~ [i]anon[/i]
Mouly
Contributor
Contributor
Posts: 684
Joined: 16 Aug 2005 00:35
Location: zzz

Post by Mouly »

Pretty cool but it looks like it may only work with US stocks.
I played with it a bit and it would recognize MMM as a stock but not PCA.
Maybe it does work with TSE listed stocks but I couldn't make it work initially.
User avatar
Norbert Schlenker
Veteran Contributor
Veteran Contributor
Posts: 7960
Joined: 16 Feb 2005 09:56
Location: An Argument Surrounded By Water
Contact:

Post by Norbert Schlenker »

Try CA:PCA.
Nothing can protect people who want to buy the Brooklyn Bridge.
cardhu
Contributor
Contributor
Posts: 726
Joined: 05 Mar 2005 21:07

Post by cardhu »

I use the MSN add-in ... its pretty handy. As norbert pointed out, add the prefix "ca:" to Canadian stock symbols. Works for both TSX & whatever the venture exchange is called these days (formerly Vancouver??)
bill2009
Contributor
Contributor
Posts: 351
Joined: 03 Mar 2005 14:22

Post by bill2009 »

what's the MSN add-in?
User avatar
gummy
Veteran Contributor
Veteran Contributor
Posts: 2173
Joined: 19 Feb 2005 17:38
Location: Burlington, Ontari-ari-ari-O
Contact:

Post by gummy »

If you just wanted the latest numbers for price and/or volume and/or 52-week high and/or etc. etc., you could use Yahoo tags:
http://www.gummy-stuff.org/Yahoo-data.htm
arnyk
Veteran Contributor
Veteran Contributor
Posts: 1089
Joined: 04 Dec 2005 10:48

Post by arnyk »

That's a swell spreadsheet gummy, but I've entered some of my holdings and none of the P/E's show up. Actually the only one that showed up (out of 20ish holdings) was CM and that was some ridiculous 200ish P/E.

Then again, the holdings were all Canadian, and we always get the short end of the stick. Stockhouse.ca has pretty accurate P/Es...
cardhu
Contributor
Contributor
Posts: 726
Joined: 05 Mar 2005 21:07

Post by cardhu »

bill2009 wrote:what's the MSN add-in?
The MSN Money Stock Quotes add-in for Excel, that was mentioned in the PC World article.
Check here
DenisD
Veteran Contributor
Veteran Contributor
Posts: 4081
Joined: 19 Feb 2005 01:24
Location: Calgary

Post by DenisD »

arnyk wrote:That's a swell spreadsheet gummy, but I've entered some of my holdings and none of the P/E's show up. Actually the only one that showed up (out of 20ish holdings) was CM and that was some ridiculous 200ish P/E.
I've noticed that the accuracy of dividend amounts and dates from Yahoo has deteriorated over the past year or two. Even for US shares. :(
User avatar
gummy
Veteran Contributor
Veteran Contributor
Posts: 2173
Joined: 19 Feb 2005 17:38
Location: Burlington, Ontari-ari-ari-O
Contact:

Post by gummy »

... I've entered some of my holdings and none of the P/E's show up.
Funny, eh? Yahoo don't do much fer Canucks.

I get e-mail saying:
"That %#$@!*! spreadsheet of yours don't work good. I don't get this or that when I click the button."
I have to explain that the blame lies elsewhere ... like with Yahoo. :?
arnyk
Veteran Contributor
Veteran Contributor
Posts: 1089
Joined: 04 Dec 2005 10:48

Post by arnyk »

MSN any better then? IS it complicated to switch the spreadsheet from feeding from YHOO to MSN?

Edit Update: Just checked out the MSN add-on, P/Es work just fine and dandy, if only we could harness the power of the MSN P/E, and combine with the Yahoo Ex-div Dates. ;)
arnyk
Veteran Contributor
Veteran Contributor
Posts: 1089
Joined: 04 Dec 2005 10:48

Post by arnyk »

New lappy, Vista, MSN stock quote add on for Excel installs fine but it won't retrieve the data I need. Further research on forums seems to indicate that the Excel stock quotes don't work fer Vista.. which sucks because it forms the basis for me DCF spreadsheet - it gives me the MC value of each stock and updates automatically - thus giving me an immediate real-time (ish) valuation check on all the companies I track. No MC value = useless model. :cry:

Any techy guys here with some tips? :?:
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Post by Shakespeare »

Any techy guys here with some tips
Don't be one of Bill's beta testers. :wink:
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
arnyk
Veteran Contributor
Veteran Contributor
Posts: 1089
Joined: 04 Dec 2005 10:48

Post by arnyk »

Shakespeare wrote:
Any techy guys here with some tips
Don't be one of Bill's beta testers. :wink:
I think that's what'll eventually happen - I'll have to make the switch. Sucks though, everything is running fairly smoothly thus far - but I have to say the functionality of my DCF model trumps everything else. If everything is working EXCEPT my spreadsheet, I'll still dump Vista. :lol:
User avatar
Bylo Selhi
Veteran Contributor
Veteran Contributor
Posts: 29494
Joined: 16 Feb 2005 10:36
Location: Waterloo, ON
Contact:

Post by Bylo Selhi »

arnyk wrote:Excel stock quotes don't work fer Vista.
According to Bill's Borgs that's only a "rumor." You should ignore those rumors and instead "proceed with confidence" :D Don't wait for Vista SP1, pleads Microsoft
Microsoft has launched a "fact rich" program to help customers understand why they should "proceed with confidence" in rolling out Vista across all their PCs. "Some customers may be waiting to adopt Windows Vista because they've heard rumors about device or application compatibility issues, or because they think they should wait for a service pack release," the company said in a newsletter. To help partners and customers get the real story, Microsoft has created a comprehensive set of fact-rich materials illustrating how Windows Vista is ready today and tomorrow."...
Sedulously eschew obfuscatory hyperverbosity and prolixity.
Post Reply