8 New Messages
Digest #2935
Messages
Mon Jan 20, 2014 7:58 am (PST) . Posted by:
"Chuck Grablewski" cgrablew
Randy,
I've attached the spreadsheet. It starts to go off at PEG Ratio - I
don't know if this is a float field. There are 2 sheets in the spreadsheet.
One sheet uses 1 single array formula and the other uses 3 array formulas
(more accurate results but still off). I pasted the correct results from
another spreadsheet. I got the correct results by using individual function
in each cell (no array formula).
Thanks again,
Chuck
PS - Elements 27-32 are now obsolete. Is there another place to get them?
On Mon, Jan 20, 2014 at 1:02 AM, Randy Harmelink <rharmelink@gmail.com>wrote:
>
>
> There is a problem with the "float" field, because Yahoo then populates a
> CSV file with a field that has commas in it. So you could see shifting if
> you're using that item.
>
> If that's not the issue, why don't you attach a copy of a sample workbook.
>
> On Sun, Jan 19, 2014 at 9:57 PM, <cgrablew@gmail.com> wrote:
>
>> I tried the RCHGETYAHOOQUOTES but I'm having problems with it using it
>> as an array formula. It seems to work fine if I pull the Basic Quote Data
>> but if I used it to pull the FUNDAMENTAL elements the data returns gets off
>> shifted (it seems to return a couple of extra values which off shifts the
>> data). Try putting the Fundamental elements across the top with 5 stocks (I
>> used T, TW, AXP, GE, IBM). When you do this as an array formula the data
>> get off shifted and date fields get numeric values (not a format issue).
>> When you do the same with individual formulas in each cell it works fine.
>> I'm using the SMF Add-in from the WORK IN PROGRESS folder so if that's a
>> problem let me know. If this isn't clear, I'll be happy to send you the
>> spreadsheet so you can see it. I'm using Excel 2013 on Windows 7 machine.
>>
>
>
>
I've attached the spreadsheet. It starts to go off at PEG Ratio - I
don't know if this is a float field. There are 2 sheets in the spreadsheet.
One sheet uses 1 single array formula and the other uses 3 array formulas
(more accurate results but still off). I pasted the correct results from
another spreadsheet. I got the correct results by using individual function
in each cell (no array formula).
Thanks again,
Chuck
PS - Elements 27-32 are now obsolete. Is there another place to get them?
On Mon, Jan 20, 2014 at 1:02 AM, Randy Harmelink <rharmelink@gmail.
>
>
> There is a problem with the "float" field, because Yahoo then populates a
> CSV file with a field that has commas in it. So you could see shifting if
> you're using that item.
>
> If that's not the issue, why don't you attach a copy of a sample workbook.
>
> On Sun, Jan 19, 2014 at 9:57 PM, <cgrablew@gmail.
>
>> I tried the RCHGETYAHOOQUOTES but I'm having problems with it using it
>> as an array formula. It seems to work fine if I pull the Basic Quote Data
>> but if I used it to pull the FUNDAMENTAL elements the data returns gets off
>> shifted (it seems to return a couple of extra values which off shifts the
>> data). Try putting the Fundamental elements across the top with 5 stocks (I
>> used T, TW, AXP, GE, IBM). When you do this as an array formula the data
>> get off shifted and date fields get numeric values (not a format issue).
>> When you do the same with individual formulas in each cell it works fine.
>> I'm using the SMF Add-in from the WORK IN PROGRESS folder so if that's a
>> problem let me know. If this isn't clear, I'll be happy to send you the
>> spreadsheet so you can see it. I'm using Excel 2013 on Windows 7 machine.
>>
>
>
>
Attachments with this message:
1 of 1 File(s)
Mon Jan 20, 2014 8:19 am (PST) . Posted by:
"Randy Harmelink" rharmelink
What is returned in cell B4 tells the problem:
"Sorry, the Yahoo! Finance system limits quotes to 200 ticker symbols at a
time, and your request included 207ticker symbols. Please adjust your
request to include 200 or less."
You need to do an RCHGetYahooQuotes() function in sets of 200 tickers.
That's a Yahoo limitation.
In column Q, you return the "Float" field. That's the one I said is
unreliable. Anything after that can be offset by several columns.
If I delete column Q and do:
=RCHGetYahooQuotes(A4:A8,B3:AH3)
...I match your cell-by-cell retrieval, except where you had blanks values
returned by Yahoo.
===========================================> Re: Elements #27 thru #32
I've been looking at several MorningStar web pages:
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?s=0P00001MK8&align=d&t=MMM
http://performance.morningstar.com/Performance/stock/quote-data-strip.action?&t=MMM
http://performance.morningstar.com/Performance/stock/performance-history-1.action?s=0P00001MK8&ndec=2&align=d&t=MMM
On Mon, Jan 20, 2014 at 8:58 AM, Chuck Grablewski <cgrablew@gmail.com>wrote:
>
> I've attached the spreadsheet. It starts to go off at PEG Ratio - I
> don't know if this is a float field. There are 2 sheets in the spreadsheet.
> One sheet uses 1 single array formula and the other uses 3 array formulas
> (more accurate results but still off). I pasted the correct results from
> another spreadsheet. I got the correct results by using individual function
> in each cell (no array formula).
>
> Thanks again,
>
> Chuck
>
> PS - Elements 27-32 are now obsolete. Is there another place to get them?
>
"Sorry, the Yahoo! Finance system limits quotes to 200 ticker symbols at a
time, and your request included 207ticker symbols. Please adjust your
request to include 200 or less."
You need to do an RCHGetYahooQuotes(
That's a Yahoo limitation.
In column Q, you return the "Float" field. That's the one I said is
unreliable. Anything after that can be offset by several columns.
If I delete column Q and do:
=RCHGetYahooQuotes(
...I match your cell-by-cell retrieval, except where you had blanks values
returned by Yahoo.
============
I've been looking at several MorningStar web pages:
http://performance.
http://performance.
http://performance.
On Mon, Jan 20, 2014 at 8:58 AM, Chuck Grablewski <cgrablew@gmail.
>
> I've attached the spreadsheet. It starts to go off at PEG Ratio - I
> don't know if this is a float field. There are 2 sheets in the spreadsheet.
> One sheet uses 1 single array formula and the other uses 3 array formulas
> (more accurate results but still off). I pasted the correct results from
> another spreadsheet. I got the correct results by using individual function
> in each cell (no array formula).
>
> Thanks again,
>
> Chuck
>
> PS - Elements 27-32 are now obsolete. Is there another place to get them?
>
Mon Jan 20, 2014 12:36 pm (PST) . Posted by:
sdavis81
I would like to make a sheet which pulls the 1, 3, 6, and 12 month returns of the Fidelity sector funds.
I wanted to make the same for some ETF's (SPDR ETF's) I follow
I am getting error messages using element numbers 1087-1090 from ETF screener (using either ETF, Stock, or Sector tickers) and also from element numbers 27-29 from MSN (same, trying different symbols)
The MSN elements also don't have a 1 month return choice.
Is there another way? Or any thoughts what I am doing wrong? I don't the the ETFscreener site will pull sector data. MSN should though, but getting an error message there too. I did try element number 40 as a test on MSN and it worked fine.
Thanks for any thoughts.
I wanted to make the same for some ETF's (SPDR ETF's) I follow
I am getting error messages using element numbers 1087-1090 from ETF screener (using either ETF, Stock, or Sector tickers) and also from element numbers 27-29 from MSN (same, trying different symbols)
The MSN elements also don't have a 1 month return choice.
Is there another way? Or any thoughts what I am doing wrong? I don't the the ETFscreener site will pull sector data. MSN should though, but getting an error message there too. I did try element number 40 as a test on MSN and it worked fine.
Thanks for any thoughts.
Mon Jan 20, 2014 2:00 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
For your stated need, I would suggest just using the smfPricesByDates()
function to compute them yourself. Then you can do whatever time periods
you want. For examples, take a look at these files in the "Uploads by Forum
Members" folder:
Price-Performance-Data-Retrieval-2.xls
Price-Performance-Data-Retrieval-v3.xls
Price-Performance-Data-Retrieval-v4.xls
=============================================================================
Elements #27-32, 35 and 36 had to be obsoleted because MSN dropped them
from the stock print page when they redid it last year. New element
definitions are in the "Works in Progress" folder of the FILES area. But
they would have been for stocks only, so they wouldn't have helped with
funds anyway.
Elements #1087-1090 work fine for me for ETFs, or at least the ones I
tried, SPY and IWM. But I did have to redefine those element definitions as
well, in July. Those updates are also in the "Works in Progress" folder.
If it's for mutual funds, why not the MorningStar Total Return template?
Hmmm. How about barchart.com -- it has a number of technical indicators.
See:
SMF-Template-Barchart-Technical-Indicators.xls
BTW, if you've never been to barchart.com, or haven't visited it recently,
you'll need to go to the site with IE first. They have a splash screen with
some agreements you need to go through before you can access the web site.
And the IE cookies need to be updated to reflect that visit and those
choices.
On Mon, Jan 20, 2014 at 1:36 PM, <sdavis81@yahoo.com> wrote:
>
> I would like to make a sheet which pulls the 1, 3, 6, and 12 month returns
> of the Fidelity sector funds.
>
> I wanted to make the same for some ETF's (SPDR ETF's) I follow
>
> I am getting error messages using element numbers 1087-1090 from ETF
> screener (using either ETF, Stock, or Sector tickers) and also from element
> numbers 27-29 from MSN (same, trying different symbols)
>
> The MSN elements also don't have a 1 month return choice.
>
> Is there another way? Or any thoughts what I am doing wrong? I don't the
> the ETFscreener site will pull sector data. MSN should though, but getting
> an error message there too. I did try element number 40 as a test on MSN
> and it worked fine.
>
> Thanks for any thoughts.
>
function to compute them yourself. Then you can do whatever time periods
you want. For examples, take a look at these files in the "Uploads by Forum
Members" folder:
Price-Performance-
Price-Performance-
Price-Performance-
============
Elements #27-32, 35 and 36 had to be obsoleted because MSN dropped them
from the stock print page when they redid it last year. New element
definitions are in the "Works in Progress" folder of the FILES area. But
they would have been for stocks only, so they wouldn't have helped with
funds anyway.
Elements #1087-1090 work fine for me for ETFs, or at least the ones I
tried, SPY and IWM. But I did have to redefine those element definitions as
well, in July. Those updates are also in the "Works in Progress" folder.
If it's for mutual funds, why not the MorningStar Total Return template?
Hmmm. How about barchart.com -- it has a number of technical indicators.
See:
SMF-Template-
BTW, if you've never been to barchart.com, or haven't visited it recently,
you'll need to go to the site with IE first. They have a splash screen with
some agreements you need to go through before you can access the web site.
And the IE cookies need to be updated to reflect that visit and those
choices.
On Mon, Jan 20, 2014 at 1:36 PM, <sdavis81@yahoo.
>
> I would like to make a sheet which pulls the 1, 3, 6, and 12 month returns
> of the Fidelity sector funds.
>
> I wanted to make the same for some ETF's (SPDR ETF's) I follow
>
> I am getting error messages using element numbers 1087-1090 from ETF
> screener (using either ETF, Stock, or Sector tickers) and also from element
> numbers 27-29 from MSN (same, trying different symbols)
>
> The MSN elements also don't have a 1 month return choice.
>
> Is there another way? Or any thoughts what I am doing wrong? I don't the
> the ETFscreener site will pull sector data. MSN should though, but getting
> an error message there too. I did try element number 40 as a test on MSN
> and it worked fine.
>
> Thanks for any thoughts.
>
Mon Jan 20, 2014 5:37 pm (PST) . Posted by:
sdavis81
My thanks Randy!
I will look over these files.
What I have been doing is using Barcharts and copying the HTML tables over. But that doesn't line up straight a lot of time and takes some reformatting. I thought I could probably automate it a little better.
I have not used the Prices by Dates. Will check it out.
Thanks
Scott
I will look over these files.
What I have been doing is using Barcharts and copying the HTML tables over. But that doesn't line up straight a lot of time and takes some reformatting. I thought I could probably automate it a little better.
I have not used the Prices by Dates. Will check it out.
Thanks
Scott
Mon Jan 20, 2014 1:24 pm (PST) . Posted by:
dwstelsel
Awesome solution using Google Finance, but does not work for a few stocks, i.e. DE, DOW.
Mon Jan 20, 2014 2:02 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
That's because those are not valid stock ticker symbols on Google Finance.
On Mon, Jan 20, 2014 at 2:24 PM, <dwstelsel@yahoo.com> wrote:
> Awesome solution using Google Finance, but does not work for a few
> stocks, i.e. DE, DOW.
>
On Mon, Jan 20, 2014 at 2:24 PM, <dwstelsel@yahoo.
> Awesome solution using Google Finance, but does not work for a few
> stocks, i.e. DE, DOW.
>
Mon Jan 20, 2014 8:02 pm (PST) . Posted by:
hamishthedenizen
Try NYSE:DOW as the ticker.
Tidak ada komentar:
Posting Komentar