Kamis, 19 Mei 2011

[smf_addin] Digest Number 1833

Messages In This Digest (16 Messages)

Messages

1a.

Re: Inventory & Property Plant & Equipment

Posted by: "Arun" arungarg@comcast.net   ag_shole

Wed May 18, 2011 8:00 am (PDT)



Thanks. That helps.

Arun

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> It's because you use the simple word inventory. That will find you the FIRST
> occurrence of that word on the web page and then give you the next table
> cell. But it would be easy for a word like that to show up in a metatag
> description, or, in this case, part of the JavaScript for an advertisement.
> That's why I typically use the ending HTML tag closing character, if it's
> appropriate. For example:
>
> =RCHGetTableCell("http://finance.yahoo.com/q/bs?s=A", 1, ">Inventory")
>
> On Tue, May 17, 2011 at 10:21 AM, Arun <arungarg@...> wrote:
>
> > I am trying to obtain the latest inventory as well as property plant &
> > equipment data using
> >
> > = RCHGetTableCell("http://finance.yahoo.com/q/bs?s=A", 1, "Inventory").
> >
> > The code returns
> >
> > if(window.yzq_d==null)window.yzq_d=new
> > Object();window.yzq_d['.C5ARmKImn0-']='&U=141se6qs2%2fN%3d.C5ARmKImn0-%2fC%3d601546236.602387688.717674051.687570551%2fD%3dFB2%2fB%3d3082386578634693138%2fV%3d3';
> >
> > instead of the value.
> >
> > If I try to obtain the property plant & equipment data using the same
> > function, I get an error.
> >
> > This problem was not happening a few weeks ago.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2a.

Refreshing Web Queries

Posted by: "GaryFera" gfera@shaw.ca   GaryFera

Wed May 18, 2011 8:48 am (PDT)




Randy, I have done most of the work to incorporate RCHGetTableCell into
my spreadsheet to get all the stock and option quotes that I need from
Yahoo into one page and then I search against that page in my
spreadsheet.

I don't quite understand how to refresh the web data. If I am using a
straight Excel web query then Excel gives me various refresh options.
When using RCHGetTableCell, do I simply use the smfForceRecalulation
macro? I am not sure that I am logging into my Yahoo page from Excel
properly either.

Also, do I want to create the RCHGetTableCell requests into an array
formula as much as possible?

Thanks.

Gary

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
2b.

Re: Refreshing Web Queries

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed May 18, 2011 9:36 am (PDT)



Yes, you would just use the smfForceRecalculation macro -- it purges all of
the saved copies of web pages and then tells EXCEL to recalculate. When
recalculating, the add-in functions will retrieve and save fresh copies of
web pages, as needed, from which to extract the data.

There is no point in array-entering the RCHGetTableCell() function -- it
only returns a single data item anyway. The only function that runs faster
with array-entering is the RCHGetYahooQuotes() function, because Yahoo
provides the CSV file with all requested items in a single Internet access.

If you use the EXCEL Web Query process (keyboard shortcut alt+d+d+w) to log
in, just go to web address http://finance.yahoo.com. If it says "Hi, Gary"
(or whatever) in the upper left hand corner, then you are already logged in.
If it says "New User?", then you need to use the "Sign In" option to log in.

On Wed, May 18, 2011 at 8:48 AM, GaryFera <gfera@shaw.ca> wrote:

> Randy, I have done most of the work to incorporate RCHGetTableCell into
> my spreadsheet to get all the stock and option quotes that I need from Yahoo
> into one page and then I search against that page in my spreadsheet.
>
> I don't quite understand how to refresh the web data. If I am using a
> straight Excel web query then Excel gives me various refresh options. When
> using RCHGetTableCell, do I simply use the smfForceRecalulation macro? I am
> not sure that I am logging into my Yahoo page from Excel properly either.
>
> Also, do I want to create the RCHGetTableCell requests into an array
> formula as much as possible?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
2c.

Re: Refreshing Web Queries

Posted by: "GaryFera" gfera@shaw.ca   GaryFera

Wed May 18, 2011 1:06 pm (PDT)




Randy, I am logged into My.Yahoo all day long as confirmed by the
message "Hi GaryFera". Yet executing the smfForceRecalculation macro
does not seem to properly refresh the data. The only thing that works is
closing and re-opening Excel. Any suggestions?

