Rabu, 26 Maret 2014

[smf_addin] Digest Number 3021

15 Messages

Digest #3021
1c
Re: Screening by company age... by "Sharon McAllister" sharonnraymc
1d
Re: Screening by company age... by "Sharon McAllister" sharonnraymc
2a
Re: Obscure (?) UK Funds by nick.roche@ymail.com
2b
Re: Obscure (?) UK Funds by "Randy Harmelink" rharmelink
3b
Re: Retrieving quotes from Yahoo and Google by "Randy Harmelink" rharmelink
4a
4b
Re: Issue with smfGetAdvFNElement formula by "Randy Harmelink" rharmelink
5b
Re: Google data retrieval issues by "Randy Harmelink" rharmelink
6a
6b
Re: The New File Store on Your Website by "Randy Harmelink" rharmelink
7
businessweek data by navelhunt

Messages

Wed Mar 26, 2014 9:58 am (PDT) . Posted by:

dmatu2000

Thanks Randy. It is a different approach but I think I can use that for my goals. Basically, I am trying to determine if a stock has at least ten years of financials available before doing anymore research on it.

Wed Mar 26, 2014 10:44 am (PDT) . Posted by:

mikemcq802

In that case you could subscribe to Morningstar and use their Premium Stock Screener.

It allows you to check financial metrics from 10 years ago so just check for companies with some non-zero number in the dozen or so items in the screener.

Wed Mar 26, 2014 1:48 pm (PDT) . Posted by:

"Sharon McAllister" sharonnraymc

Mike, that's a good suggestion. Morningstar's premium service screening tool will let you look for 10 Year Return = Not Available (This returns 11,181 companies)

Morningstar will also let you screen with this horrible not-not logic, 10 Year Return not = Not Available (This returns 7299 companies)

Screening tools can be fabulous tools to isolate a meaningful number of stocks in a universe based on your investing objectives. You need more than the 10-year criteria to winnow down the universe. <vbg>

--Sharon

Wed Mar 26, 2014 1:48 pm (PDT) . Posted by:

"Sharon McAllister" sharonnraymc

PS. Many libraries in the US carry Morningstar's premium service and make it available to their patrons. -- Sharon

________________________________
From: Sharon McAllister <sharonnraymc@yahoo.com>
To: "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com>
Sent: Wednesday, March 26, 2014 3:47 PM
Subject: Re: Screening by company age...


Mike, that's a good suggestion. Morningstar&#39;s premium service screening tool will let you look for 10 Year Return = Not Available (This returns 11,181 companies)

Morningstar will also let you screen with this horrible not-not logic, 10 Year Return not = Not Available (This returns 7299 companies)

Screening tools can be fabulous tools to isolate a meaningful number of stocks in a universe based on your investing objectives. You need more than the 10-year criteria to winnow down the universe. <vbg>

--Sharon

Wed Mar 26, 2014 11:37 am (PDT) . Posted by:

nick.roche@ymail.com

Thanks to you both - I've been trying for some time to work out how to access closing prices for UK and Irish funds.
Now, with the benefit of the information in this thread and using a combination of "RCHGetYahooHistory()" and "RCHGetTableCell()" where the history is missing, I have managed to value my entire portfolio.
It is however odd that all my funds do appear in Yahoo with a closing price like this one:
http://finance.yahoo.com/q;_ylt=AkL2UcAUoOAsKyhsfQfkucOiuYdG;_ylu=X3oDMTBxdGVyNzJxBHNlYwNVSCAzIERlc2t0b3AgU2VhcmNoIDEx;_ylg=X3oDMTBsdWsyY2FpBGxhbmcDZW4tVVMEcHQDMgR0ZXN0Aw--;_ylv=3?uhb=uhb2&fr=uh3_finance_vert_gs&type=2button&s=GB00B41QSW23.L http://finance.yahoo.com/q;_ylt=AkL2UcAUoOAsKyhsfQfkucOiuYdG;_ylu=X3oDMTBxdGVyNzJxBHNlYwNVSCAzIERlc2t0b3AgU2VhcmNoIDEx;_ylg=X3oDMTBsdWsyY2FpBGxhbmcDZW4tVVMEcHQDMgR0ZXN0Aw--;_ylv=3?uhb=uhb2&fr=uh3_finance_vert_gs&type=2button&s=GB00B41QSW23.L
and I'm therefore puzzled that there doesn't appear to be a defined smf_addin element that will access the closing price using "RCHGetElementNumber()".
Am I missing something?
Nick

