Messages In This Digest (5 Messages)
- 1a.
- Re: Yahoo Quote failures - work-around? From: Market Monk
- 1b.
- Re: Yahoo Quote failures - work-around? From: Randy Harmelink
- 1c.
- Re: Yahoo Quote failures - work-around? From: Market Monk
- 1d.
- Re: Yahoo Quote failures - work-around? From: Randy Harmelink
- 1e.
- Re: Yahoo Quote failures - work-around? From: Market Monk
Messages
- 1a.
-
Re: Yahoo Quote failures - work-around?
Posted by: "Market Monk" marketmonk777@yahoo.com marketmonk777
Thu Aug 11, 2011 9:19 pm (PDT)
Before we jump into various pay services, can I ask how many symbols you are trying to obtain, how often, and how much historical data?
--- In smf_addin@yahoogroups.com , "MikeM" <mikemcq802@...> wrote:
>
> Which of the pay services would provide array entered quote retrievals?
>
> Yes, my work-around is just to always return the date to make sure it's what I expect. I then added a button on my spreadsheet to use Randy's SMFForceRecalculation function to re-get the quotes if they are wrong.
>
> To help identify what's "off", I use Conditional Formatting to highlight quotes when the dates don't match. I don't see all the detailed quotes on some of my summary tabs and this allows me highlight the problems better.
>
> So, it's not a work-around in the sense it avoids the problem. But, a work-around in the sense that it provides an easy and fast way to get the correct results.
>
> Randy, I couldn't find the doc for SMFForceRecalculation, is that still the recommended way to refresh the cache?
>
>
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > I don't know of any work-around, other than getting a pay service. There
> > isn't another free service that I know of that returns quotes for a
> > multitude of tickers in a single Internet access.
> >
> > If you are getting a number of quotes, getting them by individual ticker
> > will take a long time.
> >
> > On Wed, Aug 10, 2011 at 6:57 PM, Daniel Perciballi <dperciballi@>wrote:
> >
> > >
> > > THAN K GOD someone else is having this problem. It started for me about a
> > > month ago (working flawlessly prior to that). Today I am continually
> > > getting quotes from Aug 2nd as well.
> > >
> > > What is your workaround? You request the date as welll and wait until all
> > > the dates are "today"?
> > >
> > > I also retrieve blocks of quotes in the same sheet and refresh accordingly.
> > >
> >
>
- 1b.
-
Re: Yahoo Quote failures - work-around?
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 9:21 pm (PDT)
In bulk, with a single Internet access?
I wouldn't want to convert array-entered RCHGetYahooQuotes() usage to an
individual Internet access for each ticker symbol. When you get to hundreds
of ticker symbols, that could be very slow to update.
On Thu, Aug 11, 2011 at 9:18 PM, Market Monk <marketmonk777@yahoo.com >wrote:
> Outside of Yahoo, folks can get quotes from Google and MSN Finance. Not
> sure if add-in works for those so a separate downloader would be required.
>
- 1c.
-
Re: Yahoo Quote failures - work-around?
Posted by: "Market Monk" marketmonk777@yahoo.com marketmonk777
Thu Aug 11, 2011 9:31 pm (PDT)
Can Excel access CSV or TXT files to pull quote data into a range of cells somewhat with ease and relatively fast?
External downloaders can retrieve a ton of quotes for symbols and then store the data in individual text files.
These downloaders can also just retrieve the current date and append the quote to the file.
I am just thinking out of the box and am hopefully not offending anyone with my ignorance of how the add-in works and how folks use the data within excel.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> In bulk, with a single Internet access?
>
> I wouldn't want to convert array-entered RCHGetYahooQuotes() usage to an
> individual Internet access for each ticker symbol. When you get to hundreds
> of ticker symbols, that could be very slow to update.
>
> On Thu, Aug 11, 2011 at 9:18 PM, Market Monk <marketmonk777@...>wrote:
>
> > Outside of Yahoo, folks can get quotes from Google and MSN Finance. Not
> > sure if add-in works for those so a separate downloader would be required.
> >
>
- 1d.
-
Re: Yahoo Quote failures - work-around?
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 9:41 pm (PDT)
Yes, EXCEL can access CSV or TXT files. In fact, the Yahoo current quotes
process is a CSV file that Yahoo provides.
I'm curious how an external downloader will speed things up if it still
needs to get something from the Internet for each ticker symbol?
Let's say quotes for 600-1000 tickers, for example?
On Thu, Aug 11, 2011 at 9:31 PM, Market Monk <marketmonk777@yahoo.com >wrote:
> Can Excel access CSV or TXT files to pull quote data into a range of cells
> somewhat with ease and relatively fast?
>
> External downloaders can retrieve a ton of quotes for symbols and then
> store the data in individual text files.
>
> These downloaders can also just retrieve the current date and append the
> quote to the file.
>
> I am just thinking out of the box and am hopefully not offending anyone
> with my ignorance of how the add-in works and how folks use the data within
> excel.
>
- 1e.
-
Re: Yahoo Quote failures - work-around?
Posted by: "Market Monk" marketmonk777@yahoo.com marketmonk777
Thu Aug 11, 2011 10:14 pm (PDT)
I haven't used your add-in to access that many symbols so can't speak of any speed improvements. So allow me to explain that which I do know.
My only experience with retrieving quote data is with AmiBroker's AmiQuote. www.amibroker.com
AmiQuote (AQ) is a downloader that will retrieve either a single quote (current) or a multitude of quotes for a single ticker or 10,000s of tickers. The data is store in a simple text file with the name of the ticker and either .aqd (daily ie single quote) or .aqh (historical).
When it comes to retrieving quotes from Yahoo they have two types of servers - current (which has delayed intraday quotes) and historical.
You can request current quotes for an unlimited amount of symbols (up to 200 at a time per batch, automatically handled by AQ). Your request will get directed to the current servers.
When you request historical data for a batch of tickers, you can have 10 requests open at a time (again all handled by AmiQuote).
To give you an idea of speed, I download 7000 symbols (single quote) in about 3 minutes. Should I want to download my entire database for all available quotes since 1970 (30+ years of daily data) it takes about 15 to 35 minutes (depending on Yahoo).
I don't know where the data comes from when you make a request to pull data down using the add-in. AQ gets data from the ichart.finace.yahoo.com server.
I don't seem to be having this issue with getting old data - I do check each night by running various routines to locate stocks that are not updating. That's the main way I maintain my own database. It helps catch symbols that have changed (SHIP to SHIPD) or buyouts TRAD, etc.
A potential advantage of using an external data downloader is that you only have to retrieve the quotes for the days you are missing. ie go on vacation for a week and then come back and retrieve 5 days of historical data for all of your symbols. Or retrieve today's quotes for all of your symbols in 3 minutes. That could be a big time saver.
Now that all of the quote data has been downloaded into files with the ticker name and an .aqh extension, how can you use it? This part I am not an expert in. How to retrieve or extract the data for the various dates you are interested in.
I could upload a few AQH files for you folks to play with. This is the file format for AAPL
$NAME AAPL
11-08-2011,370.52,375.45, 364.72,373. 70,26482900, 373.70
10-08-2011,371.15,374.65, 362.50,363. 69,31358700, 363.69
09-08-2011,361.30,374.61, 355.00,374. 01,38629400, 374.01
08-08-2011,361.69,367.77, 353.02,353. 21,40810600, 353.21
05-08-2011,380.44,383.50, 362.57,373. 62,42992200, 373.62
04-08-2011,389.41,391.32, 377.35,377. 37,31083700, 377.37
03-08-2011,390.98,393.55, 382.24,392. 57,26388200, 392.57
02-08-2011,397.65,397.90, 388.35,388. 91,22810100, 388.91
01-08-2011,397.78,399.50, 392.37,396. 75,21829000, 396.75
29-07-2011,387.64,395.15, 384.00,390. 48,22568800, 390.48
28-07-2011,391.62,396.99, 388.13,391. 82,21200200, 391.82
27-07-2011,400.59,402.64, 392.15,392. 59,23523900, 392.59
26-07-2011,400.00,404.50, 399.68,403. 41,17006300, 403.41
25-07-2011,390.35,400.00, 389.62,398. 50,21045000, 398.50
22-07-2011,388.32,395.05, 387.75,393. 30,18395400, 393.30
21-07-2011,386.95,390.06, 383.90,387. 29,18787700, 387.29
You can see the date format is DD-MM-YYYY
the last column I believe is the adjusted close
There is another product called ML Downloader as well.
http://www.trading-tools.com/ mld.htm
Many speak highly of it. I have not used it.
Again, my only intention is to suggest a "potential" solution to obtaining large amounts of quote data.
Monk
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Yes, EXCEL can access CSV or TXT files. In fact, the Yahoo current quotes
> process is a CSV file that Yahoo provides.
>
> I'm curious how an external downloader will speed things up if it still
> needs to get something from the Internet for each ticker symbol?
>
> Let's say quotes for 600-1000 tickers, for example?
>
> On Thu, Aug 11, 2011 at 9:31 PM, Market Monk <marketmonk777@...>wrote:
>
> > Can Excel access CSV or TXT files to pull quote data into a range of cells
> > somewhat with ease and relatively fast?
> >
> > External downloaders can retrieve a ton of quotes for symbols and then
> > store the data in individual text files.
> >
> > These downloaders can also just retrieve the current date and append the
> > quote to the file.
> >
> > I am just thinking out of the box and am hopefully not offending anyone
> > with my ignorance of how the add-in works and how folks use the data within
> > excel.
> >
>
Need to Reply?
Click one of the "Reply" links to respond to a specific message in the Daily Digest.
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
Tidak ada komentar:
Posting Komentar