Gary

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...>
wrote:
>
> Yes, you would just use the smfForceRecalculation macro -- it purges
all of
> the saved copies of web pages and then tells EXCEL to recalculate.
When
> recalculating, the add-in functions will retrieve and save fresh
copies of
> web pages, as needed, from which to extract the data.
>
> There is no point in array-entering the RCHGetTableCell() function --
it
> only returns a single data item anyway. The only function that runs
faster
> with array-entering is the RCHGetYahooQuotes() function, because Yahoo
> provides the CSV file with all requested items in a single Internet
access.
>
> If you use the EXCEL Web Query process (keyboard shortcut alt+d+d+w)
to log
> in, just go to web address http://finance.yahoo.com. If it says "Hi,
Gary"
> (or whatever) in the upper left hand corner, then you are already
logged in.
> If it says "New User?", then you need to use the "Sign In" option to
log in.
>
> On Wed, May 18, 2011 at 8:48 AM, GaryFera gfera@... wrote:
>
> > Randy, I have done most of the work to incorporate RCHGetTableCell
into
> > my spreadsheet to get all the stock and option quotes that I need
from Yahoo
> > into one page and then I search against that page in my spreadsheet.
> >
> > I don't quite understand how to refresh the web data. If I am using
a
> > straight Excel web query then Excel gives me various refresh
options. When
> > using RCHGetTableCell, do I simply use the smfForceRecalulation
macro? I am
> > not sure that I am logging into my Yahoo page from Excel properly
either.
> >
> > Also, do I want to create the RCHGetTableCell requests into an array
> > formula as much as possible?
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
2d.

Re: Refreshing Web Queries

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed May 18, 2011 2:09 pm (PDT)



Check your Internet Options and make sure IE is configured to always get a
new web page when they are visited. Otherwise, IE can cache the web pages as
well.

On Wed, May 18, 2011 at 1:06 PM, GaryFera <gfera@shaw.ca> wrote:

>
> Randy, I am logged into My.Yahoo all day long as confirmed by the
> message "Hi GaryFera". Yet executing the smfForceRecalculation macro
> does not seem to properly refresh the data. The only thing that works is
> closing and re-opening Excel. Any suggestions?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
3.

Historical option price data

Posted by: "lexstar" lexstar@yahoo.com   lexstar

Wed May 18, 2011 11:01 am (PDT)



Does anyone know where we may be able to obtain/download historical options price data?

For example, all monthly closing option price for say SPY for year 2010.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)
4a.

SMF-Template-Yahoo-Google-Pre-Market-Quotes.xls

Posted by: "t" tnmc77@yahoo.com   tnmc77

Wed May 18, 2011 3:09 pm (PDT)



I downloaded the template file but I can't seem to make it work. Get #Name? in all 10 cells. What am doing wrong? The rest of the smf-addin functions work well so I must be doing something wrong.

Thanks in advance. Can't sing enough praises about the smf-addin.

Shashank

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
4b.

Re: SMF-Template-Yahoo-Google-Pre-Market-Quotes.xls

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed May 18, 2011 5:01 pm (PDT)



Might be a location issue:

http://finance.groups.yahoo.com/group/smf_addin/message/11346

...or you may need to get the latest version of the add-in, since I used
some of the functions that were new to 2.1.2010.08.02. To see what version
of the add-in you have:

=RCHGetElementNumber("Version")

On Wed, May 18, 2011 at 3:09 PM, t <tnmc77@yahoo.com> wrote:

> I downloaded the template file but I can't seem to make it work. Get #Name?
> in all 10 cells. What am doing wrong? The rest of the smf-addin functions
> work well so I must be doing something wrong.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
4c.

Re: SMF-Template-Yahoo-Google-Pre-Market-Quotes.xls

Posted by: "t" tnmc77@yahoo.com   tnmc77

Wed May 18, 2011 5:18 pm (PDT)



I have the latest version. Find and replace all did not work. Any suggestion?
Shashank

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Might be a location issue:
>
> http://finance.groups.yahoo.com/group/smf_addin/message/11346
>
> ...or you may need to get the latest version of the add-in, since I used
> some of the functions that were new to 2.1.2010.08.02. To see what version
> of the add-in you have:
>
> =RCHGetElementNumber("Version")
>
> On Wed, May 18, 2011 at 3:09 PM, t <tnmc77@...> wrote:
>
> > I downloaded the template file but I can't seem to make it work. Get #Name?
> > in all 10 cells. What am doing wrong? The rest of the smf-addin functions
> > work well so I must be doing something wrong.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
4d.

