15 New Messages
Digest #2469
Messages
Wed Jan 16, 2013 10:47 am (PST) . Posted by:
"bobc94595" bobc94595
I have an array-entered sheet of 100 stocks to gather Yahoo Codes n, l1, d and y.
The information for "y" is overstated by 100. For example, AT&T dividend is returning 530% rather than 5.3%.
Is there some way of adjusting the array formula to correct this issue?
The information for "y" is overstated by 100. For example, AT&T dividend is returning 530% rather than 5.3%.
Is there some way of adjusting the array formula to correct this issue?
Wed Jan 16, 2013 2:18 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Nope, sorry. Yahoo is returning the percentage amount, not a percentage
value (i.e. 5.3 instead of .053).
You would need to adjust it yourself, in another column.
On Tue, Jan 15, 2013 at 10:33 PM, bobc94595 conlonrc@comcast.net > wrote:
> I have an array-entered sheet of 100 stocks to gather Yahoo Codes n, l1, d
> and y.
>
> The information for "y" is overstated by 100. For example, AT&T dividend
> is returning 530% rather than 5.3%.
>
> Is there some way of adjusting the array formula to correct this issue?
>
value (i.e. 5.3 instead of .053).
You would need to adjust it yourself, in another column.
On Tue, Jan 15, 2013 at 10:33 PM, bobc94595 conlonrc@comcast.
> I have an array-entered sheet of 100 stocks to gather Yahoo Codes n, l1, d
> and y.
>
> The information for "y" is overstated by 100. For example, AT&T dividend
> is returning 530% rather than 5.3%.
>
> Is there some way of adjusting the array formula to correct this issue?
>
Wed Jan 16, 2013 1:58 pm (PST) . Posted by:
"nlavery" nlavery
To accomplish a fully functional version of the add-in in one workbook (without the xla and related codes in C:\SMF Add-In), would one create a number of modules and then paste the current codes into them? Toby
------------
--- In smf_addin@yahoogrou
>
> You'd need to copy over the necessary VBA modules into the workbook, so
> that it doesn't need the external functions from the add-in. There are
> examples of such workbooks here:
>
> Files > Templates and Examples > SMFLite
>
> ..although they are based on older versions of add-in functions. But that's
> the problem with not using the add-in -- modules don't get updated
> automatically.
>
> On Tue, Jan 15, 2013 at 2:05 PM, d.sweitzer22 wrote:
>
> > Is there a way to add certain SMF add-in functions (such as
> > RCHGetYahooQuotes and RCHGetYahooHistory) into a work book so that I could
> > use the spread sheet on other computers without having to install the
> > add-in on each machine?
> >
>
Wed Jan 16, 2013 6:35 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
That wouldn't do the trick if you use RCHGetElementNumber(), because the
element definitions are external to the XLA file. But, for other functions,
that should work.
On Tue, Jan 15, 2013 at 9:23 PM, nlavery toby.avery@gmail.com > wrote:
>
> To accomplish a fully functional version of the add-in in one workbook
> (without the xla and related codes in C:\SMF Add-In), would one create a
> number of modules and then paste the current codes into them? Toby
>
element definitions are external to the XLA file. But, for other functions,
that should work.
On Tue, Jan 15, 2013 at 9:23 PM, nlavery toby.avery@gmail.
>
> To accomplish a fully functional version of the add-in in one workbook
> (without the xla and related codes in C:\SMF Add-In), would one create a
> number of modules and then paste the current codes into them? Toby
>
Wed Jan 16, 2013 3:42 pm (PST) . Posted by:
"lawrence.leesh" lawrence.leesh
Hi Randy
The file you mentioned below smfGetAdvFNElement-Template-All-Line-Items.xls could not retrieve advfn data and it returns blank. Did advfn make some changes to their website?
regards
LL
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> Check out the templates folder in the FILES area...
>
> You're talking about multiple web pages worth of data. There's no reason to
> get it all at once, since each piece can be gotten individually. There
> would be no advantage to creating a function that retrieved everything --
> it would still require the same number of web page retrievals. The add-in
> functions are basically building blocks, so you can build what you want.
>
> Having said that, the smfGetAdvFNElement-Template-All-Line-Items.xls
> template is probably the closest to what you're asking for.
>
> On Tue, Jan 1, 2013 at 1:15 PM, hathangz wrote:
>
> > Is there a function that I can enter the ticker and the custom add-in will
> > give the recent financial statement data i.e the balance sheet, income
> > statement, and cash flows. I know there is a code to get each element, but
> > I want to essentially type in the ticker in the code and it spits out
> > whatever financial statement data is available on the website.
> >
>
The file you mentioned below smfGetAdvFNElement-
regards
LL
--- In smf_addin@yahoogrou
>
> Check out the templates folder in the FILES area...
>
> You're talking about multiple web pages worth of data. There's no reason to
> get it all at once, since each piece can be gotten individually. There
> would be no advantage to creating a function that retrieved everything --
> it would still require the same number of web page retrievals. The add-in
> functions are basically building blocks, so you can build what you want.
>
> Having said that, the smfGetAdvFNElement-
> template is probably the closest to what you're asking for.
>
> On Tue, Jan 1, 2013 at 1:15 PM, hathangz wrote:
>
> > Is there a function that I can enter the ticker and the custom add-in will
> > give the recent financial statement data i.e the balance sheet, income
> > statement, and cash flows. I know there is a code to get each element, but
> > I want to essentially type in the ticker in the code and it spits out
> > whatever financial statement data is available on the website.
> >
>
Wed Jan 16, 2013 6:33 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I just retrieved the file from the Yahoo group, used ticker symbol MMM, and
it worked fine for me.
What do you get with:
=smfGetADVFNElement("MMM","A",999)
=smfGetADVFNElement("MMM","A",1,">year end date<",,"--")
If you plug this URL into IE:
http://www.advfn.com/p.php?pid=financials&symbol=MMM
...do you get redirected to a different URL?
On Wed, Jan 16, 2013 at 3:41 PM, lawrence.leesh lawrence.leesh@yahoo.com >wrote:
>
> The file you mentioned below
> smfGetAdvFNElement-Template-All-Line-Items.xls could not retrieve advfn
> data and it returns blank. Did advfn make some changes to their website?
>
it worked fine for me.
What do you get with:
=smfGetADVFNElement
=smfGetADVFNElement
If you plug this URL into IE:
http://www.advfn.
...do you get redirected to a different URL?
On Wed, Jan 16, 2013 at 3:41 PM, lawrence.leesh lawrence.leesh@
>
> The file you mentioned below
> smfGetAdvFNElement-
> data and it returns blank. Did advfn make some changes to their website?
>
Wed Jan 16, 2013 3:59 pm (PST) . Posted by:
"lawrence.leesh" lawrence.leesh
Hi, did advfn make changes to their website. I can't download any advfn data and even this file does not return any data.
smfGetAdvFNElement-Template-All-Line-Items.xls
thanks
LL
smfGetAdvFNElement-
thanks
LL
Wed Jan 16, 2013 6:36 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
That one also works fine for me when I used ticker symbol MMM.
On Wed, Jan 16, 2013 at 4:01 PM, lawrence.leesh lawrence.leesh@yahoo.com >wrote:
> Hi, did advfn make changes to their website. I can't download any advfn
> data and even this file does not return any data.
>
> smfGetAdvFNElement-Template-All-Line-Items.xls
>
On Wed, Jan 16, 2013 at 4:01 PM, lawrence.leesh lawrence.leesh@
> Hi, did advfn make changes to their website. I can't download any advfn
> data and even this file does not return any data.
>
> smfGetAdvFNElement-
>
Wed Jan 16, 2013 5:47 pm (PST) . Posted by:
"iamruf" iamruf
I am using this formula =RCHGetWebData("http://tmx.quotemedia.com/json/getQuotesMini.json.php?jsoncallba \ck=jsonp1358089488792&symbols=ATL1879&webmasterId=101020&_=1358089550952",1,1000)
and then this formula to extract a value =MID(D2,FIND("last",D2)+7,5)which returns:
({"copyright":"Copyright (c) 2013 QuoteMedia, Inc.","symbolcount";:1,"quotedata":[{"symbol":"ATL1879","datatype":"mutual fund","entitlement";:"EOD";,"delaymin":null,"datetime":"2013-01-15T20:00:00-05:00","pricedata":{ "last":"11.4162", "change":"0.021", "changepercent":"0.184288", "sharevolume":"0"}}]});
In this case the result returned is 28.87 in general format.
When I click the format cell drop-down for currency, the $ symbol is missing.
I have tried converting to text or number & back to currency but cannot get the $ symbol.
Any suggestions? Thanks
and then this formula to extract a value =MID(D2,FIND(
({"copyright&q
In this case the result returned is 28.87 in general format.
When I click the format cell drop-down for currency, the $ symbol is missing.
I have tried converting to text or number & back to currency but cannot get the $ symbol.
Any suggestions? Thanks
Wed Jan 16, 2013 6:40 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It's probably still just a text string, so a numeric format wouldn't affect
it. Instead of:
=MID(D2,FIND("last",D2)+7,5)
...try:
=0+MID(D2,FIND("last",D2)+7,5)
The numeric operation will attempt to convert the string into a number and
add it to zero, which just turns the original string into a number if it is
a valid number. Another option would be:
=VALUE(MID(D2,FIND("last",D2)+7,5))
On Wed, Jan 16, 2013 at 6:18 PM, iamruf iamruf@yahoo.com > wrote:
> I am using this formula =RCHGetWebData("
> http://tmx.quotemedia.com/json/getQuotesMini.json.php?jsoncallba \ck=jsonp1358089488792&symbols=ATL1879&webmasterId=101020&_=1358089550952
> ",1,1000)
> and then this formula to extract a value =MID(D2,FIND("last",D2)+7,5)which
> returns:
> ({"copyright":"Copyright (c) 2013 QuoteMedia,
> Inc.","symbolcount";:1,"quotedata":[{"symbol":"ATL1879","datatype":"mutual
> fund","entitlement";:"EOD";,"delaymin":null,"datetime":"2013-01-15T20:00:00-05:00","pricedata":{
> "last":"11.4162", "change":"0.021", "changepercent":"0.184288",
> "sharevolume":"0"}}]});
>
> In this case the result returned is 28.87 in general format.
> When I click the format cell drop-down for currency, the $ symbol is
> missing.
> I have tried converting to text or number & back to currency but cannot
> get the $ symbol.
> Any suggestions? Thanks
>
it. Instead of:
=MID(D2,FIND(
...try:
=0+MID(D2,FIND(
The numeric operation will attempt to convert the string into a number and
add it to zero, which just turns the original string into a number if it is
a valid number. Another option would be:
=VALUE(MID(D2,
On Wed, Jan 16, 2013 at 6:18 PM, iamruf iamruf@yahoo.
> I am using this formula =RCHGetWebData(
> http://tmx.quotemed
> ",1,1000)
> and then this formula to extract a value =MID(D2,FIND(
> returns:
> ({"copyright&q
> Inc.","
> fund","
> "last"
> "sharevolume&q
>
> In this case the result returned is 28.87 in general format.
> When I click the format cell drop-down for currency, the $ symbol is
> missing.
> I have tried converting to text or number & back to currency but cannot
> get the $ symbol.
> Any suggestions? Thanks
>
Wed Jan 16, 2013 6:28 pm (PST) . Posted by:
"cesarcrivelli" cesarcrivelli
Hi All,
I am from Brasil, and just few days ago I found this amazing solution regarding stock market data.
The issue here is that most of the web content its not related to Brazilian Stock Market.
Bloomberg as a WorldWide data provider has at their web site some info that I would like to retrieve.
The bottom line is... Can this add-in retrieve data from bloomberg website? Does anybody get data of Brazilian Stock Market: Would be nice to have some color about this!
Best,
Cesar
I am from Brasil, and just few days ago I found this amazing solution regarding stock market data.
The issue here is that most of the web content its not related to Brazilian Stock Market.
Bloomberg as a WorldWide data provider has at their web site some info that I would like to retrieve.
The bottom line is... Can this add-in retrieve data from bloomberg website? Does anybody get data of Brazilian Stock Market: Would be nice to have some color about this!
Best,
Cesar
Wed Jan 16, 2013 6:43 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It all depends on how the data is placed on the web page. The add-in
extracts data from the source code of the web page, so if the data is
placed on the web page dynamically, there may be nothing in the source code
for the add-in to extract. A sample URL, along with an item you want to
extract, would help.
On Wed, Jan 16, 2013 at 4:36 PM, cesarcrivelli cesarcrivelli@gmail.com >wrote:
>
> I am from Brasil, and just few days ago I found this amazing solution
> regarding stock market data.
>
> The issue here is that most of the web content its not related to
> Brazilian Stock Market.
>
> Bloomberg as a WorldWide data provider has at their web site some info
> that I would like to retrieve.
>
> The bottom line is... Can this add-in retrieve data from bloomberg
> website? Does anybody get data of Brazilian Stock Market: Would be nice to
> have some color about this!
>
extracts data from the source code of the web page, so if the data is
placed on the web page dynamically, there may be nothing in the source code
for the add-in to extract. A sample URL, along with an item you want to
extract, would help.
On Wed, Jan 16, 2013 at 4:36 PM, cesarcrivelli cesarcrivelli@
>
> I am from Brasil, and just few days ago I found this amazing solution
> regarding stock market data.
>
> The issue here is that most of the web content its not related to
> Brazilian Stock Market.
>
> Bloomberg as a WorldWide data provider has at their web site some info
> that I would like to retrieve.
>
> The bottom line is... Can this add-in retrieve data from bloomberg
> website? Does anybody get data of Brazilian Stock Market: Would be nice to
> have some color about this!
>
Wed Jan 16, 2013 10:00 pm (PST) . Posted by:
"bobc94595" bobc94595
Is there a formula modification which can be made to the array formula "RCHGetYahooQuotes(A4:A106,B3:E3)" which will prevent automatic recalculation or update of the worksheet upon its next opening? I would like to preserve the sheet as of a particular date (e.g. year end 12/31/12,) and not have it recalculated automatically if I open it thereafter. How do I preserve the sheet as of a specific date?
How does one establish a trigger cell for recalculation?
How does one establish a trigger cell for recalculation?
Wed Jan 16, 2013 10:08 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Only two ways to prevent the recalculation and preserve what is displayed
-- turn of automatic calculation, or convert everything to values. If you
wanted to preserve the values infrequently, as in the year-end case, I
would first create another worksheet, then copy the original worksheet and
paste special both values and formats into the new empty workbook, and then
name the tab appropriately.
As far as a trigger cell, it would be something like:
=IF($B$2<>"Y","--",RCHGetYahooQuotes(A4:A106,B3:E3))
...so when you save the workbook, you set cell B2 to "N", and everything
will be set to "--". When you next open the workbook, no Internet access
would be performed, because the RCHGetYahooQuotes() function would not be
executed.
Typically, I do that when it would take some time to open a workbook,
because of the number of Internet accesses that need to be done. And I may
not want the current formulas to update until after I've made some changes
to the workbook.
On Wed, Jan 16, 2013 at 11:00 PM, bobc94595 conlonrc@comcast.net > wrote:
> Is there a formula modification which can be made to the array formula
> "RCHGetYahooQuotes(A4:A106,B3:E3)" which will prevent automatic
> recalculation or update of the worksheet upon its next opening? I would
> like to preserve the sheet as of a particular date (e.g. year end
> 12/31/12,) and not have it recalculated automatically if I open it
> thereafter. How do I preserve the sheet as of a specific date?
>
> How does one establish a trigger cell for recalculation?
>
-- turn of automatic calculation, or convert everything to values. If you
wanted to preserve the values infrequently, as in the year-end case, I
would first create another worksheet, then copy the original worksheet and
paste special both values and formats into the new empty workbook, and then
name the tab appropriately.
As far as a trigger cell, it would be something like:
=IF($B$2<
...so when you save the workbook, you set cell B2 to "N"
will be set to "--"
would be performed, because the RCHGetYahooQuotes(
executed.
Typically, I do that when it would take some time to open a workbook,
because of the number of Internet accesses that need to be done. And I may
not want the current formulas to update until after I've made some changes
to the workbook.
On Wed, Jan 16, 2013 at 11:00 PM, bobc94595 conlonrc@comcast.
> Is there a formula modification which can be made to the array formula
> "RCHGetYahooQu
> recalculation or update of the worksheet upon its next opening? I would
> like to preserve the sheet as of a particular date (e.g. year end
> 12/31/12,) and not have it recalculated automatically if I open it
> thereafter. How do I preserve the sheet as of a specific date?
>
> How does one establish a trigger cell for recalculation?
>
Wed Jan 16, 2013 10:06 pm (PST) . Posted by:
"mff2805" mff2805
Thanks Randy,
question: if i grab all 50 stocks and all the columns for each stock by varying pRows andd pCells, is that just one grab of the data table or multiple grabs, i.e. is it inefficient? If so, would there be a more efficient way?
just wondering.
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> I don't see a way to get the data from the screen center. Those pages are
> dynamically generated, and the Export and EXCEL output options are
> incomplete when I use the links they generate.
>
> I grab the IBD50 table from the eTables page, with something like:
>
> =RCHGetTableCell("
> http://research.investors.com/etables/IBD50XLS.aspx?tabView=IBD50&from=etables&columnsort1=ibd50rank&columnsorttype1=DESC&columnsort2=&columnsorttype2=DESC ",2," About
> IBD",,"--")
>
> ...where I vary the "pCells" and "pRows" parameters as needed.
>
> On Tue, Jan 15, 2013 at 5:51 PM, mff2805 wrote:
>
> > hi,
> > is there a way to interrogate the IBD from their screen center page? i'm
> > a member and know all about being logged in to their site. the web page is
> > http://research.investors.com/screen-center/?screen=9172
> > but i don't know if the 9172 number is static or dynamic.
> >
> > then, part 2, is there a way to get, say, the top sector performers list
> > from
> > http://research.investors.com/screen-center/?screen=9171
> >
> > etc, etc for their other lists.
> >
>
question: if i grab all 50 stocks and all the columns for each stock by varying pRows andd pCells, is that just one grab of the data table or multiple grabs, i.e. is it inefficient? If so, would there be a more efficient way?
just wondering.
--- In smf_addin@yahoogrou
>
> I don't see a way to get the data from the screen center. Those pages are
> dynamically generated, and the Export and EXCEL output options are
> incomplete when I use the links they generate.
>
> I grab the IBD50 table from the eTables page, with something like:
>
> =RCHGetTableCell(
> http://research.
> IBD",,"
>
> ...where I vary the "pCells" and "pRows" parameters as needed.
>
> On Tue, Jan 15, 2013 at 5:51 PM, mff2805 wrote:
>
> > hi,
> > is there a way to interrogate the IBD from their screen center page? i'm
> > a member and know all about being logged in to their site. the web page is
> > http://research.
> > but i don't know if the 9172 number is static or dynamic.
> >
> > then, part 2, is there a way to get, say, the top sector performers list
> > from
> > http://research.
> >
> > etc, etc for their other lists.
> >
>
Tidak ada komentar:
Posting Komentar