15 New Messages
Digest #2938
Messages
Wed Jan 22, 2014 7:05 am (PST) . Posted by:
weekeewawa
hi randy,
for both the code
=smfGetTagContent("http://investing.businessweek.com/research/stocks/financials/financials.asp?ticker=IBM&dataset=incomeStatement&period=A¤cy=native http://investing.businessweek.com/research/stocks/financials/financials.asp?ticker=IBM&dataset=incomeStatement&period=A¤cy=native","div",-1,"financialsSmartText")
and
=RCHGetHTMLTable("http://investing.businessweek.com/research/stocks/financials/financials.asp?ticker=IBM&dataset=incomeStatement&period=A¤cy=native http://investing.businessweek.com/research/stocks/financials/financials.asp?ticker=IBM&dataset=incomeStatement&period=A¤cy=native","As of:",-1,"",1)
how do i point it to cell A1 instead?
for both the code
=smfGetTagContent(
and
=RCHGetHTMLTable(
how do i point it to cell A1 instead?
Wed Jan 22, 2014 7:29 am (PST) . Posted by:
"Randy Harmelink" rharmelink
See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/21995
On Wed, Jan 22, 2014 at 8:05 AM, <weekeewawa@yahoo.com> wrote:
>
> for both the code
>
> =smfGetTagContent("
> http://investing.businessweek.com/research/stocks/financials/financials.asp?ticker=IBM&dataset=incomeStatement&period=A¤cy=native
> ","div",-1,"financialsSmartText")
>
> and
>
> =RCHGetHTMLTable("
> http://investing.businessweek.com/research/stocks/financials/financials.asp?ticker=IBM&dataset=incomeStatement&period=A¤cy=native","As
> of:",-1,"",1)
>
> how do i point it to cell A1 instead?
>
>
https://groups.
On Wed, Jan 22, 2014 at 8:05 AM, <weekeewawa@yahoo.
>
> for both the code
>
> =smfGetTagContent(
> http://investing.
> ","
>
> and
>
> =RCHGetHTMLTable(
> http://investing.
> of:",-1,
>
> how do i point it to cell A1 instead?
>
>
Wed Jan 22, 2014 10:35 am (PST) . Posted by:
"Donald McKinnon" dgmckin
I'd like to try Google stock quotes to replace RCHGetYahooQuotes in my
Excel 2010 workbooks, but I couldn't find documentation for this in
the Files section. I found documentation forGoogle option quotes, but
not stock quotes. Am I looking in the right place?
Don
Excel 2010 workbooks, but I couldn't find documentation for this in
the Files section. I found documentation forGoogle option quotes, but
not stock quotes. Am I looking in the right place?
Don
Wed Jan 22, 2014 11:06 am (PST) . Posted by:
mikemcq802
Donald, in the future you should create a new thread when starting a new line of questions.
But, to answer your question, there is not any specific built-in function to get Google quotes. You can use one of Randy's "raw" lookups such as described in this thread:
http://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/25045
Here is what that person suggested (where A1 contains the ticker):
=smfConvertData(smfGetTagContent("https://www.google.com/finance?q="&A1,"span",-1,"_l"">"))
Make note that there have already been comments about Google needing some tickers needing the exchange as a prefix, like NYSE:DOW. The smfGetTagContent will require one internet access per request - unlike the GetYahooQuotes which can return many quotes in one request when entered as an array formula.
But, to answer your question, there is not any specific built-in function to get Google quotes. You can use one of Randy's "raw" lookups such as described in this thread:
http://groups.
Here is what that person suggested (where A1 contains the ticker):
=smfConvertData(
Make note that there have already been comments about Google needing some tickers needing the exchange as a prefix, like NYSE:DOW. The smfGetTagContent will require one internet access per request - unlike the GetYahooQuotes which can return many quotes in one request when entered as an array formula.
Wed Jan 22, 2014 11:10 am (PST) . Posted by:
"Randy Harmelink" rharmelink
There is no documentation specific to Google, since there is no function
specific to Google. You would need to use the generic SMF functions to pull
the data from the Google web pages. For examples, see:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/25047
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/25094
Note that it will be very slow for a large number of tickers, since you'll
be doing an Internet access for each individual quote.
However, there is a template that uses a Google API to grab data for stocks
and ETFs:
RCHGetWebData-Google-API-Quotes.xls
I'm told it works for up to 100 ticker symbols? I don't use it myself, so I
have no idea on its reliability. I just threw together the template out of
curiosity when I learned of the Google API. Some related information can be
found here (and on that message thread):
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/20131
On Wed, Jan 22, 2014 at 11:16 AM, Donald McKinnon <dgmckin@gmail.com> wrote:
> I'd like to try Google stock quotes to replace RCHGetYahooQuotes in my
> Excel 2010 workbooks, but I couldn't find documentation for this in
> the Files section. I found documentation forGoogle option quotes, but
> not stock quotes. Am I looking in the right place?
>
specific to Google. You would need to use the generic SMF functions to pull
the data from the Google web pages. For examples, see:
https://groups.
https://groups.
Note that it will be very slow for a large number of tickers, since you'll
be doing an Internet access for each individual quote.
However, there is a template that uses a Google API to grab data for stocks
and ETFs:
RCHGetWebData-
I'm told it works for up to 100 ticker symbols? I don't use it myself, so I
have no idea on its reliability. I just threw together the template out of
curiosity when I learned of the Google API. Some related information can be
found here (and on that message thread):
https://groups.
On Wed, Jan 22, 2014 at 11:16 AM, Donald McKinnon <dgmckin@gmail.
> I'd like to try Google stock quotes to replace RCHGetYahooQuotes in my
> Excel 2010 workbooks, but I couldn't find documentation for this in
> the Files section. I found documentation forGoogle option quotes, but
> not stock quotes. Am I looking in the right place?
>
Wed Jan 22, 2014 1:05 pm (PST) . Posted by:
"Marco Deen" marco.deen
Thanks, this definitely is an improvement. As I usually sell options 2
months away I always had problems with Yahoo not returning those strikes
yet. OX handles that a lot better.
On Tue, Jan 21, 2014 at 7:25 PM, Randy Harmelink <rharmelink@gmail.com>wrote:
>
>
> I would get the first ITM strike price with something like (not
> array-entered):
>
> =smfGetOptionStrikes("IWM";,"2/22/2014",,"Y",,2,1)
>
> ...and the first OTM strike price with something like:
>
> =INDEX(smfGetOptionStrikes("IWM","2/22/2014",,"Y",,2,1),2)
>
> The "2,1" parameters tell the function to return a 2-row by 1-column
> array. That means the first row will be the first ITM price and the 2nd row
> with be the first OTM price (hence the need for the INDEX function).
>
> Or, you could array-enter this over a 2-row by 1-column range and get both
> of them at the same time:
>
> =smfGetOptionStrikes("IWM";,"2/22/2014",,"Y")
>
> I use OptionsXPress instead of Yahoo. They are a bit slower, but the
> results are more reliable (because people are actually using their data for
> trading options). Just change the "Y" parameters above to "OX" to get
> OptionsXPress data.
>
>
> On Tue, Jan 21, 2014 at 2:42 PM, Marco Deen <marco.deen@gmail.com> wrote:
>
>>
>> Randy, what I'm trying to do is get the first ITM strike prices for some
>> 10 to 80 securities. Once I have that I copy it into my broker's API
>> and request bid, ask and greeks there.
>>
>> Up until last week =smfGETYAHOOOPTIONQUOTE(A2,"P",$A$1,"ITM1","s") worked
>> but now most of my cells return empty. ($A$1 is the cell where I specify my
>> expiration date)
>> Do you have suggestions for another source I could use?
>>
>>
>
>
months away I always had problems with Yahoo not returning those strikes
yet. OX handles that a lot better.
On Tue, Jan 21, 2014 at 7:25 PM, Randy Harmelink <rharmelink@gmail.
>
>
> I would get the first ITM strike price with something like (not
> array-entered)
>
> =smfGetOptionStrike
>
> ...and the first OTM strike price with something like:
>
> =INDEX(smfGetOption
>
> The "2,1" parameters tell the function to return a 2-row by 1-column
> array. That means the first row will be the first ITM price and the 2nd row
> with be the first OTM price (hence the need for the INDEX function).
>
> Or, you could array-enter this over a 2-row by 1-column range and get both
> of them at the same time:
>
> =smfGetOptionStrike
>
> I use OptionsXPress instead of Yahoo. They are a bit slower, but the
> results are more reliable (because people are actually using their data for
> trading options). Just change the "Y" parameters above to "OX" to get
> OptionsXPress data.
>
>
> On Tue, Jan 21, 2014 at 2:42 PM, Marco Deen <marco.deen@gmail.
>
>>
>> Randy, what I'm trying to do is get the first ITM strike prices for some
>> 10 to 80 securities. Once I have that I copy it into my broker's API
>> and request bid, ask and greeks there.
>>
>> Up until last week =smfGETYAHOOOPTIONQ
>> but now most of my cells return empty. ($A$1 is the cell where I specify my
>> expiration date)
>> Do you have suggestions for another source I could use?
>>
>>
>
>
Wed Jan 22, 2014 4:35 pm (PST) . Posted by:
sdavis81
Randy,
Thanks again!
My sheeet is working like a charm. I learned all sorts of new tricks with your nudge. Arrays, Macros, etc.
excited to use it.
Thanks again for all you do to help people.
Scott
Thanks again!
My sheeet is working like a charm. I learned all sorts of new tricks with your nudge. Arrays, Macros, etc.
excited to use it.
Thanks again for all you do to help people.
Scott
Wed Jan 22, 2014 10:54 pm (PST) . Posted by:
holmberg_tommy
Hi Randy,
I would like add a row in the table that calculates the book value increase in percentages for last five years. How it can be carried out. At the moment I bring all the necessary data from Reuters and Financial times. Could this be added on "RCHGetElementNumber-Element-Definitions"; or how do I carry it out on the table? I prefer the option with Reuters but if you have a good solution for Financial times, I would be very interested to hear your proposal.
Thanks in advance,
Tommy Holmberg
I would like add a row in the table that calculates the book value increase in percentages for last five years. How it can be carried out. At the moment I bring all the necessary data from Reuters and Financial times. Could this be added on "RCHGetElement
Thanks in advance,
Tommy Holmberg
Wed Jan 22, 2014 11:45 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Sample URLs please...
On Wed, Jan 22, 2014 at 11:54 PM, <holmberg_tommy@yahoo.com> wrote:
>
> I would like add a row in the table that calculates the book value
> increase in percentages for last five years. How it can be carried out. At
> the moment I bring all the necessary data from Reuters and Financial times.
> Could this be added on "RCHGetElementNumber-Element-Definitions"; or how do
> I carry it out on the table? I prefer the option with Reuters but if you
> have a good solution for Financial times, I would be very interested to
> hear your proposal.
>
> Thanks in advance,
>
>
On Wed, Jan 22, 2014 at 11:54 PM, <holmberg_tommy@
>
> I would like add a row in the table that calculates the book value
> increase in percentages for last five years. How it can be carried out. At
> the moment I bring all the necessary data from Reuters and Financial times.
> Could this be added on "RCHGetElement
> I carry it out on the table? I prefer the option with Reuters but if you
> have a good solution for Financial times, I would be very interested to
> hear your proposal.
>
> Thanks in advance,
>
>
Wed Jan 22, 2014 11:47 pm (PST) . Posted by:
herkshirebathaway
Hi Guys,
Just wondering whether anyone has had any success pulling in the Gurufocus CSV file using the SMF GetCSVFile function?
Here is a link to the APPL gurufocus csv file:
http://www.gurufocus.com/download_financials_in_CSV.php?symbol=APPL
This is what I have entered in Excel:
=smfGetCSVFile("http://www.gurufocus.com/download_financials_in_CSV.php?symbol=APPL")
Any thoughts on where I am going wrong?
Just wondering whether anyone has had any success pulling in the Gurufocus CSV file using the SMF GetCSVFile function?
Here is a link to the APPL gurufocus csv file:
http://www.gurufocu
This is what I have entered in Excel:
=smfGetCSVFile(
Any thoughts on where I am going wrong?
Wed Jan 22, 2014 11:53 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I don't have a premium membership, so I can't try it. However, two things
come to mind off-hand:
1. It's a PHP web page, so there may not actually be a file sitting out
there to download. It could be the PHP web page creates the CSV file, which
it then delivers to you for download. Or, it could be that the URL is just
the link to get to the actual download file, so you need a different URL
for the CSV file itself.
2. You haven't created the IE security cookie so that EXCEL and the add-in
can get to the data.
On Thu, Jan 23, 2014 at 12:36 AM, <j.zhen.yap@gmail.com> wrote:
>
> Just wondering whether anyone has had any success pulling in the Gurufocus
> CSV file using the SMF GetCSVFile function?
>
> Here is a link to the APPL gurufocus csv file:
> http://www.gurufocus.com/download_financials_in_CSV.php?symbol=APPL
>
> This is what I have entered in Excel:
> =smfGetCSVFile("
> http://www.gurufocus.com/download_financials_in_CSV.php?symbol=APPL")
>
> Any thoughts on where I am going wrong?
>
come to mind off-hand:
1. It's a PHP web page, so there may not actually be a file sitting out
there to download. It could be the PHP web page creates the CSV file, which
it then delivers to you for download. Or, it could be that the URL is just
the link to get to the actual download file, so you need a different URL
for the CSV file itself.
2. You haven't created the IE security cookie so that EXCEL and the add-in
can get to the data.
On Thu, Jan 23, 2014 at 12:36 AM, <j.zhen.yap@gmail.
>
> Just wondering whether anyone has had any success pulling in the Gurufocus
> CSV file using the SMF GetCSVFile function?
>
> Here is a link to the APPL gurufocus csv file:
> http://www.gurufocu
>
> This is what I have entered in Excel:
> =smfGetCSVFile(
> http://www.gurufocu
>
> Any thoughts on where I am going wrong?
>
Thu Jan 23, 2014 12:22 am (PST) . Posted by:
herkshirebathaway
Hi Randy,
Thanks for responding so swiftly.
I think it may be a case of number 2, ala no internet security cookie...
I assume this needs to be done through VBA which I have some experience in, but by no means professional.
Probably best to seek advice at VBA oriented forums if this is the case.
However, if in fact there is another hidden URL which I need for the CSV file itself, how would you advise me to go about finding it?
Thanks again Randy, I will post anything I find back here.
Thanks for responding so swiftly.
I think it may be a case of number 2, ala no internet security cookie...
I assume this needs to be done through VBA which I have some experience in, but by no means professional.
Probably best to seek advice at VBA oriented forums if this is the case.
However, if in fact there is another hidden URL which I need for the CSV file itself, how would you advise me to go about finding it?
Thanks again Randy, I will post anything I find back here.
Thu Jan 23, 2014 12:31 am (PST) . Posted by:
"Randy Harmelink" rharmelink
To create the IE security cookie, you just need to log on to the site using
either the EXCEL Web Query dialog (keyboard shortcut alt+d+d+w) or using IE
as a browser.
As to finding out the actual URL, if it is something different, you could
use a URL sniffer or look at the browser's download log. I use the HTTPFOX
add-in with FireFox, or look at FIreFox's download log and copy the
download link.
On Thu, Jan 23, 2014 at 1:22 AM, <j.zhen.yap@gmail.com> wrote:
> I think it may be a case of number 2, ala no internet security cookie...
>
> I assume this needs to be done through VBA which I have some experience
> in, but by no means professional.
>
> Probably best to seek advice at VBA oriented forums if this is the case.
>
> However, if in fact there is another hidden URL which I need for the CSV
> file itself, how would you advise me to go about finding it?
>
> Thanks again Randy, I will post anything I find back here.
>
either the EXCEL Web Query dialog (keyboard shortcut alt+d+d+w) or using IE
as a browser.
As to finding out the actual URL, if it is something different, you could
use a URL sniffer or look at the browser'
add-in with FireFox, or look at FIreFox'
download link.
On Thu, Jan 23, 2014 at 1:22 AM, <j.zhen.yap@gmail.
> I think it may be a case of number 2, ala no internet security cookie...
>
> I assume this needs to be done through VBA which I have some experience
> in, but by no means professional.
>
> Probably best to seek advice at VBA oriented forums if this is the case.
>
> However, if in fact there is another hidden URL which I need for the CSV
> file itself, how would you advise me to go about finding it?
>
> Thanks again Randy, I will post anything I find back here.
>
Wed Jan 22, 2014 11:53 pm (PST) . Posted by:
herkshirebathaway
Hi Guys,
I am trying to pull the Gurufocus CSV files into excel using the =smfGetCSVFile function.
The URL for the CSV file is http://www.gurufocus.com/download_financials_in_CSV.php?symbol=APPL
so I have array entered...
=smfGetCSVFile("http://www.gurufocus.com/download_financials_in_CSV.php?symbol=ASX:SHV")
But it is returning errors...
Has anyone had any success pulling CSV files from GuruFocus?
I know that is definitely the right link as when I paste it directly into my browser it download the file directly....
Would appreciate any thoughts/ideas/suggestions!
Thank you for your time.
Justin
I am trying to pull the Gurufocus CSV files into excel using the =smfGetCSVFile function.
The URL for the CSV file is http://www.gurufocu
so I have array entered...
=smfGetCSVFile(
But it is returning errors...
Has anyone had any success pulling CSV files from GuruFocus?
I know that is definitely the right link as when I paste it directly into my browser it download the file directly....
Would appreciate any thoughts/ideas/
Thank you for your time.
Justin
Thu Jan 23, 2014 12:27 am (PST) . Posted by:
herkshirebathaway
Randy,
I can confirm that I am able to import the CSV file only after logging in through the web query dialogue box.
I will attempt to write some code that will automate this process.
Any past threads on this or other forums you could point me towards for a head start would be greatly appreciated.
Cheers,
Justin
I can confirm that I am able to import the CSV file only after logging in through the web query dialogue box.
I will attempt to write some code that will automate this process.
Any past threads on this or other forums you could point me towards for a head start would be greatly appreciated.
Cheers,
Justin
Tidak ada komentar:
Posting Komentar