Wed Mar 26, 2014 11:54 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I didn't do elements for current quotes information for several reasons:

-- Yahoo's CSV interface was much, much faster
-- Yahoo's CSV interface allowed for easy updating (originally elements
COULDN'T update)
-- RCHGetYahooQuotes() existed before I wrote RCHGetElementNumber(), so
there was no need

And, over time, the page scraping to get closing prices from Yahoo has
changed at least a half-dozen times. So I'm glad I didn't. I had defined an
element for "Last Price" from MSN and always regretted it. It also changed
a number of times, and the last time they updated the web page they made it
useless.

Since I only do U.S. stocks, obscure UK and Irish funds wouldn't have been
a consideration. Keep in mind I'm basically just sharing something I
originally wrote for my own use. Even though I've since added a lot of
things I don't use at all. :)

On Wed, Mar 26, 2014 at 10:29 AM, <nick.roche@ymail.com> wrote:

> Thanks to you both - I've been trying for some time to work out how to
> access closing prices for UK and Irish funds.
>
> Now, with the benefit of the information in this thread and using a
> combination of "RCHGetYahooHistory()" and "RCHGetTableCell()" where the
> history is missing, I have managed to value my entire portfolio.
>
> It is however odd that all my funds do appear in Yahoo with a closing
> price like this one:
>
> http://finance.yahoo.com/q;_ylt=AkL2UcAUoOAsKyhsfQfkucOiuYdG;_ylu=X3oDMTBxdGVyNzJxBHNlYwNVSCAzIERlc2t0b3AgU2VhcmNoIDEx;_ylg=X3oDMTBsdWsyY2FpBGxhbmcDZW4tVVMEcHQDMgR0ZXN0Aw--;_ylv=3?uhb=uhb2&fr=uh3_finance_vert_gs&type=2button&s=GB00B41QSW23.L
>
> and I'm therefore puzzled that there doesn't appear to be a defined
> smf_addin element that will access the closing price using
> "RCHGetElementNumber()".
>
> Am I missing something?
>

Wed Mar 26, 2014 1:30 pm (PDT) . Posted by:

jaja3279

Thank you Randy, I'll check it out. Problems with Yahoo historical quotes continue unresolved. I am having serious doubts about Yahoo as a reliable data provider. It would be nice to have a set of Google APIs that work the same way as smfPricesbyDates and RCHGetYahooHistory. Or a configuration option to send the requests to Google instead of Yahoo could also work. To be honest, I haven't yet looked into the existing Google APIs for quotes, so they may already be there. Just thinking aloud at this point; I'll check them out as soon as I get a chance.

Wed Mar 26, 2014 3:02 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

As I recently mentioned on another thread, because of issues with Google
historical quotes, I don't consider them an acceptable alternative to
Yahoo. See:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/20792

On Wed, Mar 26, 2014 at 1:30 PM, <karerat@yahoo.com> wrote:

>
> Thank you Randy, I'll check it out. Problems with Yahoo historical
> quotes continue unresolved. I am having serious doubts about Yahoo as a
> reliable data provider. It would be nice to have a set of Google APIs that
> work the same way as smfPricesbyDates and RCHGetYahooHistory. Or a
> configuration option to send the requests to Google instead of Yahoo could
> also work. To be honest, I haven't yet looked into the existing Google APIs
> for quotes, so they may already be there. Just thinking aloud at this
> point; I'll check them out as soon as I get a chance.
>

Wed Mar 26, 2014 1:55 pm (PDT) . Posted by:

"Nikola Ganev" ganevniko

Sorry to bother again Randy,
Both of the machines are in USA and when I open the link http://www.advfn.com/exchanges/NYSE/MMM/stock-pricehttp://www.advfn.com/exchanges/NYSE/MMM/stock-pricehttp://www.advfn.com/exchanges/NYSE/MMM/stock-pricehttp://www.advfn.com/exchanges/NYSE/MMM/stock-pricehttp://www.advfn.com/exchanges/NYSE/MMM/stock-pricehttp://www.advfn.com/exchanges/NYSE/MMM/stock-price
 It doesn't redirect me anywhere, the link opens with www prefix. Can it be something else?
