13 New Messages
Digest #2519
1a
Re: Is it possible to reference stock symbol in =RCHGetElementNumber by "Randy Harmelink" rharmelink
Messages
Fri Feb 22, 2013 7:36 am (PST) . Posted by:
"Randy Harmelink" rharmelink
You'd use a browser option that allows you to look at the source code of
the web page.
Otherwise, how would you know what you're searching for? What you see
displayed on the web page is not always how things are written up in the
HTML source code. For example, displays of data that appear to be in tables
are not always tables. And displays on the web pages can be other web pages
displayed in a frame.
There is a template that might be of help:
smfGetTagContent-Quick-Webpage-Examination.xls
On Fri, Feb 22, 2013 at 6:10 AM, Tze Wei, tzewei_79@yahoo.com.sg > wrote:
> er....sorry, I'm very new to coding. How do I look at the source code? Is
> there a code in the group?
>
the web page.
Otherwise, how would you know what you're searching for? What you see
displayed on the web page is not always how things are written up in the
HTML source code. For example, displays of data that appear to be in tables
are not always tables. And displays on the web pages can be other web pages
displayed in a frame.
There is a template that might be of help:
smfGetTagContent-
On Fri, Feb 22, 2013 at 6:10 AM, Tze Wei, tzewei_79@yahoo.
> er....sorry, I'm very new to coding. How do I look at the source code? Is
> there a code in the group?
>
Fri Feb 22, 2013 7:37 am (PST) . Posted by:
"Roger Diaz" rogerdiazbernal
Thanks Randy
Download data Quaterly OK
I admire your knowledge and help.
Roger
Download data Quaterly OK
I admire your knowledge and help.
Roger
Fri Feb 22, 2013 7:40 am (PST) . Posted by:
"Randy Harmelink" rharmelink
You can set up the RCHGetYahooQuotes() function to be updated by
recalculating, if you make the function volatile.
But for most other functions, no. They rely on stored copies of web pages.
In order to be truly "refreshed", those stored web pages need to be purged
and retrieved again from the Internet. That's an "all or nothing" deal.
That's what the smfForceRecalculation macro is for.
A possible solution is to use the smfUpdateDownloadTable process. It loads
values into your workbook instead of formulas that calculate. Then, you can
be in control of what gets updated and when. But it may not fit your needs.
Another option is to set up a calculation trigger, so that the only
formulas that calculate are those you trigger. For example, something like:
=IF($A$1<>"Y","--",RCHGetElementNumber(....))
...so that the add-in function will only be calculated if cell A1 has "Y"
in it. But you need to save the file when the trigger cell has something
other than "Y" in it, so that when you open the file, no calculations will
be done.
On Fri, Feb 22, 2013 at 4:15 AM, j14mako johnyu@yahoo.com.au > wrote:
>
> btw, an unrelated question: when I use RCHGetElementNumber() or
> RCHGetYahooQuotes(), can I control when it executes and refresh the UDFs? I
> have many UDF cells in my spreadsheet. Each time when I open the file, it
> takes a very long time to load all of them. Most of the time, I only need a
> couple of columns.
>
recalculating, if you make the function volatile.
But for most other functions, no. They rely on stored copies of web pages.
In order to be truly "refreshed&quo
and retrieved again from the Internet. That's an "all or nothing" deal.
That's what the smfForceRecalculati
A possible solution is to use the smfUpdateDownloadTa
values into your workbook instead of formulas that calculate. Then, you can
be in control of what gets updated and when. But it may not fit your needs.
Another option is to set up a calculation trigger, so that the only
formulas that calculate are those you trigger. For example, something like:
=IF($A$1<
...so that the add-in function will only be calculated if cell A1 has "Y"
in it. But you need to save the file when the trigger cell has something
other than "Y" in it, so that when you open the file, no calculations will
be done.
On Fri, Feb 22, 2013 at 4:15 AM, j14mako johnyu@yahoo.
>
> btw, an unrelated question: when I use RCHGetElementNumber
> RCHGetYahooQuotes(
> have many UDF cells in my spreadsheet. Each time when I open the file, it
> takes a very long time to load all of them. Most of the time, I only need a
> couple of columns.
>
Fri Feb 22, 2013 9:50 pm (PST) . Posted by:
"j14mako" j14mako
Using a formula with "Y" to control is a great and simple idea! Thanks.
On a different note, where can I find more information on smfUpdateDownloadTable()? I'm a novice in UDFs/macros/VBA.
Thanks again.
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> You can set up the RCHGetYahooQuotes() function to be updated by
> recalculating, if you make the function volatile.
>
> But for most other functions, no. They rely on stored copies of web pages.
> In order to be truly "refreshed", those stored web pages need to be purged
> and retrieved again from the Internet. That's an "all or nothing" deal.
> That's what the smfForceRecalculation macro is for.
>
> A possible solution is to use the smfUpdateDownloadTable process. It loads
> values into your workbook instead of formulas that calculate. Then, you can
> be in control of what gets updated and when. But it may not fit your needs.
>
> Another option is to set up a calculation trigger, so that the only
> formulas that calculate are those you trigger. For example, something like:
>
> =IF($A$1<>"Y","--",RCHGetElementNumber(....))
>
> ...so that the add-in function will only be calculated if cell A1 has "Y"
> in it. But you need to save the file when the trigger cell has something
> other than "Y" in it, so that when you open the file, no calculations will
> be done.
>
> On Fri, Feb 22, 2013 at 4:15 AM, j14mako wrote:
>
> >
> > btw, an unrelated question: when I use RCHGetElementNumber() or
> > RCHGetYahooQuotes(), can I control when it executes and refresh the UDFs? I
> > have many UDF cells in my spreadsheet. Each time when I open the file, it
> > takes a very long time to load all of them. Most of the time, I only need a
> > couple of columns.
> >
>
On a different note, where can I find more information on smfUpdateDownloadTa
Thanks again.
--- In smf_addin@yahoogrou
>
> You can set up the RCHGetYahooQuotes(
> recalculating, if you make the function volatile.
>
> But for most other functions, no. They rely on stored copies of web pages.
> In order to be truly "refreshed&quo
> and retrieved again from the Internet. That's an "all or nothing" deal.
> That's what the smfForceRecalculati
>
> A possible solution is to use the smfUpdateDownloadTa
> values into your workbook instead of formulas that calculate. Then, you can
> be in control of what gets updated and when. But it may not fit your needs.
>
> Another option is to set up a calculation trigger, so that the only
> formulas that calculate are those you trigger. For example, something like:
>
> =IF($A$1<
>
> ...so that the add-in function will only be calculated if cell A1 has "Y"
> in it. But you need to save the file when the trigger cell has something
> other than "Y" in it, so that when you open the file, no calculations will
> be done.
>
> On Fri, Feb 22, 2013 at 4:15 AM, j14mako wrote:
>
> >
> > btw, an unrelated question: when I use RCHGetElementNumber
> > RCHGetYahooQuotes(
> > have many UDF cells in my spreadsheet. Each time when I open the file, it
> > takes a very long time to load all of them. Most of the time, I only need a
> > couple of columns.
> >
>
Fri Feb 22, 2013 9:52 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
There are several items on it in the LINKS area of the group, as well as an
example in the FILES area.
On Fri, Feb 22, 2013 at 10:50 PM, j14mako johnyu@yahoo.com.au > wrote:
>
> On a different note, where can I find more information on
> smfUpdateDownloadTable()? I'm a novice in UDFs/macros/VBA.
>
example in the FILES area.
On Fri, Feb 22, 2013 at 10:50 PM, j14mako johnyu@yahoo.
>
> On a different note, where can I find more information on
> smfUpdateDownloadTa
>
Fri Feb 22, 2013 7:41 am (PST) . Posted by:
"Randy Harmelink" rharmelink
The drop-down list would be easy to do with a cell validation, but an
extensive list would probably be more cumbersome to find something than it
would be to type in one of the few that are actually useful.
There were some instructions in the file description...
On Fri, Feb 22, 2013 at 7:08 AM, Kermit W. Prather
kermitp@tampabay.rr.com >wrote:
>
>
> Perhaps I can make a suggestion to make the "C3" cell a dropdown with the
> major HTML type.****
>
> ** **
>
> I know it is a lot to ask, but could you in the future include a worksheet
> with instructions on how to use the spreadsheet. ****
>
>
>
extensive list would probably be more cumbersome to find something than it
would be to type in one of the few that are actually useful.
There were some instructions in the file description.
On Fri, Feb 22, 2013 at 7:08 AM, Kermit W. Prather
kermitp@tampabay.
>
>
> Perhaps I can make a suggestion to make the "C3" cell a dropdown with the
> major HTML type.****
>
> ** **
>
> I know it is a lot to ask, but could you in the future include a worksheet
> with instructions on how to use the spreadsheet. ****
>
>
>
Fri Feb 22, 2013 10:27 am (PST) . Posted by:
"Jim" buckleca
Using your very useful "Quick" spreadsheet I added two sheets. One using RCHGetTableCell with inputs for Row # and additional finds. The count is by Cell:
Web Page: http://finance.
Row #
Start String: Financial Highlights
Find 2 Forward Annual Dividend Rate
Find 3
Find 4
Count Tag Contents
0 Forward Annual Dividend Rate4:
1 2.54
2
3
.
.
The other sheet is using RCHGetWebData and extract 1000 for each line. Then I just search the line for the text and that tells me the position to where to extract.
Web Page: http://finance.
Start Pos 50
Offset
Count Tag Contents
1049 web data in here
2049 " "
3049 " "
.
.
I'm sure there's people that could do a better job - but it seems to work for me.
--- In smf_addin@yahoogrou
>
> The drop-down list would be easy to do with a cell validation, but an
> extensive list would probably be more cumbersome to find something than it
> would be to type in one of the few that are actually useful.
>
> There were some instructions in the file description.
>
> On Fri, Feb 22, 2013 at 7:08 AM, Kermit W. Prather
> wrote:
>
> >
> >
> > Perhaps I can make a suggestion to make the “C3†cell a dropdown with the
> > major HTML type.****
> >
> > ** **
> >
> > I know it is a lot to ask, but could you in the future include a worksheet
> > with instructions on how to use the spreadsheet. ****
> >
> >
> >
>
Fri Feb 22, 2013 12:44 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
My table exploration usually varies the row and column, such as:
* * *Web Page:*
http://finance.yahoo.com/q/ks?s=MMM *Find 1:*
>Revenue *Find 2:* *Find
3:* *Find 4:* *End:*
buckleca@yahoo.com > wrote:
>
>
> Using your very useful "Quick" spreadsheet I added two sheets. One using
> RCHGetTableCell with inputs for Row # and additional finds. The count is by
> Cell:
>
> Web Page: http://finance.yahoo.com/q/ks?s=MMM "
> Row #
> Start String: Financial Highlights
> Find 2 Forward Annual Dividend Rate
> Find 3
> Find 4
>
> Count Tag Contents
> 0 Forward Annual Dividend Rate4:
> 1 2.54
> 2
> 3
> .
> .
> The other sheet is using RCHGetWebData and extract 1000 for each line.
> Then I just search the line for the text and that tells me the position to
> where to extract.
>
> Web Page: http://finance.yahoo.com/q/ks?s=MMM "
> Start Pos 50
> Offset
>
> Count Tag Contents
> 1049 web data in here
> 2049 " "
> 3049 " "
>
> I'm sure there's people that could do a better job - but it seems to work
> for me.
>
* * *Web Page:*
http://finance.
>Revenue *Find 2:* *Find
3:* *Find 4:* *End:*
buckleca@yahoo.
>
>
> Using your very useful "Quick" spreadsheet I added two sheets. One using
> RCHGetTableCell with inputs for Row # and additional finds. The count is by
> Cell:
>
> Web Page: http://finance.
> Row #
> Start String: Financial Highlights
> Find 2 Forward Annual Dividend Rate
> Find 3
> Find 4
>
> Count Tag Contents
> 0 Forward Annual Dividend Rate4:
> 1 2.54
> 2
> 3
> .
> .
> The other sheet is using RCHGetWebData and extract 1000 for each line.
> Then I just search the line for the text and that tells me the position to
> where to extract.
>
> Web Page: http://finance.
> Start Pos 50
> Offset
>
> Count Tag Contents
> 1049 web data in here
> 2049 " "
> 3049 " "
>
> I'm sure there's people that could do a better job - but it seems to work
> for me.
>
Fri Feb 22, 2013 1:53 pm (PST) . Posted by:
"Jim" buckleca
Hi Randy,
Thanks - your spreadsheet is even better with the cells going across the spreadsheet. Masterfully done! That's a very helpful tool.
I tried one more sheet for RCHGetHTMLTable:
Web Page: http://finance.
Find Begin Market Cap (intraday)
Begin Direct -1
Find end
End Direct 1
Array Contents:
Market Cap (intraday)5: 71270000
Enterprise Value (Feb 22, 2013)3: 72420000
Trailing P/E (ttm, intraday): 16.34
Forward P/E (fye Dec 31, 2014)1: 13.75
PEG Ratio (5 yr expected)1: 1.54
Price/Sales (ttm): 2.37
Price/Book (mrq): 4.02
Enterprise Value/Revenue (ttm)3: 2.42
Enterprise Value/EBITDA (ttm)6: 9.32
--- In smf_addin@yahoogrou
>
> My table exploration usually varies the row and column, such as:
>
> * * *Web Page:*
> http://finance.
> >Revenue *Find 2:* *Find
> 3:* *Find 4:* *End:*
>
============
> * *Row* *1* *2* *3* *4* *5* *6* *7* *8* *9* *10* *11* *12* *0*
> 29900000 Error Error Error Error Error Error Error Error Error Error Error
> *1* Revenue Per Share (ttm): 43.1 Error Error Error Error Error Error
> Error Error Error Error *2* Qtrly Revenue Growth (yoy): 0.042 Error Error
> Error Error Error Error Error Error Error Error *3* Gross Profit (ttm):
> 14220000 Error Error Error Error Error Error Error Error Error Error
> *4* EBITDA
> (ttm)6: 7770000 Error Error Error Error Error Error Error Error Error Error
> *5* Net Income Avl to Common (ttm): 4440000 Error Error Error Error Error
> Error Error Error Error Error *6* Diluted EPS (ttm): 6.32 Error Error
> Error Error Error Error Error Error Error Error *7* Qtrly Earnings
> Growth (yoy): 0.039 Error Error Error Error Error Error Error Error Error
> Error
> I have done the RCHGetWebData(
> pieces.
>
> I also frequently do a cascading find on the RCHGetWebData(
> well.
>
> On Fri, Feb 22, 2013 at 11:27 AM, Jim wrote:
>
> >
> >
> > Using your very useful "Quick" spreadsheet I added two sheets. One using
> > RCHGetTableCell with inputs for Row # and additional finds. The count is by
> > Cell:
> >
> > Web Page: http://finance.
> > Row #
> > Start String: Financial Highlights
> > Find 2 Forward Annual Dividend Rate
> > Find 3
> > Find 4
> >
> > Count Tag Contents
> > 0 Forward Annual Dividend Rate4:
> > 1 2.54
> > 2
> > 3
> > .
> > .
> > The other sheet is using RCHGetWebData and extract 1000 for each line.
> > Then I just search the line for the text and that tells me the position to
> > where to extract.
> >
> > Web Page: http://finance.
> > Start Pos 50
> > Offset
> >
> > Count Tag Contents
> > 1049 web data in here
> > 2049 " "
> > 3049 " "
> >
> > I'm sure there's people that could do a better job - but it seems to work
> > for me.
> >
>
Fri Feb 22, 2013 4:45 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Yeah. I sometimes do that when I'm not sure what parameters to use. It's
easier than having to redo the array-entered formula every time.
Added as a new worksheet.
On Fri, Feb 22, 2013 at 2:53 PM, Jim buckleca@yahoo.com > wrote:
> Thanks - your spreadsheet is even better with the cells going across the
> spreadsheet. Masterfully done! That's a very helpful tool.
>
> I tried one more sheet for RCHGetHTMLTable:
>
> Web Page: http://finance.yahoo.com/q/ks?s=MMM
>
> Find Begin Market Cap (intraday)
> Begin Direct -1
> Find end
> End Direct 1
>
> Array Contents:
> Market Cap (intraday)5: 71270000
> Enterprise Value (Feb 22, 2013)3: 72420000
> Trailing P/E (ttm, intraday): 16.34
> Forward P/E (fye Dec 31, 2014)1: 13.75
> PEG Ratio (5 yr expected)1: 1.54
> Price/Sales (ttm): 2.37
> Price/Book (mrq): 4.02
> Enterprise Value/Revenue (ttm)3: 2.42
> Enterprise Value/EBITDA (ttm)6: 9.32
>
easier than having to redo the array-entered formula every time.
Added as a new worksheet.
On Fri, Feb 22, 2013 at 2:53 PM, Jim buckleca@yahoo.
> Thanks - your spreadsheet is even better with the cells going across the
> spreadsheet. Masterfully done! That's a very helpful tool.
>
> I tried one more sheet for RCHGetHTMLTable:
>
> Web Page: http://finance.
>
> Find Begin Market Cap (intraday)
> Begin Direct -1
> Find end
> End Direct 1
>
> Array Contents:
> Market Cap (intraday)5: 71270000
> Enterprise Value (Feb 22, 2013)3: 72420000
> Trailing P/E (ttm, intraday): 16.34
> Forward P/E (fye Dec 31, 2014)1: 13.75
> PEG Ratio (5 yr expected)1: 1.54
> Price/Sales (ttm): 2.37
> Price/Book (mrq): 4.02
> Enterprise Value/Revenue (ttm)3: 2.42
> Enterprise Value/EBITDA (ttm)6: 9.32
>
Fri Feb 22, 2013 12:44 pm (PST) . Posted by:
"mknu@juno.com" markknutson2005
Thanks Randy that file is working for me. I had downloaded that other spreadsheet sometime ago. I don't remember where I got it.
__________________________________________________________
How to Sleep Like a Rock
Obey this one natural trick to fall asleep and stay asleep all night.
http://thirdpartyoffers.juno.com/TGL3131/5127d8ab7a5e758ab3db9st01vuc
____________
How to Sleep Like a Rock
Obey this one natural trick to fall asleep and stay asleep all night.
http://thirdpartyof
Hello,
This email message is a notification to let you know that
a file has been uploaded to the Files area of the smf_addin
group.
File : /Templates and Examples/smfGetTagC
Uploaded by : rharmelink rharmelink@gmail.
Description : Several examples of quick processes I sometimes use to explore what might be on the web page and within the source code of the web page, using the smfGetTagContent(
You can access this file at the URL:
http://groups.
To learn more about file sharing for your group, please visit:
http://help.
Regards,
rharmelink rharmelink@gmail.
Fri Feb 22, 2013 8:57 pm (PST) . Posted by:
"almondtree" almondtree
--- In smf_addin@yahoogrou
I was in a major tiff about compile error I have had to redo everything, I still don't trust .xla 2-12-2013. But I do apologize for shouting. I think my over use of ASAP Utilities did me in; copying formulas from sheet to sheet caused a bug to zap me.
Randy thank you for the great work you do for us mortals. Thank You!
Tidak ada komentar:
Posting Komentar