Messages In This Digest (23 Messages)
- 1a.
- Re: Excel on Macbook From: Randy Harmelink
- 2a.
- Re: Yahoo "high" quotes not working? From: smortonm
- 2b.
- Re: Yahoo "high" quotes not working? From: swoodbri
- 2c.
- Re: Yahoo "high" quotes not working? From: dguillett1
- 2d.
- Re: Yahoo "high" quotes not working? From: Randy Harmelink
- 3a.
- yahoo historical prices and dates From: vegantom
- 3b.
- Re: yahoo historical prices and dates From: Randy Harmelink
- 3c.
- Re: yahoo historical prices and dates From: Tom Vegan
- 3d.
- Re: yahoo historical prices and dates From: Randy Harmelink
- 4a.
- Google Finance data retrieval still not working From: cherry77tree
- 4b.
- Re: Google Finance data retrieval still not working From: Randy Harmelink
- 4c.
- Re: Google Finance data retrieval still not working From: cherry77tree
- 5a.
- Re: downloading free bulk historical stock data From: Market Monk
- 6a.
- Re: Google Finance data retrieval not working as of 8/3/2011 From: Randy Harmelink
- 7a.
- Slow Response From: viraj.pandit@ymail.com
- 7b.
- Re: Slow Response From: Randy Harmelink
- 7c.
- Re: Slow Response From: viraj.pandit@ymail.com
- 7d.
- Re: Slow Response From: viraj.pandit@ymail.com
- 7e.
- Re: Slow Response From: viraj.pandit@ymail.com
- 7f.
- Re: Slow Response From: Randy Harmelink
- 7g.
- Re: Slow Response From: viraj.pandit@ymail.com
- 7h.
- Re: Slow Response From: viraj.pandit@ymail.com
- 7i.
- Re: Slow Response From: Randy Harmelink
Messages
- 1a.
-
Re: Excel on Macbook
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sat Aug 6, 2011 7:04 am (PDT)
No idea.
On Sat, Aug 6, 2011 at 3:27 AM, Au <uanant@ymail.com > wrote:
> Will your add-in work on excel on Macbook. Not on virtual machine actual
> ms office version of excel on Macbook.
>
- 2a.
-
Re: Yahoo "high" quotes not working?
Posted by: "smortonm" smmarder@yahoo.com smortonm
Sat Aug 6, 2011 7:32 am (PDT)
I believe Yahoo may be having some sort of problem. They seem to have
cleared up the last issue of returning 5400000 as a quote. However, I
now find that the request: =RCHGetYahooQuotes(E1,"l1") returns erroneous
quotes in random instances on my spreadsheet. For example if I ask for a
quote in three separate columns for the same stock, I may get different
values. When I refresh the bad quote if then corrects itself. I assume
that others are having similar problems -or am I wrong??
Sid
12.94
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...>
wrote:
>
> BTW, 178.05 was the closing price of IBM on Aug 2nd.
>
> But I'm baffled...
>
> On Fri, Aug 5, 2011 at 9:09 PM, swoodbri woodbri@... wrote:
>
> > I reset my internet options per the message referenced below and
cleared
> > the internet cache, and was able to delete the cookie using HTTP
Spy, but I
> > still am getting the same behavior. I get a new Cookie after I
delete the
> > Cookie, but it does not change the behavior.
> >
> > The internet cache option should not really matter if IE is honoring
the:
> > Pragma: no-cache in the request header. And the response header
also has:
> >
> > Cache-Control: no-cache
> > Pragma: no-cache
> >
> > -Steve
> >
>
- 2b.
-
Re: Yahoo "high" quotes not working?
Posted by: "swoodbri" woodbri@swoodbridge.com swoodbri
Sat Aug 6, 2011 8:58 am (PDT)
Yes, this is the same problem I am having. And I am seeing the problem not only in smf but also my Desktop gadgets that use yahoo for their quotes.
Does any one know if there is a support list/email for download.finance.yahoo.com? It is very frustrating when you can not rely on any of your tools as return valid results! <sigh>
--- In smf_addin@yahoogroups.com , "smortonm" <smmarder@...> wrote:
>
> I believe Yahoo may be having some sort of problem. They seem to have
> cleared up the last issue of returning 5400000 as a quote. However, I
> now find that the request: =RCHGetYahooQuotes(E1,"l1") returns erroneous
> quotes in random instances on my spreadsheet. For example if I ask for a
> quote in three separate columns for the same stock, I may get different
> values. When I refresh the bad quote if then corrects itself. I assume
> that others are having similar problems -or am I wrong??
> Sid
> 12.94
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@>
> wrote:
> >
> > BTW, 178.05 was the closing price of IBM on Aug 2nd.
> >
> > But I'm baffled...
> >
> > On Fri, Aug 5, 2011 at 9:09 PM, swoodbri woodbri@ wrote:
> >
> > > I reset my internet options per the message referenced below and
> cleared
> > > the internet cache, and was able to delete the cookie using HTTP
> Spy, but I
> > > still am getting the same behavior. I get a new Cookie after I
> delete the
> > > Cookie, but it does not change the behavior.
> > >
> > > The internet cache option should not really matter if IE is honoring
> the:
> > > Pragma: no-cache in the request header. And the response header
> also has:
> > >
> > > Cache-Control: no-cache
> > > Pragma: no-cache
> > >
> > > -Steve
> > >
> >
>
- 2c.
-
Re: Yahoo "high" quotes not working?
Posted by: "dguillett1" dguillett1@gmail.com donaldb36
Sat Aug 6, 2011 9:33 am (PDT)
I would imagine that the problem is with YAHOO since I am using an External query and getting a 3 day old quote sometimes and the right quote other times. It was the same about a week ago and then cleared up... until now.
From: swoodbri
Sent: Saturday, August 06, 2011 10:58 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Yahoo "high" quotes not working?
Yes, this is the same problem I am having. And I am seeing the problem not only in smf but also my Desktop gadgets that use yahoo for their quotes.
Does any one know if there is a support list/email for download.finance.yahoo.com? It is very frustrating when you can not rely on any of your tools as return valid results! <sigh>
--- In mailto:smf_addin%40yahoogroups. com, "smortonm" <smmarder@.. .> wrote:
>
> I believe Yahoo may be having some sort of problem. They seem to have
> cleared up the last issue of returning 5400000 as a quote. However, I
> now find that the request: =RCHGetYahooQuotes(E1,"l1") returns erroneous
> quotes in random instances on my spreadsheet. For example if I ask for a
> quote in three separate columns for the same stock, I may get different
> values. When I refresh the bad quote if then corrects itself. I assume
> that others are having similar problems -or am I wrong??
> Sid
> 12.94
> --- In mailto:smf_addin%40yahoogroups. com, Randy Harmelink <rharmelink@ >
> wrote:
> >
> > BTW, 178.05 was the closing price of IBM on Aug 2nd.
> >
> > But I'm baffled...
> >
> > On Fri, Aug 5, 2011 at 9:09 PM, swoodbri woodbri@ wrote:
> >
> > > I reset my internet options per the message referenced below and
> cleared
> > > the internet cache, and was able to delete the cookie using HTTP
> Spy, but I
> > > still am getting the same behavior. I get a new Cookie after I
> delete the
> > > Cookie, but it does not change the behavior.
> > >
> > > The internet cache option should not really matter if IE is honoring
> the:
> > > Pragma: no-cache in the request header. And the response header
> also has:
> > >
> > > Cache-Control: no-cache
> > > Pragma: no-cache
> > >
> > > -Steve
> > >
> >
>
- 2d.
-
Re: Yahoo "high" quotes not working?
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sat Aug 6, 2011 9:50 am (PDT)
At the bottom of the page when looking at the quotes page of a stock, you'll
find a "Report problems" link.
On Sat, Aug 6, 2011 at 8:58 AM, swoodbri <woodbri@swoodbridge.com > wrote:
>
> Does any one know if there is a support list/email for
> download.finance.yahoo.com? It is very frustrating when you can not rely
> on any of your tools as return valid results! <sigh>
>
- 3a.
-
yahoo historical prices and dates
Posted by: "vegantom" vegantom@yahoo.com vegantom
Sat Aug 6, 2011 12:35 pm (PDT)
Hi Randy,
Need help with the following.
Array entering RCHGetYahooHistory(D5,,,,,,, ,"A",0,,, 90,1)
gives me historical prices from yahoo for the ticker in cell D5.
But, how do I get it to print the corresponding dates into the array ?
Thanks !
- 3b.
-
Re: yahoo historical prices and dates
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sat Aug 6, 2011 1:55 pm (PDT)
Did you bother to check the documentation?
Just change the "A" to "DA" and array-enter it over two columns.
On Sat, Aug 6, 2011 at 12:35 PM, vegantom <vegantom@yahoo.com > wrote:
>
> Need help with the following.
>
> Array entering RCHGetYahooHistory(D5,,,,,,, ,"A",0,,, 90,1)
> gives me historical prices from yahoo for the ticker in cell D5.
> But, how do I get it to print the corresponding dates into the array ?
>
- 3c.
-
Re: yahoo historical prices and dates
Posted by: "Tom Vegan" vegantom@yahoo.com vegantom
Sat Aug 6, 2011 2:41 pm (PDT)
Randy,
Yes, I did check the documentation and had tried this. But, it was printing the dates on
both columns.
I had to do them separately on each column to make it work.
That is,
array enter RCHGetYahooHistory(D5,,,,,,, ,"D",0,,, 90,1) for the date
and thenarray enter RCHGetYahooHistory(D5,,,,,,, ,"A",0,,, 90,1) for the closing price
But, array entering RCHGetYahooHistory(D5,,,,,,, ,"DA",0,, ,90,1) onto two columns did not work for me.
Thanks !
From: Randy Harmelink <rharmelink@gmail.com >
To: smf_addin@yahoogroups.com
Sent: Saturday, August 6, 2011 1:55 PM
Subject: Re: [smf_addin] yahoo historical prices and dates
Did you bother to check the documentation?
Just change the "A" to "DA" and array-enter it over two columns.
On Sat, Aug 6, 2011 at 12:35 PM, vegantom <vegantom@yahoo.com > wrote:
>Need help with the following.
>
>Array entering RCHGetYahooHistory(D5,,,,,,, ,"A",0,,, 90,1)
>gives me historical prices from yahoo for the ticker in cell D5.
>But, how do I get it to print the corresponding dates into the array ?
>
- 3d.
-
Re: yahoo historical prices and dates
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sat Aug 6, 2011 2:52 pm (PDT)
Ahhh....it's because you have the "90,1" coded at the end -- it tells the
function to only return 90 rows and 1 column of data. So when you
array-entered it, it copied the first column into the second.
On Sat, Aug 6, 2011 at 2:41 PM, Tom Vegan <vegantom@yahoo.com > wrote:
>
> Yes, I did check the documentation and had tried this. But, it was
> printing the dates on
> both columns.
>
> I had to do them separately on each column to make it work.
>
> That is,
>
> array enter RCHGetYahooHistory(D5,,,,,,, ,"D",0,,, 90,1) for the date
> and then
> array enter RCHGetYahooHistory(D5,,,,,,, ,"A",0,,, 90,1) for the closing
> price
>
> But, array entering RCHGetYahooHistory(D5,,,,,,, ,"DA",0,, ,90,1) onto two
> columns did not work for me.
>
>
- 4a.
-
Google Finance data retrieval still not working
Posted by: "cherry77tree" david.nincic.wg99@wharton.upenn.edu cherry77tree
Sat Aug 6, 2011 12:58 pm (PDT)
I am having the exact same problem as Adam. I am using Excel 2010 and
Windows 7. What happens is when I update an RCHGetElementNumber formula
linking to Google Finance, Internet Explorer windows begin opening up
and only "Error" is returned in the Excel cells. One IE window opens up
per ticker symbol.
The IE windows have the same following URL (with the only difference
being the ticker) and have no content:
about:blank#http://finance.google.com/ finance?fstype= ii&q=IBM
I find Google Finance to have the highest data integrity of all the web
data sources, so it would be a pity to lose access to it.
Does anyone have a clue to what is going on?
If Google Finance data retrievals work for you, what Excel and Windows
versions are you using?
Thanks.
David
> > On Wed, Aug 3, 2011 at 8:40 AM, adam_sommers adam_sommers@ wrote:
> >
> > > I just opened my spreadsheet with the add-in linked data, and all
of sudden
> > > a ton of blank internet explorer windows (I pull over 100 pieces
of data on
> > > 100 companies) started opening up indicating a google finance URL,
and the
> > > data does not come into the spreadsheet. Anyone else having this
problem,
> > > or know how I can fix it?
> > >
> > > I'm thinking I might go back to Yahoo data, but any suggestions
would be
> > > appreciated.
> > >
> >
>
- 4b.
-
Re: Google Finance data retrieval still not working
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sat Aug 6, 2011 2:00 pm (PDT)
What is the date on your smf-elements-3.txt file? I think at one point, it
was actually using the IE object because of changes Google had made. So if
you have an old copy of that file, that may be the culprit?
There is a new version of that file in the "Works in Progress" folder that
was updated earlier this year. You could try:
1. Exit EXCEL
2. Replace that file with the new one
3. Restart EXCEL
...and see if the symptoms go away?
On Sat, Aug 6, 2011 at 12:58 PM, cherry77tree <
david.nincic.wg99@wharton. > wrote:upenn.edu
> I am having the exact same problem as Adam. I am using Excel 2010 and
> Windows 7. What happens is when I update an RCHGetElementNumber formula
> linking to Google Finance, Internet Explorer windows begin opening up
> and only "Error" is returned in the Excel cells. One IE window opens up
> per ticker symbol.
>
> The IE windows have the same following URL (with the only difference
> being the ticker) and have no content:
> about:blank#http://finance.google.com/ finance?fstype= ii&q=IBM
>
> I find Google Finance to have the highest data integrity of all the web
> data sources, so it would be a pity to lose access to it.
>
> Does anyone have a clue to what is going on?
> If Google Finance data retrievals work for you, what Excel and Windows
> versions are you using?
>
- 4c.
-
Re: Google Finance data retrieval still not working
Posted by: "cherry77tree" david.nincic.wg99@wharton.upenn.edu cherry77tree
Sat Aug 6, 2011 5:48 pm (PDT)
Randy that was it! Thanks a million.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...>
wrote:
>
> What is the date on your smf-elements-3.txt file? I think at one
point, it
> was actually using the IE object because of changes Google had made.
So if
> you have an old copy of that file, that may be the culprit?
>
> There is a new version of that file in the "Works in Progress" folder
that
> was updated earlier this year. You could try:
>
> 1. Exit EXCEL
> 2. Replace that file with the new one
> 3. Restart EXCEL
>
> ...and see if the symptoms go away?
>
> On Sat, Aug 6, 2011 at 12:58 PM, cherry77tree <
> david.nincic.wg99@... wrote:
>
> > I am having the exact same problem as Adam. I am using Excel 2010
and
> > Windows 7. What happens is when I update an RCHGetElementNumber
formula
> > linking to Google Finance, Internet Explorer windows begin opening
up
> > and only "Error" is returned in the Excel cells. One IE window
opens up
> > per ticker symbol.
> >
> > The IE windows have the same following URL (with the only difference
> > being the ticker) and have no content:
> > about:blank#http://finance.google.com/ finance?fstype= ii&q=IBM
> >
> > I find Google Finance to have the highest data integrity of all the
web
> > data sources, so it would be a pity to lose access to it.
> >
> > Does anyone have a clue to what is going on?
> > If Google Finance data retrievals work for you, what Excel and
Windows
> > versions are you using?
> >
>
- 5a.
-
Re: downloading free bulk historical stock data
Posted by: "Market Monk" marketmonk777@yahoo.com marketmonk777
Sat Aug 6, 2011 1:13 pm (PDT)
You have a number of choices available to you if you want to obtain free historical data.
The first challenge is creating a list of ticker symbols. You have to decide what type and how many you want. You can get symbols for the 3 major exchanges (NYSE, NAZ, and AMEX) relatively easy. You can also get ticker symbols for OTC BB and OTC Pink Sheets. But those last two groups will add about 23,000 items to the list.
Do you also want preferred issues? How about ETFs and CEFs? What about Mutual Funds?
Do you want just the ticker symbol? Will you need the name or description for each symbol.
Do you want the Sector and Industry that each symbol is assigned to? What what industry classification system (Morningstar, GICS, ICB)?
You can get most if not all of the above by going to a paid data provider.
There is no one data file which contains all of the above information for which you could just use with your COBOL skills (that I am aware of).
I have an excel spreadsheet with all of the above (Morningstar Industry Classifications). It's taken me 100s of hours to create and many hours each week to maintain. In the course of doing so I have learned a ton of tricks and sources for free data. None of it is super easy and in a format that is 100% useable as is. All of the data I download needs to be massaged in some manner.
The easiest thing to do is to go to Finviz and export their data.
http://www.finviz.com/screener. look to bottom right hand corner and click on the export button. You can first add or remove columns of data that you are not interested in before doing so.ashx
That is one of the fastest ways to get a list of tickers for the 3 major exchanges. That are for the most part compatible with Yahoo (for free downloading of historical data).
What most folks don't realize is just how many symbol changes there are each and every month. A lot of symbols are doing the exchange dance (being kicked down to BB or Pink sheets, or brought up to a major exchange), or have a change (SHIPD to SHIP to SHIPD and back), or stop trading due to a halt (can you say Chinese Reverse Merger fraud), or stop trading due to a merger or acquisition, and so forth.
So it's up to you to create this file. And then it is up to you to figure out how to keep it up to date.
I use AmiBroker for all of my charting and scanning needs. It is a program that is better suited for technical analysis. It comes with a downloader called AmiQuote. All of my data is free (Yahoo) as I no longer subscribe to any paid data providers for a number of reasons (being cheap is not in the top five reasons fwiw).
Best of luck
Monk
--- In smf_addin@yahoogroups.com , "ciroverdi77" <ciroverdi77@...> wrote:
>
> Thanks for getting back to me, but:
>
> 1) Being a former COBAL programmer, all I need is a simple ASCII or text file of ALL stock tickers of ALL markets with data elements separated by a comma, or space. I then unstring the data elements via my program -- I do not need to use Excel or its machinations, and found it unnecessarily complicated.
>
> 2) I would expect to simply define the data elements and where to store it.
>
> Can it not be that simple??? Ciro
>
- 6a.
-
Re: Google Finance data retrieval not working as of 8/3/2011
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sat Aug 6, 2011 6:04 pm (PDT)
Maybe this is your problem as well?
http://finance.groups.yahoo. com/group/ smf_addin/ message/15355
On Wed, Aug 3, 2011 at 8:40 AM, adam_sommers <adam_sommers@yahoo.com > wrote:
> I just opened my spreadsheet with the add-in linked data, and all of sudden
> a ton of blank internet explorer windows (I pull over 100 pieces of data on
> 100 companies) started opening up indicating a google finance URL, and the
> data does not come into the spreadsheet. Anyone else having this problem,
> or know how I can fix it?
>
> I'm thinking I might go back to Yahoo data, but any suggestions would be
> appreciated.
>
- 7a.
-
Slow Response
Posted by: "viraj.pandit@ymail.com" viraj.pandit@ymail.com viraj.pandit@ymail.com
Sat Aug 6, 2011 8:14 pm (PDT)
Hello,
I created an excel sheet similar to "SMF-Template-RCHGetYahooQuote s.xls" from Template folder and used "=RCHGetYahooQuotes (" formula. I populated the worksheet with a number of symbols (~200). However, every time I open the file or move a column from one place to another, Excel tries to recalculate (i.e. tries to fetch data from yahoo and populate all the entries etc. It takes VERY long time. My computer is Win7 with Core2Duo processor. Both Excel 2007 and Excel 2003 do the same. I did two things:
1. Tool-->Options-->Calculation- ->Manual
2. Entered
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'from optimize VBA
at the beginning of each function and sub in the add-in code
and
Application.Calculation = xlCalculationAutoma tic
Application.ScreenUpdating = True
before ending each function and sub.
However, it still takes a long time to do anything with the worksheet since it tries to get data from yahoo for each stock and each variable (i.e. last price, P/E etc).
Could you please suggest some ideas with which I could make the worksheet usable?
Else, the MSN Stock Quote Add-in worked fine for me until I figured that I cannot use it on Office 2003 AND saw the flexibility your add-in provided me. I just need to add to it a formula so I can find out the %change in last 7, 30 days etc.
-- The specific function(s) or template(s) you are having problems with. --> Full addin for worksheet runs slow since lots of tickers.
-- The parameter(s) you are using with the function(s). --> Mainly "=RCHGetYahooQuotes("
-- The version of EXCEL you use. --> Office 2003, Office 2007.
-- The operating system you're running EXCEL under. --> Windows 7
-- The steps you took to try to resolve the problem. --> I can email you the excel sheet if you like. Template I used to create it was "SMF-Template-RCHGetYahooQuote s.xls" from Template folder
-- The ticker symbols or URLs that are being used. --> Multiple i.e. AAPL, ADBE and so on
-- The results you expect to get and what you actually get. --> I get the expected results, but it takes VERY long time.
Thanks,
- 7b.
-
Re: Slow Response
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sat Aug 6, 2011 8:50 pm (PDT)
I just opened the template and populated it with 200 ticker symbols.
Recalculation is almost instantaneous here. Did you change it to do
individual functions instead of using a single array-entered function? Each
invocation of the RCHGetYahooQuotes() function requires an access to the
Internet, so if you do 2000 individual functions instead of one
array-entered function to get the 2000 pieces of data, recalculation will
slow significantly.
On Sat, Aug 6, 2011 at 8:14 PM, viraj.pandit@ymail.com <
viraj.pandit@ymail.com > wrote:
>
> I created an excel sheet similar to "SMF-Template-RCHGetYahooQuote s.xls"
> from Template folder and used "=RCHGetYahooQuotes(" formula. I populated
> the worksheet with a number of symbols (~200). However, every time I open
> the file or move a column from one place to another, Excel tries to
> recalculate (i.e. tries to fetch data from yahoo and populate all the
> entries etc. It takes VERY long time. My computer is Win7 with Core2Duo
> processor. Both Excel 2007 and Excel 2003 do the same. I did two things:
> 1. Tool-->Options-->Calculation- ->Manual
> 2. Entered
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual 'from optimize VBA
> at the beginning of each function and sub in the add-in code
> and
> Application.Calculation = xlCalculationAutoma tic
> Application.ScreenUpdating = True
> before ending each function and sub.
> However, it still takes a long time to do anything with the worksheet since
> it tries to get data from yahoo for each stock and each variable (i.e. last
> price, P/E etc).
> Could you please suggest some ideas with which I could make the worksheet
> usable?
>
- 7c.
-
Re: Slow Response
Posted by: "viraj.pandit@ymail.com" viraj.pandit@ymail.com viraj.pandit@ymail.com
Sat Aug 6, 2011 9:57 pm (PDT)
Yes, you are right. I did make the array into single entries. That is right. That must be the root-cause.
So, for me to modify the template, I'll have to understand how to locate and update the array. I will try it tonight, else might have to ask you for a bit of help.
Thanks again.
Regards,
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> I just opened the template and populated it with 200 ticker symbols.
> Recalculation is almost instantaneous here. Did you change it to do
> individual functions instead of using a single array-entered function? Each
> invocation of the RCHGetYahooQuotes() function requires an access to the
> Internet, so if you do 2000 individual functions instead of one
> array-entered function to get the 2000 pieces of data, recalculation will
> slow significantly.
>
> On Sat, Aug 6, 2011 at 8:14 PM, viraj.pandit@... <
> viraj.pandit@...> wrote:
>
> >
> > I created an excel sheet similar to "SMF-Template-RCHGetYahooQuote s.xls"
> > from Template folder and used "=RCHGetYahooQuotes(" formula. I populated
> > the worksheet with a number of symbols (~200). However, every time I open
> > the file or move a column from one place to another, Excel tries to
> > recalculate (i.e. tries to fetch data from yahoo and populate all the
> > entries etc. It takes VERY long time. My computer is Win7 with Core2Duo
> > processor. Both Excel 2007 and Excel 2003 do the same. I did two things:
> > 1. Tool-->Options-->Calculation- ->Manual
> > 2. Entered
> > Application.ScreenUpdating = False
> > Application.Calculation = xlCalculationManual 'from optimize VBA
> > at the beginning of each function and sub in the add-in code
> > and
> > Application.Calculation = xlCalculationAutoma tic
> > Application.ScreenUpdating = True
> > before ending each function and sub.
> > However, it still takes a long time to do anything with the worksheet since
> > it tries to get data from yahoo for each stock and each variable (i.e. last
> > price, P/E etc).
> > Could you please suggest some ideas with which I could make the worksheet
> > usable?
> >
>
- 7d.
-
Re: Slow Response
Posted by: "viraj.pandit@ymail.com" viraj.pandit@ymail.com viraj.pandit@ymail.com
Sun Aug 7, 2011 1:17 am (PDT)
Hello,
I have partial success in making the automation fast. I used the array and it is fine. I'm using "=smfPricesByDates(" for obtaining stock price 7 days ago for a number of stocks listed in a column. I don't think I understand array programming. I tried "{=smfPricesByDates ($Y5:$Y204, T$3:W$3)} " (Ctrl+Enter) but it doesn't take the command. Y5-Y204 have the stock tickers and T3-W3 have the dates for which I need the stock prices for those stocks.
My computer, excel versions are listed below. Please help.
Thanks
--- In smf_addin@yahoogroups.com , "viraj.pandit@..." <viraj.pandit@ ...> wrote:
>
> Yes, you are right. I did make the array into single entries. That is right. That must be the root-cause.
> So, for me to modify the template, I'll have to understand how to locate and update the array. I will try it tonight, else might have to ask you for a bit of help.
> Thanks again.
> Regards,
>
>
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > I just opened the template and populated it with 200 ticker symbols.
> > Recalculation is almost instantaneous here. Did you change it to do
> > individual functions instead of using a single array-entered function? Each
> > invocation of the RCHGetYahooQuotes() function requires an access to the
> > Internet, so if you do 2000 individual functions instead of one
> > array-entered function to get the 2000 pieces of data, recalculation will
> > slow significantly.
> >
> > On Sat, Aug 6, 2011 at 8:14 PM, viraj.pandit@ <
> > viraj.pandit@> wrote:
> >
> > >
> > > I created an excel sheet similar to "SMF-Template-RCHGetYahooQuote s.xls"
> > > from Template folder and used "=RCHGetYahooQuotes(" formula. I populated
> > > the worksheet with a number of symbols (~200). However, every time I open
> > > the file or move a column from one place to another, Excel tries to
> > > recalculate (i.e. tries to fetch data from yahoo and populate all the
> > > entries etc. It takes VERY long time. My computer is Win7 with Core2Duo
> > > processor. Both Excel 2007 and Excel 2003 do the same. I did two things:
> > > 1. Tool-->Options-->Calculation- ->Manual
> > > 2. Entered
> > > Application.ScreenUpdating = False
> > > Application.Calculation = xlCalculationManual 'from optimize VBA
> > > at the beginning of each function and sub in the add-in code
> > > and
> > > Application.Calculation = xlCalculationAutoma tic
> > > Application.ScreenUpdating = True
> > > before ending each function and sub.
> > > However, it still takes a long time to do anything with the worksheet since
> > > it tries to get data from yahoo for each stock and each variable (i.e. last
> > > price, P/E etc).
> > > Could you please suggest some ideas with which I could make the worksheet
> > > usable?
> > >
> >
>
- 7e.
-
Re: Slow Response
Posted by: "viraj.pandit@ymail.com" viraj.pandit@ymail.com viraj.pandit@ymail.com
Sun Aug 7, 2011 1:23 am (PDT)
Hello,
I have partial success in making the automation fast. I used the array and it is fine. I'm using "=smfPricesByDates(" for obtaining stock price 7 days ago for a number of stocks listed in a column. I don't think I understand array programming. I tried "{=smfPricesByDates ($Y5:$Y204, T$3:W$3)} " (Ctrl+Shift+ Enter) but it doesn't take the command. Y5-Y204 have the stock tickers and T3-W3 have the dates for which I need the stock prices for those stocks.
My computer, excel versions are listed below. Please help.
Thanks
--- In smf_addin@yahoogroups.com , "viraj.pandit@..." <viraj.pandit@ ...> wrote:
>
> Yes, you are right. I did make the array into single entries. That is right. That must be the root-cause.
> So, for me to modify the template, I'll have to understand how to locate and update the array. I will try it tonight, else might have to ask you for a bit of help.
> Thanks again.
> Regards,
>
>
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > I just opened the template and populated it with 200 ticker symbols.
> > Recalculation is almost instantaneous here. Did you change it to do
> > individual functions instead of using a single array-entered function? Each
> > invocation of the RCHGetYahooQuotes() function requires an access to the
> > Internet, so if you do 2000 individual functions instead of one
> > array-entered function to get the 2000 pieces of data, recalculation will
> > slow significantly.
> >
> > On Sat, Aug 6, 2011 at 8:14 PM, viraj.pandit@ <
> > viraj.pandit@> wrote:
> >
> > >
> > > I created an excel sheet similar to "SMF-Template-RCHGetYahooQuote s.xls"
> > > from Template folder and used "=RCHGetYahooQuotes(" formula. I populated
> > > the worksheet with a number of symbols (~200). However, every time I open
> > > the file or move a column from one place to another, Excel tries to
> > > recalculate (i.e. tries to fetch data from yahoo and populate all the
> > > entries etc. It takes VERY long time. My computer is Win7 with Core2Duo
> > > processor. Both Excel 2007 and Excel 2003 do the same. I did two things:
> > > 1. Tool-->Options-->Calculation- ->Manual
> > > 2. Entered
> > > Application.ScreenUpdating = False
> > > Application.Calculation = xlCalculationManual 'from optimize VBA
> > > at the beginning of each function and sub in the add-in code
> > > and
> > > Application.Calculation = xlCalculationAutoma tic
> > > Application.ScreenUpdating = True
> > > before ending each function and sub.
> > > However, it still takes a long time to do anything with the worksheet since
> > > it tries to get data from yahoo for each stock and each variable (i.e. last
> > > price, P/E etc).
> > > Could you please suggest some ideas with which I could make the worksheet
> > > usable?
> > >
> >
>
- 7f.
-
Re: Slow Response
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sun Aug 7, 2011 1:26 am (PDT)
It's not ONLY array-entering that speeds up the function -- it's the fact
that Yahoo provides a feed to get data on multiple data items for multiple
tickers. That's why RCHGetYahooQuotes() is the way it is.
You can't array-enter the smfPricesByDates() for multiple ticker symbols.
You can array-enter it for multiple dates. That's because Yahoo does provide
multiple historical dates for a single ticker, but not for multiple tickers.
So there was no reason to make the function an array-entered function on the
ticker symbols. It would still have needed an individual Internet access for
each ticker symbol and not speeded anything up.
If you use a number of those functions, it's going to take a while to update
the worksheet.
On Sun, Aug 7, 2011 at 1:17 AM, viraj.pandit@ymail.com <
viraj.pandit@ymail.com > wrote:
>
> I have partial success in making the automation fast. I used the array and
> it is fine. I'm using "=smfPricesByDates(" for obtaining stock price 7 days
> ago for a number of stocks listed in a column. I don't think I understand
> array programming. I tried "{=smfPricesByDates($Y5:$Y204, T$3:W$3)} "
> (Ctrl+Enter) but it doesn't take the command. Y5-Y204 have the stock
> tickers and T3-W3 have the dates for which I need the stock prices for those
> stocks.
> My computer, excel versions are listed below. Please help.
>
- 7g.
-
Re: Slow Response
Posted by: "viraj.pandit@ymail.com" viraj.pandit@ymail.com viraj.pandit@ymail.com
Sun Aug 7, 2011 1:45 am (PDT)
Hello,
My apologies for posting a poor question an hour before. I partly solved the issue of entering Array by trial and error and online-tutorials. For some reason, it is still not taking "=smfPricesbyDates($Y5:$Y204, T$3:W$3)" (Control+Shift+ Enter i.e. CSE command) (gave "#Value" error); but it took "=smfPricesbyDates( $Y5,T$3:W$ 3)". When I drag the command on multiple rows (each row per stock), Excel functions slower.
***How do I do it the way you did it in the Template "SMF-Template-RCHGetYahooQuote s.xls"? ***
With "=smfPricesbyDates($Y5,T$3:W$ 3)", I get the following error when I try to copy the formula in another row.
"Microsoft Office Excel cannot calculate a formula. This is a circular reference in an open workbook (I doubt it is), but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command."
My computer, excel versions are listed below. Please help.
Thanks,
--- In smf_addin@yahoogroups.com , "viraj.pandit@..." <viraj.pandit@ ...> wrote:
>
> Yes, you are right. I did make the array into single entries. That is right. That must be the root-cause.
> So, for me to modify the template, I'll have to understand how to locate and update the array. I will try it tonight, else might have to ask you for a bit of help.
> Thanks again.
> Regards,
>
>
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > I just opened the template and populated it with 200 ticker symbols.
> > Recalculation is almost instantaneous here. Did you change it to do
> > individual functions instead of using a single array-entered function? Each
> > invocation of the RCHGetYahooQuotes() function requires an access to the
> > Internet, so if you do 2000 individual functions instead of one
> > array-entered function to get the 2000 pieces of data, recalculation will
> > slow significantly.
> >
> > On Sat, Aug 6, 2011 at 8:14 PM, viraj.pandit@ <
> > viraj.pandit@> wrote:
> >
> > >
> > > I created an excel sheet similar to "SMF-Template-RCHGetYahooQuote s.xls"
> > > from Template folder and used "=RCHGetYahooQuotes(" formula. I populated
> > > the worksheet with a number of symbols (~200). However, every time I open
> > > the file or move a column from one place to another, Excel tries to
> > > recalculate (i.e. tries to fetch data from yahoo and populate all the
> > > entries etc. It takes VERY long time. My computer is Win7 with Core2Duo
> > > processor. Both Excel 2007 and Excel 2003 do the same. I did two things:
> > > 1. Tool-->Options-->Calculation- ->Manual
> > > 2. Entered
> > > Application.ScreenUpdating = False
> > > Application.Calculation = xlCalculationManual 'from optimize VBA
> > > at the beginning of each function and sub in the add-in code
> > > and
> > > Application.Calculation = xlCalculationAutoma tic
> > > Application.ScreenUpdating = True
> > > before ending each function and sub.
> > > However, it still takes a long time to do anything with the worksheet since
> > > it tries to get data from yahoo for each stock and each variable (i.e. last
> > > price, P/E etc).
> > > Could you please suggest some ideas with which I could make the worksheet
> > > usable?
> > >
> >
>
- 7h.
-
Re: Slow Response
Posted by: "viraj.pandit@ymail.com" viraj.pandit@ymail.com viraj.pandit@ymail.com
Sun Aug 7, 2011 1:50 am (PDT)
I think I understand most of what you said. So, if I want to get the price of a stock 7, 14, 30 days before today, is "=smfPricesbyDates($Y5,T$3:W$ 3)" the best function or is there another way to do it which will allow me to make it as fast as your cool "SMF-Template- RCHGetYahooQuote s" had? It appears PricebyDates can do the job but not efficiently. Is there some parameter in functions such as RCHGetYahooQuotes (or another) that will let me get 7,14,30 day old prices for multiple stocks?
Thanks again for your guidance.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> It's not ONLY array-entering that speeds up the function -- it's the fact
> that Yahoo provides a feed to get data on multiple data items for multiple
> tickers. That's why RCHGetYahooQuotes() is the way it is.
>
> You can't array-enter the smfPricesByDates() for multiple ticker symbols.
> You can array-enter it for multiple dates. That's because Yahoo does provide
> multiple historical dates for a single ticker, but not for multiple tickers.
> So there was no reason to make the function an array-entered function on the
> ticker symbols. It would still have needed an individual Internet access for
> each ticker symbol and not speeded anything up.
>
> If you use a number of those functions, it's going to take a while to update
> the worksheet.
>
> On Sun, Aug 7, 2011 at 1:17 AM, viraj.pandit@... <
> viraj.pandit@...> wrote:
>
> >
> > I have partial success in making the automation fast. I used the array and
> > it is fine. I'm using "=smfPricesByDates(" for obtaining stock price 7 days
> > ago for a number of stocks listed in a column. I don't think I understand
> > array programming. I tried "{=smfPricesByDates($Y5:$Y204, T$3:W$3)} "
> > (Ctrl+Enter) but it doesn't take the command. Y5-Y204 have the stock
> > tickers and T3-W3 have the dates for which I need the stock prices for those
> > stocks.
> > My computer, excel versions are listed below. Please help.
> >
>
- 7i.
-
Re: Slow Response
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sun Aug 7, 2011 1:59 am (PDT)
Only if you can find a website that provides such data...currently,
smfPricesByDates() is the most efficient way I'm aware of.
On Sun, Aug 7, 2011 at 1:50 AM, viraj.pandit@ymail.com <
viraj.pandit@ymail.com > wrote:
> I think I understand most of what you said. So, if I want to get the price
> of a stock 7, 14, 30 days before today, is "=smfPricesbyDates($Y5,T$3:W$ 3)"
> the best function or is there another way to do it which will allow me to
> make it as fast as your cool "SMF-Template-RCHGetYahooQuote s" had? It
> appears PricebyDates can do the job but not efficiently. Is there some
> parameter in functions such as RCHGetYahooQuotes (or another) that will let
> me get 7,14,30 day old prices for multiple stocks?
>
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