Re: SMF-Template-Yahoo-Google-Pre-Market-Quotes.xls

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed May 18, 2011 5:30 pm (PDT)



Do you get #NAME? with either of these functions?

=smfGetTagContent("http://www.google.com/finance?q=MMM","div",1)
=smfStrExtr("12345","2","4")

Otherwise, can you do a copy and paste of the full formula shown in cell C5?

On Wed, May 18, 2011 at 5:18 PM, t <tnmc77@yahoo.com> wrote:

> I have the latest version. Find and replace all did not work. Any
> suggestion?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
4e.

Re: SMF-Template-Yahoo-Google-Pre-Market-Quotes.xls

Posted by: "t" tnmc77@yahoo.com   tnmc77

Wed May 18, 2011 5:56 pm (PDT)



1) I get some long text when I pasted the first one.

2) i get "3" with the second one.

3) I am not sure what you meant. Here is the formula from your original quotes file, cell C5.

=IF('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfGetTagContent("http://www.google.com/finance?q="&Ticker,"div",-1,"Pre-Market:"),"",":")="Pre-Market","Pre-Market",
IF('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfGetTagContent("http://www.google.com/finance?q="&Ticker,"span",-1,"Real-Time:"),"",":")="Real-Time","Real Time",
IF('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfGetTagContent("http://www.google.com/finance?q="&Ticker,"div",-1,"After Hours:"),"",":")="After Hours","After Hours","--")))

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Do you get #NAME? with either of these functions?
>
> =smfGetTagContent("http://www.google.com/finance?q=MMM","div",1)
> =smfStrExtr("12345","2","4")
>
> Otherwise, can you do a copy and paste of the full formula shown in cell C5?
>
> On Wed, May 18, 2011 at 5:18 PM, t <tnmc77@...> wrote:
>
> > I have the latest version. Find and replace all did not work. Any
> > suggestion?
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
4f.

Re: SMF-Template-Yahoo-Google-Pre-Market-Quotes.xls

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed May 18, 2011 6:02 pm (PDT)



You need to do a "Find and Replace All" (keyboard shortcut Ctrl-H) and
change all occurrences of:

'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!

...to be nothing. That's what the previous link I referred to was
indicating.

Any time you see a hard-coded location of the add-in in a formula like that,
they need to be removed. Once you save the workbook on your own machine, you
should never see a hard-coded location again (unless you move your add-in to
a different location). The problem is that EXCEL saves a hard-coded location
to the add-in whenever it saves a workbook. If the person opening the
workbook has the add-in in a different location than when the workbook was
saved, those hard-coded locations need to be manually removed. EXCEL
automatically removes those hard-coded locations if it does find the add-in
in that location.

On Wed, May 18, 2011 at 5:56 PM, t <tnmc77@yahoo.com> wrote:

> 3) I am not sure what you meant. Here is the formula from your original
> quotes file, cell C5.
>
> =IF('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF
> Add-in\RCH_Stock_Market_Functions.xla'!smfGetTagContent("
> http://www.google.com/finance?q=
> "&Ticker,"div",-1,"Pre-Market:"),"",":")="Pre-Market","Pre-Market",
> IF('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF
> Add-in\RCH_Stock_Market_Functions.xla'!smfGetTagContent("
> http://www.google.com/finance?q="&Ticker,"span",-1,"Real-Time:"),"",":")="Real-Time","Real
> Time",
> IF('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF
> Add-in\RCH_Stock_Market_Functions.xla'!smfGetTagContent("
> http://www.google.com/finance?q="&Ticker,"div",-1,"After
> Hours:"),"",":")="After Hours","After Hours","--")))
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
4g.

Re: SMF-Template-Yahoo-Google-Pre-Market-Quotes.xls

Posted by: "t" tnmc77@yahoo.com   tnmc77

Wed May 18, 2011 6:29 pm (PDT)



It worked. You are a genius.
Thanks for your help and patience.
Shashank

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> You need to do a "Find and Replace All" (keyboard shortcut Ctrl-H) and
> change all occurrences of:
>
> 'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!
>
> ...to be nothing. That's what the previous link I referred to was
> indicating.
>
> Any time you see a hard-coded location of the add-in in a formula like that,
> they need to be removed. Once you save the workbook on your own machine, you
> should never see a hard-coded location again (unless you move your add-in to
> a different location). The problem is that EXCEL saves a hard-coded location
> to the add-in whenever it saves a workbook. If the person opening the
> workbook has the add-in in a different location than when the workbook was
> saved, those hard-coded locations need to be manually removed. EXCEL
> automatically removes those hard-coded locations if it does find the add-in
> in that location.
>
> On Wed, May 18, 2011 at 5:56 PM, t <tnmc77@...> wrote:
>
> > 3) I am not sure what you meant. Here is the formula from your original
> > quotes file, cell C5.
> >
> > =IF('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF
> > Add-in\RCH_Stock_Market_Functions.xla'!smfGetTagContent("
> > http://www.google.com/finance?q=
> > "&Ticker,"div",-1,"Pre-Market:"),"",":")="Pre-Market","Pre-Market",
> > IF('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF
> > Add-in\RCH_Stock_Market_Functions.xla'!smfGetTagContent("
> > http://www.google.com/finance?q="&Ticker,"span",-1,"Real-Time:"),"",":")="Real-Time","Real
> > Time",
> > IF('C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!smfStrExtr('C:\SMF
> > Add-in\RCH_Stock_Market_Functions.xla'!smfGetTagContent("
> > http://www.google.com/finance?q="&Ticker,"div",-1,"After
> > Hours:"),"",":")="After Hours","After Hours","--")))
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
5a.

custom refresh

Posted by: "snvk1012" krishnasnv@yahoo.com   snvk1012

Thu May 19, 2011 1:16 am (PDT)



Hi Randy,

Is it possible to force refresh only on a particular range on sheet.

For example, I am retrieving values on 5 variables for 100 stocks. I have two queries regarding this,

a)Everytime I open the excel, the formula automatically fetches the data from web and hence it takes a lot of time. I would like to have this auto calc. or auto refresh disabled. What I mean is, I like the formula to fetch data from web only upon some trigger or like F9. (Setting through options will probably change all other excel files settings and hence not choosing that as an option.)

b) If there is a way, then is it possible to refresh only a selected subset.

Many thanks for your help and support.

Regards,

Krishna

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
5b.

Re: custom refresh

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu May 19, 2011 1:27 am (PDT)



It all depends on which functions you are using.

If you need to use smfForceRecalculation (see "Links" area of the group for
more info), for RCHGetElementNumber() or RCHGetTableCell(), you can't limit
it to any subset. But you could look at replacing the process with
smfUpdateDownloadTable (again, see the "Links").

If you are using RCHGetYahooQuotes(), your best bet would be to array-enter
the formula to get everything at once. That should take only a second or
two, and then the amount of time isn't an issue.

On Thu, May 19, 2011 at 1:15 AM, snvk1012 <krishnasnv@yahoo.com> wrote:

>
> Is it possible to force refresh only on a particular range on sheet.
>
> For example, I am retrieving values on 5 variables for 100 stocks. I have
> two queries regarding this,
>
> a)Everytime I open the excel, the formula automatically fetches the data
> from web and hence it takes a lot of time. I would like to have this auto
> calc. or auto refresh disabled. What I mean is, I like the formula to fetch
> data from web only upon some trigger or like F9. (Setting through options
> will probably change all other excel files settings and hence not choosing
> that as an option.)
>
> b) If there is a way, then is it possible to refresh only a selected
> subset.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
6.

Invitation to connect on LinkedIn

Posted by: "Ali Imran Naqvi" naqviimran@gmail.com   dillli_ka_baadshah

Thu May 19, 2011 2:20 am (PDT)



LinkedIn
------------

I'd like to add you to my professional network on LinkedIn.

- Ali Imran

Ali Imran Naqvi
Research Head, Knowledge Centre at Gensol Consultants Pvt. Ltd.
Ahmedabad Area, India

Confirm that you know Ali Imran Naqvi
https://www.linkedin.com/e/-tv9qrq-gnvhpb4g-2x/isd/2932209900/j2t-XXXy/

--
(c) 2011, LinkedIn Corporation
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)
Recent Activity
Visit Your Group
Yahoo! News

Get it all here

Breaking news to

entertainment news

Need traffic?

Drive customers

With search ads

on Yahoo!

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Get great advice about dogs and cats. Visit the Dog & Cat Answers Center.

Diposting oleh EXCEL FEED LEARNING di 04.02

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)