12 New Messages
Digest #2383
Messages
Wed Oct 24, 2012 3:57 am (PDT) . Posted by:
"boo1712" boo1712
Dear Randy,
This is what I need.
Thanks a lot for your always prompt and accurate answers.
Terry
--- In smf_addin@yahoogrou
>
> General extraction formula would be something like:
>
> =smfStrExtr(
>
> ....where:
>
> $D$2 = the URL to retrieve the data
> $C4 = which row to extract (e.g. 1, 2, 3, ...)
> G$3 = which data item to extract (e.g. e, t, l_cur, ...)
>
> Or, to retrieve by ticker symbol:
>
> =smfStrExtr(
> """&$C4&""""
>
> ...where:
>
> $D$2 = the URL to retrieve the data
> $C4 = which ticker symbol to extract
> G$3 = which data item to extract (e.g. e, t, l_cur, ...)
>
> I found this list of data items:
>
> avvo = Average volume (float with multiplier, like '3.54M')
> beta = Beta (float)
> c = Amount of change while open (float)
> ccol = (unknown) (chars)
> cl = Last perc. change
> cp = Change perc. while open (float)
> e = Exchange (text, like 'NASDAQ')
> ec = After hours last change from close (float)
> eccol = (unknown) (chars)
> ecp = After hours last chage perc. from close (float)
> el = After. hours last quote (float)
> el_cur = (unknown) (float)
> elt = After hours last quote time (unknown)
> eo = Exchange Open (0 or 1)
> eps = Earnings per share (float)
> fwpe = Forward PE ratio (float)
> hi = Price high (float)
> hi52 = 52 weeks high (float)
> id = Company id (identifying number)
> l = Last value while open (float)
> l_cur = Last value at close (like 'l')
> lo = Price low (float)
> lo52 = 52 weeks low (float)
> lt = Last value date/time
> ltt = Last trade time (Same as "lt" without the data)
> mc = Market cap. (float with multiplier, like '123.45B')
> name = Company name (text)
> op = Open price (float)
> pe = PE ratio (float)
> t = Ticker (text)
> type = Type (i.e. 'Company')
> vo = Volume (float with multiplier, like '3.54M')
>
> On Tue, Oct 23, 2012 at 1:18 AM, boo1712 <boo1712@...
>
> > Hi guys,
> >
> > I discovered a new google API:
> > http://www.google.
> >
> > It has more variables such as beta, 52wk high, premarket quotes etc.
> >
> > However I do not know how to handle the numerous """.
> >
> > Previously we had a spreadsheet using another google API
> > smfStrExtr(RCHGetWe
> > data="""&$C4&
> >
> > It is useful and fast for getting multiple quotes.
> >
> > Can anyone help to provide the formula to the new API with more variables?
> >
>
Wed Oct 24, 2012 4:35 am (PDT) . Posted by:
"boo1712" boo1712
One issue, some data to be retrieved like name and time has ",".
We need to change the the searched key from "," to ",""" to solve it
Hence
=smfStrExtr(smfStrExtr(smfStrExtr(RCHGetWebData($D$2,1),"""t"" : """&$C4&"""","}"),""""&G$3&"""",","""),"""","""")
where:
$D$2 = the URL to retrieve the data
$C4 = which ticker symbol to extract
G$3 = which data item to extract (e.g. e, t, l_cur, ...)
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> General extraction formula would be something like:
>
> =smfStrExtr(smfStrExtr(smfWord(RCHGetWebData($D$2,1),$C4,"}"),""""&G$3&"""",","),"""","""")
>
> ....where:
>
> $D$2 = the URL to retrieve the data
> $C4 = which row to extract (e.g. 1, 2, 3, ...)
> G$3 = which data item to extract (e.g. e, t, l_cur, ...)
>
> Or, to retrieve by ticker symbol:
>
> =smfStrExtr(smfStrExtr(smfStrExtr(RCHGetWebData($D$2,1),"""t"" :
> """&$C4&"""","}"),""""&G$3&"""",","),"""","""")
>
> ...where:
>
> $D$2 = the URL to retrieve the data
> $C4 = which ticker symbol to extract
> G$3 = which data item to extract (e.g. e, t, l_cur, ...)
>
> I found this list of data items:
>
> avvo = Average volume (float with multiplier, like '3.54M')
> beta = Beta (float)
> c = Amount of change while open (float)
> ccol = (unknown) (chars)
> cl = Last perc. change
> cp = Change perc. while open (float)
> e = Exchange (text, like 'NASDAQ')
> ec = After hours last change from close (float)
> eccol = (unknown) (chars)
> ecp = After hours last chage perc. from close (float)
> el = After. hours last quote (float)
> el_cur = (unknown) (float)
> elt = After hours last quote time (unknown)
> eo = Exchange Open (0 or 1)
> eps = Earnings per share (float)
> fwpe = Forward PE ratio (float)
> hi = Price high (float)
> hi52 = 52 weeks high (float)
> id = Company id (identifying number)
> l = Last value while open (float)
> l_cur = Last value at close (like 'l')
> lo = Price low (float)
> lo52 = 52 weeks low (float)
> lt = Last value date/time
> ltt = Last trade time (Same as "lt" without the data)
> mc = Market cap. (float with multiplier, like '123.45B')
> name = Company name (text)
> op = Open price (float)
> pe = PE ratio (float)
> t = Ticker (text)
> type = Type (i.e. 'Company')
> vo = Volume (float with multiplier, like '3.54M')
>
> On Tue, Oct 23, 2012 at 1:18 AM, boo1712 <boo1712@...> wrote:
>
> > Hi guys,
> >
> > I discovered a new google API:
> > http://www.google.com/finance/info?infotype=infoquoteall&q=BTU,JPM,AIG
> >
> > It has more variables such as beta, 52wk high, premarket quotes etc.
> >
> > However I do not know how to handle the numerous """.
> >
> > Previously we had a spreadsheet using another google API
> > smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock= "&smfJoin($C$4:$C$33,"&stock="),"<symbol
> > data="""&$C4&"""",5000),"<"&E$3&" data=""","""")
> >
> > It is useful and fast for getting multiple quotes.
> >
> > Can anyone help to provide the formula to the new API with more variables?
> >
>
We need to change the the searched key from "," to ",""" to solve it
Hence
=smfStrExtr(
where:
$D$2 = the URL to retrieve the data
$C4 = which ticker symbol to extract
G$3 = which data item to extract (e.g. e, t, l_cur, ...)
--- In smf_addin@yahoogrou
>
> General extraction formula would be something like:
>
> =smfStrExtr(
>
> ....where:
>
> $D$2 = the URL to retrieve the data
> $C4 = which row to extract (e.g. 1, 2, 3, ...)
> G$3 = which data item to extract (e.g. e, t, l_cur, ...)
>
> Or, to retrieve by ticker symbol:
>
> =smfStrExtr(
> """&$C4&""""
>
> ...where:
>
> $D$2 = the URL to retrieve the data
> $C4 = which ticker symbol to extract
> G$3 = which data item to extract (e.g. e, t, l_cur, ...)
>
> I found this list of data items:
>
> avvo = Average volume (float with multiplier, like '3.54M')
> beta = Beta (float)
> c = Amount of change while open (float)
> ccol = (unknown) (chars)
> cl = Last perc. change
> cp = Change perc. while open (float)
> e = Exchange (text, like 'NASDAQ')
> ec = After hours last change from close (float)
> eccol = (unknown) (chars)
> ecp = After hours last chage perc. from close (float)
> el = After. hours last quote (float)
> el_cur = (unknown) (float)
> elt = After hours last quote time (unknown)
> eo = Exchange Open (0 or 1)
> eps = Earnings per share (float)
> fwpe = Forward PE ratio (float)
> hi = Price high (float)
> hi52 = 52 weeks high (float)
> id = Company id (identifying number)
> l = Last value while open (float)
> l_cur = Last value at close (like 'l')
> lo = Price low (float)
> lo52 = 52 weeks low (float)
> lt = Last value date/time
> ltt = Last trade time (Same as "lt" without the data)
> mc = Market cap. (float with multiplier, like '123.45B')
> name = Company name (text)
> op = Open price (float)
> pe = PE ratio (float)
> t = Ticker (text)
> type = Type (i.e. 'Company')
> vo = Volume (float with multiplier, like '3.54M')
>
> On Tue, Oct 23, 2012 at 1:18 AM, boo1712 <boo1712@...
>
> > Hi guys,
> >
> > I discovered a new google API:
> > http://www.google.
> >
> > It has more variables such as beta, 52wk high, premarket quotes etc.
> >
> > However I do not know how to handle the numerous """.
> >
> > Previously we had a spreadsheet using another google API
> > smfStrExtr(RCHGetWe
> > data="""&$C4&
> >
> > It is useful and fast for getting multiple quotes.
> >
> > Can anyone help to provide the formula to the new API with more variables?
> >
>
Wed Oct 24, 2012 10:05 am (PDT) . Posted by:
"heink" heink
Just a heads-up, but according to a post from April 24th in the "Google Finance APIs and Tools" group on "Google Groups", this API is supposed to go away soon. On the other hand, it seems to work just fine right now and the shutdown was stated as October 20th.
heink
---------- announcement ----------
Dear Finance API users,
As you may have heard from the Google Developers Blog, Google is doing an API spring cleaning. One of the APIs affected is the Google Finance API (both the Portfolio API and the Finance Gadgets and Tools API), which will be shut down on October 20, 2012.
This was not an easy decision to take. We work with a large number of data providers and need to respect our relationships with them. As a result, we had to keep the API restricted to end users, which prevented a meaningful ecosystem from growing around the API. We also realized that we could serve more people better by integrating the data into other Google products rather than requiring them to write code to access the data. For example, check out the GoogleFinance(
Thank you for being loyal users of the Google Finance API over the last few years.
- Karolina Netolicka, Google Finance Product Manager
---------- end announcement ----------
--- In smf_addin@yahoogrou
>
> Hi guys,
>
> I discovered a new google API:
> http://www.google.
>
> It has more variables such as beta, 52wk high, premarket quotes etc.
>
> However I do not know how to handle the numerous """.
>
> Previously we had a spreadsheet using another google API
> smfStrExtr(RCHGetWe
>
> It is useful and fast for getting multiple quotes.
>
> Can anyone help to provide the formula to the new API with more variables?
>
> Thanks alot
>
Wed Oct 24, 2012 2:29 pm (PDT) . Posted by:
"José Azevedo" jma_resende
HI!
Here "$D$2 = the URL to retrieve the data"
I have some doubts what to put here.
This:
http://www.google.**com/ig/api?**stock= <http://www.google.com/ig/api?stock= >
Or how I can write?
Thanks
Jose
Here "$D$2 = the URL to retrieve the data"
I have some doubts what to put here.
This:
http://www.google.
Or how I can write?
Thanks
Jose
Wed Oct 24, 2012 3:48 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
See the first message in the thread...
On Wed, Oct 24, 2012 at 2:29 PM, José Azevedo <jmazevedo@gmail.com > wrote:
>
> Here "$D$2 = the URL to retrieve the data"
> I have some doubts what to put here.
>
> This:
> http://www.google.**com/ig/api?**stock= <http://www.google.com/ig/api?stock= >
>
> Or how I can write?
>
>
On Wed, Oct 24, 2012 at 2:29 PM, José Azevedo <jmazevedo@gmail.
>
> Here "$D$2 = the URL to retrieve the data"
> I have some doubts what to put here.
>
> This:
> http://www.google.
>
> Or how I can write?
>
>
Wed Oct 24, 2012 8:17 pm (PDT) . Posted by:
"boo1712" boo1712
There seems to be 2 google API, at least as far a I know
1. http://www.google.com/ig/api?stock=
2. http://www.google.com/finance/info?infotype=infoquoteall&q=
I think the go away soon API refers to "1." or at least the news refer to this API.
I think API "2." is relatively more realtime and has more variables and can download more quotes at one go. Hopefully this API stays:)
--- In smf_addin@yahoogroups.com , "heink" <phil.heink@...> wrote:
>
>
>
>
>
>
>
>
>
> Just a heads-up, but according to a post from April 24th in the "Google Finance APIs and Tools" group on "Google Groups", this API is supposed to go away soon. On the other hand, it seems to work just fine right now and the shutdown was stated as October 20th.
>
> heink
>
> ---------- announcement ----------
>
> Dear Finance API users,
> As you may have heard from the Google Developers Blog, Google is doing an API spring cleaning. One of the APIs affected is the Google Finance API (both the Portfolio API and the Finance Gadgets and Tools API), which will be shut down on October 20, 2012.
> This was not an easy decision to take. We work with a large number of data providers and need to respect our relationships with them. As a result, we had to keep the API restricted to end users, which prevented a meaningful ecosystem from growing around the API. We also realized that we could serve more people better by integrating the data into other Google products rather than requiring them to write code to access the data. For example, check out the GoogleFinance() function in Google Spreadsheets which replicates some of the API's functionality without requiring you to write code.
> Thank you for being loyal users of the Google Finance API over the last few years.
>
> - Karolina Netolicka, Google Finance Product Manager
>
> ---------- end announcement ----------
>
>
> --- In smf_addin@yahoogroups.com , "boo1712" <boo1712@> wrote:
> >
> > Hi guys,
> >
> > I discovered a new google API:
> > http://www.google.com/finance/info?infotype=infoquoteall&q=BTU,JPM,AIG
> >
> > It has more variables such as beta, 52wk high, premarket quotes etc.
> >
> > However I do not know how to handle the numerous """.
> >
> > Previously we had a spreadsheet using another google API
> > smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock= "&smfJoin($C$4:$C$33,"&stock="),"<symbol data="""&$C4&"""",5000),"<"&E$3&" data=""","""")
> >
> > It is useful and fast for getting multiple quotes.
> >
> > Can anyone help to provide the formula to the new API with more variables?
> >
> > Thanks alot
> >
>
1. http://www.google.
2. http://www.google.
I think the go away soon API refers to "1." or at least the news refer to this API.
I think API "2." is relatively more realtime and has more variables and can download more quotes at one go. Hopefully this API stays:)
--- In smf_addin@yahoogrou
>
>
>
>
>
>
>
>
>
> Just a heads-up, but according to a post from April 24th in the "Google Finance APIs and Tools" group on "Google Groups", this API is supposed to go away soon. On the other hand, it seems to work just fine right now and the shutdown was stated as October 20th.
>
> heink
>
> ---------- announcement ----------
>
> Dear Finance API users,
> As you may have heard from the Google Developers Blog, Google is doing an API spring cleaning. One of the APIs affected is the Google Finance API (both the Portfolio API and the Finance Gadgets and Tools API), which will be shut down on October 20, 2012.
> This was not an easy decision to take. We work with a large number of data providers and need to respect our relationships with them. As a result, we had to keep the API restricted to end users, which prevented a meaningful ecosystem from growing around the API. We also realized that we could serve more people better by integrating the data into other Google products rather than requiring them to write code to access the data. For example, check out the GoogleFinance(
> Thank you for being loyal users of the Google Finance API over the last few years.
>
> - Karolina Netolicka, Google Finance Product Manager
>
> ---------- end announcement ----------
>
>
> --- In smf_addin@yahoogrou
> >
> > Hi guys,
> >
> > I discovered a new google API:
> > http://www.google.
> >
> > It has more variables such as beta, 52wk high, premarket quotes etc.
> >
> > However I do not know how to handle the numerous """.
> >
> > Previously we had a spreadsheet using another google API
> > smfStrExtr(RCHGetWe
> >
> > It is useful and fast for getting multiple quotes.
> >
> > Can anyone help to provide the formula to the new API with more variables?
> >
> > Thanks alot
> >
>
Wed Oct 24, 2012 6:55 am (PDT) . Posted by:
"twopourdrummer" twopourdrummer
Sure
RCHGetTableCell("http://www.smartmoney.com/eqsnaps/index.cfm?story=competition&symbol=msft, 3,">Competitive Analysis")
RCHGetTableCell("http://www.smartmoney.com/quote/msft/?story=profile ",1,"Address")
RCHGetTableCell("http://www.zacks.com/research/report.php?type=report&t=msft ",1,"Sector:")
Note, I replaced my stock variable with msft. It would normally be something like "&B1&". Also note, that the references do work on my home computer and use to work on my work computer, so I know I'm not passing an improper parameter.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> For a stand-alone function, a #VALUE! error typically is the cause of
> passing an improper parameter to the function. Can you give me an example
> of a function invocation that gives you that error?
>
> On Mon, Oct 22, 2012 at 6:17 AM, twopourdrummer <twopourdrummer@...>wrote:
>
> > Got an interesting issue. On my work computer, any text field that I'm
> > trying to pull off a website creates a #value error. It use to work fine,
> > then one day, it didn't. It works fine at home so I know they still work
> > correctly. My guess is there is some type of setting on my Excel at work
> > that is causing this, but I can't find it. I saw one post that talked about
> > changing the transition formula entry option but that didn't work. Any
> > ideas would be helpful. Thanks.
> >
>
RCHGetTableCell(
RCHGetTableCell(
RCHGetTableCell(
Note, I replaced my stock variable with msft. It would normally be something like "&B1&". Also note, that the references do work on my home computer and use to work on my work computer, so I know I'm not passing an improper parameter.
--- In smf_addin@yahoogrou
>
> For a stand-alone function, a #VALUE! error typically is the cause of
> passing an improper parameter to the function. Can you give me an example
> of a function invocation that gives you that error?
>
> On Mon, Oct 22, 2012 at 6:17 AM, twopourdrummer <twopourdrummer@
>
> > Got an interesting issue. On my work computer, any text field that I'm
> > trying to pull off a website creates a #value error. It use to work fine,
> > then one day, it didn't. It works fine at home so I know they still work
> > correctly. My guess is there is some type of setting on my Excel at work
> > that is causing this, but I can't find it. I saw one post that talked about
> > changing the transition formula entry option but that didn't work. Any
> > ideas would be helpful. Thanks.
> >
>
Wed Oct 24, 2012 10:10 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Hmmm. The first and third time out on me, both in EXCEL and in FireFox. The
second one works fine.
I can't think of any reason why these would return with a #VALUE! error.
On Wed, Oct 24, 2012 at 6:55 AM, twopourdrummer <twopourdrummer@yahoo.com >wrote:
> Sure
>
> RCHGetTableCell("
> http://www.smartmoney.com/eqsnaps/index.cfm?story=competition&symbol=msft,
> 3,">Competitive Analysis")
>
>
> RCHGetTableCell("http://www.smartmoney.com/quote/msft/?story=profile
> ",1,"Address")
>
> RCHGetTableCell("
> http://www.zacks.com/research/report.php?type=report&t=msft ",1,"Sector:")
>
> Note, I replaced my stock variable with msft. It would normally be
> something like "&B1&". Also note, that the references do work on my home
> computer and use to work on my work computer, so I know I'm not passing an
> improper parameter.
>
second one works fine.
I can't think of any reason why these would return with a #VALUE! error.
On Wed, Oct 24, 2012 at 6:55 AM, twopourdrummer <twopourdrummer@
> Sure
>
> RCHGetTableCell(
> http://www.smartmon
> 3,">Competitive Analysis")
>
>
> RCHGetTableCell(
> ",1,"Address"
>
> RCHGetTableCell(
> http://www.zacks.
>
> Note, I replaced my stock variable with msft. It would normally be
> something like "&B1&". Also note, that the references do work on my home
> computer and use to work on my work computer, so I know I'm not passing an
> improper parameter.
>
Wed Oct 24, 2012 3:35 pm (PDT) . Posted by:
"oryxius" oryxius
Hi Randy,
CNBC has this little nifty tool that illustrates a company's insiders' sentiment. Here is an example using JPM:
http://data.cnbc.com/quotes/jpm/tab/8.3
Is it possible to embed this into an excel comment box similar to the way a stockchart can be embedded?
Thank you for all the help you are providing.
Moe
CNBC has this little nifty tool that illustrates a company's insiders' sentiment. Here is an example using JPM:
http://data.
Is it possible to embed this into an excel comment box similar to the way a stockchart can be embedded?
Thank you for all the help you are providing.
Moe
Wed Oct 24, 2012 8:18 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
No, because it's not an image. It's generated using HTML tags.
Also, since the web page is generated, so there is no underlying data for
the add-in to extract.
On Wed, Oct 24, 2012 at 3:35 PM, oryxius <oryxius@yahoo.com > wrote:
>
> CNBC has this little nifty tool that illustrates a company's insiders'
> sentiment. Here is an example using JPM:
>
> http://data.cnbc.com/quotes/jpm/tab/8.3
>
> Is it possible to embed this into an excel comment box similar to the way
> a stockchart can be embedded?
>
> Thank you for all the help you are providing.
>
Also, since the web page is generated, so there is no underlying data for
the add-in to extract.
On Wed, Oct 24, 2012 at 3:35 PM, oryxius <oryxius@yahoo.
>
> CNBC has this little nifty tool that illustrates a company's insiders'
> sentiment. Here is an example using JPM:
>
> http://data.
>
> Is it possible to embed this into an excel comment box similar to the way
> a stockchart can be embedded?
>
> Thank you for all the help you are providing.
>
Wed Oct 24, 2012 10:01 pm (PDT) . Posted by:
"R" venkat1926
I have donwlaoded the files given under
RCH_Stock_Market_Functions-2.1.2012.02.02.zip
will this work in windows 7.
I had similar files when I had windows xp and it worked but whenI shifted to windows 7 these addin did not work. that is why the query.
thanks.
RCH_Stock_Market_
will this work in windows 7.
I had similar files when I had windows xp and it worked but whenI shifted to windows 7 these addin did not work. that is why the query.
thanks.
Wed Oct 24, 2012 10:02 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
As far as I know, it does, but I have no personal experience with it...
On Wed, Oct 24, 2012 at 9:20 PM, R <venkat1926@yahoo.com > wrote:
> I have donwlaoded the files given under
> RCH_Stock_Market_Functions-2.1.2012.02.02.zip
>
> will this work in windows 7.
>
> I had similar files when I had windows xp and it worked but whenI shifted
> to windows 7 these addin did not work. that is why the query.
>
On Wed, Oct 24, 2012 at 9:20 PM, R <venkat1926@yahoo.
> I have donwlaoded the files given under
> RCH_Stock_Market_
>
> will this work in windows 7.
>
> I had similar files when I had windows xp and it worked but whenI shifted
> to windows 7 these addin did not work. that is why the query.
>
Tidak ada komentar:
Posting Komentar