Thank you

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Tuesday, March 25, 2014 5:05 PM
Subject: Re: [smf_addin] Issue with smfGetAdvFNElement formula

 
You would just create it with something like NotePad, and put your regional domain in it.

The add-in defaults to using a "www" domain for AdvFN. In some regions, people get redirected from "www" to something like "uk" or "au" or "in". Creating the smf-AdvFN-Prefix.txt file in your SMF add-in folder, and placing that regional domain in it, tells the add-in to use that domain for any AdvFN requests. If the file doesn't exist, the add-in just uses "www". That process was added so people didn't need to go in and edit the URL's in the VBA code. Otherwise, updates would overwrite the VBA changes, which would need to be changed again.

So, if one machine had an smf-AdvFN-Prefix.txt file with something like "uk" in it, I would expect the other machine to need it as well.

On Tue, Mar 25, 2014 at 1:34 PM, Nikola Ganev <ganevniko@yahoo.com> wrote:

>
>Sorry Randy, I don't understand, how do I create it?
>And just wondering why would it work on the other machine that does not have that smf-AdvFN-Prefix.txt file...
>

Wed Mar 26, 2014 4:31 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

On the machine that's not working, what do you get for:

=RCHGetElementNumber("MMM",5204)
=RCHGetElementNumber("Version")
=RCHGetWebData("
http://www.advfn.com/p.php?pid=financials&mode=quarterly_reports&symbol=MMM",
"/exchanges/", 50)
=RCHGetWebData("http://www.advfn.com/exchanges/NYSE/MMM/financials",
"/exchanges/", 50)
=RCHGetTableCell("
http://www.advfn.com/exchanges/NYSE/MMM/financials?btn=start_date&start_date=11&mode=annual_reports",1,">Year
End Date")
=smfGetAdvFNElement("MMM","A",999)
=smfGetAdvFNElement("MMM","A",1,">year end date<")

On Wed, Mar 26, 2014 at 1:55 PM, Nikola Ganev <ganevniko@yahoo.com> wrote:

>
> Sorry to bother again Randy,
> Both of the machines are in USA and when I open the link
> http://www.advfn.com/exchanges/NYSE/MMM/stock-price
> http://www.advfn.com/exchanges/NYSE/MMM/stock-price
> http://www.advfn.com/exchanges/NYSE/MMM/stock-price
> http://www.advfn.com/exchanges/NYSE/MMM/stock-price
> http://www.advfn.com/exchanges/NYSE/MMM/stock-price
> http://www.advfn.com/exchanges/NYSE/MMM/stock-price
> It doesn't redirect me anywhere, the link opens with www prefix. Can it
> be something else?
>

Wed Mar 26, 2014 4:52 pm (PDT) . Posted by:

gbusolini

Good evening
I am trying to download financial statement info from Google. However I would like to do this for many tickers at the same time, for example 500 at a time with around 20 data items for each.


For reference I am using a series of
=RCHGetElementNumber queries in separate excel cells.


I can successfully do this for a single ticker, or a small quantity of tickers. However the problem I encounter, is that after a few requests for small batches of tickers, I consistently get "Error" returned.


Does anyone know whether there is some kind of limit set by Google on how much information can be pulled by a single IP address over a defined timeframe?
Many thanks in advance for any help.

Wed Mar 26, 2014 5:10 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The add-in has a limit of 1000 web pages per "session". It is intended for
ad hoc downloading, not bulk downloading of data. I consider it an abuse of
the free data sources to download large amounts of data. They could easily
change the web pages so something like the add-in could not get to the data.

You should subscribe to some type of data service if you need that much
data. Even something like AAII's SI Pro database. For financial statement
screening, there's Portfolio123.com.

On Wed, Mar 26, 2014 at 3:32 PM, <gbusolini@yahoo.com> wrote:

>
> I am trying to download financial statement info from Google. However I
> would like to do this for many tickers at the same time, for example 500 at
> a time with around 20 data items for each.
>
> For reference I am using a series of
>
> =RCHGetElementNumber queries in separate excel cells.
>
> I can successfully do this for a single ticker, or a small quantity of
> tickers. However the problem I encounter, is that after a few requests
> for small batches of tickers, I consistently get "Error" returned.
>
> Does anyone know whether there is some kind of limit set by Google on how
> much information can be pulled by a single IP address over a defined
> timeframe?
>
>

Wed Mar 26, 2014 5:06 pm (PDT) . Posted by:

"Pete A" option2z

Randy,
I really like your design of the web repository. Just wondering if any of
the code you use to generate the html is sharable. I'd like to move my
stuff off of YAHOO G's too.

Any guidance on how best to approach? To do the downloads?

Thanks!

Pete A
<http://www.ttp-s.com/php/toolsmenu.php>



Wed Mar 26, 2014 5:44 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Ironically, one of the reasons I went to the website -- messed up
descriptions of files on Yahoo -- has now been fixed on the Yahoo groups. :)

However, now I can use the website access logs to see which files are being
used and/or downloaded.

I generate all the code from EXCEL worksheets. It's something I do quite a
bit of. I have quite a number of workbooks that generate HTML code. I just
worked on one today that creates an HTML web page of NetFlix new streaming
releases.

For example, the main portion of the Templates page is a worksheet with
these columns:

FileID
HTML
Filename
Filetype
Filesize
Last Modified
Description

The "FileID" is used to look up the "FileName" thru "Description" fields
from the master database (that is the CSV file that can be downloaded,
minus the FileIDs). Then, the HTML is just generated with a formula like:

="<tr><td valign=top nowrap><img src=""/icons/"&F28&".ico""> <a
href="""&E28&""">"&E28&"</a></td><td valign=top align=right
nowrap>"&G28&"</td><td valign=top nowrap><div"&IF(NOW()-H28<15,"
style=""background-color:yellow;""","")&">"&TEXT(H28,"yyyy-mm-dd hh:mm
AM/PM")&"</div></td><td valign=top>"&I28&"</td></tr>"

There's also the navigational and contents section at the top of the page,
and a header for each topical section I created. So, when I upload a new
file to the website, I:

-- Add it to the CSV worksheet, giving it a new FileID
-- Update the CSV file on the web site

Then, for the appropriate worksheet (Documentation, Template, etc):

-- Add new topics to the page's contents section, if and as necessary
-- Create new topical sections, if and as necessary
-- Add the file to each appropriate topical section
-- Copy and paste the HTML column into the INDEX.HTML file on the web site.

I can give you a copy of the workbook if you like, although it's totally
undocumented. :)

Most of the work was doing trial and error to come up with how I wanted the
pages to look. After that, it was just a matter of how to generate that
page using the detailed data from the files database. So, now I don't need
to do any more web page coding, just adding lines to the worksheet -- which
is mostly copying.

On Wed, Mar 26, 2014 at 5:05 PM, Pete A <optionzz@gmail.com> wrote:

>
> I really like your design of the web repository. Just wondering if any
> of the code you use to generate the html is sharable. I̢۪d like to move
> my stuff off of YAHOO G̢۪s too.
>
>
>
> Any guidance on how best to approach? To do the downloads?
>

Wed Mar 26, 2014 5:35 pm (PDT) . Posted by:

navelhunt

Hi Randy, I use your formula to successfully get some stock option data from businessweek:
=rchgettablecell("http://investing.businessweek.com/research/stocks/financials/pensions.asp?ticker=AMAG",1,"W/avg. Exercise price of options outstanding",,,,0,0,0,0)

will result in the number 21.31
However, replacing the ticker with another one will result in an error, or 0 in this case.
=rchgettablecell("http://investing.businessweek.com/research/stocks/financials/pensions.asp?ticker=GILD",1,"W/avg. Exercise price of options outstanding",,,,0,0,0,0)

I dont quite know what is wrong here since the actual website shows that the number 19.34 should come up with this formula.
Is there some other way to retrieve this number from that page? Any thoughts why one works and the other one doesnt?
Thanks so much


Diposting oleh EXCEL FEED LEARNING di 17.44

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)