14 New Messages
Digest #2497
Messages
Tue Feb 5, 2013 8:41 am (PST) . Posted by:
"bflauaus" bflauaus
Has anyone been able to successfully get real-time quotes from Yahoo using the IE login? I am logged into/subscribed to Yahoo real-time(verified by quotes updating real-time in IE), but the delayed quotes are not showing up in the add-in when I do a RCH... update.
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> The ECN items are obsolete, at least as far as I know.
>
> However, it's my understanding that if you create the security cookies
> using the EXCEL Web Query dialog or IE, then the RCHGetYahooQuotes()
> function returns real-time quotes for the normal data code (e.g. "l1" for
> last price).
>
> On Mon, Feb 4, 2013 at 10:38 PM, jack.baron41 wrote:
>
> > I subscribe to Yahoo's Real Time Quotes and I would like to take advantage
> > of the service using the SMF Add_In. I have installed SMF in Excel 2003,
> > Windows XP pro. The Add-In works fine, and I did find mention of the ECNs
> > in the Yahoo Codes used for column headings, but I found no mention of how
> > to set the update frequency (1 sec, 10 sec, 30 sec, ...). I installed SMF
> > after the market closed, so I haven't seen it in action yet, but the static
> > condition looks okay.
> >
>
--- In smf_addin@yahoogrou
>
> The ECN items are obsolete, at least as far as I know.
>
> However, it's my understanding that if you create the security cookies
> using the EXCEL Web Query dialog or IE, then the RCHGetYahooQuotes(
> function returns real-time quotes for the normal data code (e.g. "l1" for
> last price).
>
> On Mon, Feb 4, 2013 at 10:38 PM, jack.baron41 wrote:
>
> > I subscribe to Yahoo's Real Time Quotes and I would like to take advantage
> > of the service using the SMF Add_In. I have installed SMF in Excel 2003,
> > Windows XP pro. The Add-In works fine, and I did find mention of the ECNs
> > in the Yahoo Codes used for column headings, but I found no mention of how
> > to set the update frequency (1 sec, 10 sec, 30 sec, ...). I installed SMF
> > after the market closed, so I haven't seen it in action yet, but the static
> > condition looks okay.
> >
>
Tue Feb 5, 2013 8:59 am (PST) . Posted by:
"David Van Aken" tambourman2001
check:
control panel -> internet options -> browsing history -> settings
make sure that the check for new versions of stored pages is "Every time I
visit the webpage"
-dave
On Tue, Feb 5, 2013 at 7:51 AM, bflauaus bflauaus@swbell.net > wrote:
> Has anyone been able to successfully get real-time quotes from Yahoo using
> the IE login? I am logged into/subscribed to Yahoo real-time(verified by
> quotes updating real-time in IE), but the delayed quotes are not showing up
> in the add-in when I do a RCH... update.
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
> >
> > The ECN items are obsolete, at least as far as I know.
> >
> > However, it's my understanding that if you create the security cookies
> > using the EXCEL Web Query dialog or IE, then the RCHGetYahooQuotes()
> > function returns real-time quotes for the normal data code (e.g. "l1" for
> > last price).
> >
> > On Mon, Feb 4, 2013 at 10:38 PM, jack.baron41 wrote:
> >
> > > I subscribe to Yahoo's Real Time Quotes and I would like to take
> advantage
> > > of the service using the SMF Add_In. I have installed SMF in Excel
> 2003,
> > > Windows XP pro. The Add-In works fine, and I did find mention of the
> ECNs
> > > in the Yahoo Codes used for column headings, but I found no mention of
> how
> > > to set the update frequency (1 sec, 10 sec, 30 sec, ...). I installed
> SMF
> > > after the market closed, so I haven't seen it in action yet, but the
> static
> > > condition looks okay.
> > >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
control panel -> internet options -> browsing history -> settings
make sure that the check for new versions of stored pages is "Every time I
visit the webpage"
-dave
On Tue, Feb 5, 2013 at 7:51 AM, bflauaus bflauaus@swbell.
> Has anyone been able to successfully get real-time quotes from Yahoo using
> the IE login? I am logged into/subscribed to Yahoo real-time(verified by
> quotes updating real-time in IE), but the delayed quotes are not showing up
> in the add-in when I do a RCH... update.
>
> --- In smf_addin@yahoogrou
> >
> > The ECN items are obsolete, at least as far as I know.
> >
> > However, it's my understanding that if you create the security cookies
> > using the EXCEL Web Query dialog or IE, then the RCHGetYahooQuotes(
> > function returns real-time quotes for the normal data code (e.g. "l1" for
> > last price).
> >
> > On Mon, Feb 4, 2013 at 10:38 PM, jack.baron41 wrote:
> >
> > > I subscribe to Yahoo's Real Time Quotes and I would like to take
> advantage
> > > of the service using the SMF Add_In. I have installed SMF in Excel
> 2003,
> > > Windows XP pro. The Add-In works fine, and I did find mention of the
> ECNs
> > > in the Yahoo Codes used for column headings, but I found no mention of
> how
> > > to set the update frequency (1 sec, 10 sec, 30 sec, ...). I installed
> SMF
> > > after the market closed, so I haven't seen it in action yet, but the
> static
> > > condition looks okay.
> > >
> >
>
>
>
>
> ------------
>
> Yahoo! Groups Links
>
>
>
>
Tue Feb 5, 2013 9:02 am (PST) . Posted by:
"Randy Harmelink" rharmelink
In addition to David's suggestion -- sometimes the version of IE that EXCEL
uses is not the same version as the browser, so they may use different
cookies and temporary Internet files. That's why I say it's preferable to
log in with the EXCEL Web Query process, since that is the IE object used
by EXCEL.
On Tue, Feb 5, 2013 at 8:51 AM, bflauaus bflauaus@swbell.net > wrote:
> Has anyone been able to successfully get real-time quotes from Yahoo using
> the IE login? I am logged into/subscribed to Yahoo real-time(verified by
> quotes updating real-time in IE), but the delayed quotes are not showing up
> in the add-in when I do a RCH... update.
>
uses is not the same version as the browser, so they may use different
cookies and temporary Internet files. That's why I say it's preferable to
log in with the EXCEL Web Query process, since that is the IE object used
by EXCEL.
On Tue, Feb 5, 2013 at 8:51 AM, bflauaus bflauaus@swbell.
> Has anyone been able to successfully get real-time quotes from Yahoo using
> the IE login? I am logged into/subscribed to Yahoo real-time(verified by
> quotes updating real-time in IE), but the delayed quotes are not showing up
> in the add-in when I do a RCH... update.
>
Tue Feb 5, 2013 8:57 am (PST) . Posted by:
"Randy Harmelink" rharmelink
I don't understand your issue of "I can not look inside de html code with a
code that contains the name of the company".
I also don't understand your smfStrExtr() parameter of "title", since that
gets you far more than the image URL.
And you don't need such a long search string for RCHGetWebData().
Or the need for the SUBSTITUTE() function.
This works fine for me:
=RCHCreateComment("http://www.4-traders.com "&smfStrExtr(RCHGetWebData("
http://www.4-traders.com/MARFRIG-ALIMENTOS-SA-9059879/consensus/
",">Evolution",1000),"src=""",""""),99)
On Tue, Feb 5, 2013 at 5:20 AM, cesarcrivelli cesarcrivelli@yahoo.com >wrote:
>
> Randy,
>
> Regarding the chart issue that you helped me, there is one that I am
> trying to retreive, but so far nothing...
>
> The web page is
>
> http://www.4-traders.com/MARFRIG-ALIMENTOS-SA-9059879/consensus/
>
> The chart is the last one: "Evolution of the average goal on MARFRIG
> ALIMENTOS SA"
>
> I tried the following code, where 'traders' is the correspondent code for
> the company, found by vlookup, in another sheet.
>
> RCHCreateComment(CONCATENAR("http://www.4-traders.com
> ";SUBSTITUIR(smfStrExtr(RCHGetWebData("http://www.4-traders.com/ "&traders&"/consensus/";"Evolution
> of the average goal on MARFRIG ALIMENTOS
> SA";32767);"src=";"title");CARACT(34);""));99;333;262;1)
>
> The main problem here is that I can not look inside de html code with a
> code that contains the name of the company, since next time, if I need the
> chart for another ticker, the formula will not work.
>
> Any thoughts?
>
> Thanks
>
> Cesar
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
code that contains the name of the company"
I also don't understand your smfStrExtr() parameter of "title"
gets you far more than the image URL.
And you don't need such a long search string for RCHGetWebData(
Or the need for the SUBSTITUTE() function.
This works fine for me:
=RCHCreateComment(
http://www.4-
","
On Tue, Feb 5, 2013 at 5:20 AM, cesarcrivelli cesarcrivelli@
>
> Randy,
>
> Regarding the chart issue that you helped me, there is one that I am
> trying to retreive, but so far nothing...
>
> The web page is
>
> http://www.4-
>
> The chart is the last one: "Evolution of the average goal on MARFRIG
> ALIMENTOS SA"
>
> I tried the following code, where 'traders&#
> the company, found by vlookup, in another sheet.
>
> RCHCreateComment(
> ";SUBSTITUIR(
> of the average goal on MARFRIG ALIMENTOS
> SA"
>
> The main problem here is that I can not look inside de html code with a
> code that contains the name of the company, since next time, if I need the
> chart for another ticker, the formula will not work.
>
> Any thoughts?
>
> Thanks
>
> Cesar
>
>
>
>
>
> ------------
>
> Yahoo! Groups Links
>
>
>
>
Tue Feb 5, 2013 10:21 am (PST) . Posted by:
"rjemery7" rjemery7
How do I obtain UNADJUSTED closing prices for a specific ticker and date?
Tue Feb 5, 2013 1:00 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
To get year-end 2011 closing price, unadjusted:
=RCHGetYahooHistory("MMM",2011,12,30,2011,12,30,,"c",0)
On Tue, Feb 5, 2013 at 11:21 AM, rjemery7 rjemery@gmail.com > wrote:
> How do I obtain UNADJUSTED closing prices for a specific ticker and date?
>
=RCHGetYahooHistory
On Tue, Feb 5, 2013 at 11:21 AM, rjemery7 rjemery@gmail.
> How do I obtain UNADJUSTED closing prices for a specific ticker and date?
>
Tue Feb 5, 2013 6:37 pm (PST) . Posted by:
"bingouser" bingouser
I found another way to do this for historical quotes or anything else that has to go out to the web for each function. Instead of creating a large array, I entered the tickers starting in A2 and created an array in the first row that was 1 row high by whatever rows wide (pasting the function into B2 with Ctrl+Shift+Enter). Then I copied the function from B2, highlighted the rest of the column, and pasted the function into all the cells. This created a bunch of 1-row-high arrays, so the rows can be sorted, added, or deleted.
I tried it with the daily quotes, but since it has to go out to the website for every symbol this way, instead of getting them all at once, it takes a while.
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> What I do is remove the array-entered data from the sort area, or
> modifiable area.
>
> That is, columns A thru K may contain my sortable and modifiable database,
> but columns M thru Z may contain my RCHGetYahooQuotes() "retrieval"
> columns. Then, if I want column B to be a particular column from the M thru
> Z area, I would just put a formula of =N12 in cell B12. Using range names,
> you can even make this self-documenting, so that instead of =N12, you have
> =LastTradedPrice.
>
> This allows the data in columns A thru K to be sorted. When the column with
> the ticker symbols changes the rows that the ticker symbols are in, the
> RCHGetYahooQuotes() function recalculates for the new sort order. Since it
> is array-entered, the update is quick.
>
> Then, instead of deleting rows, you just delete ranges. Just have the
> RCHGetYahooQuotes() in columns M thru Z cover 200 rows of input data, and
> then you don't need to re-enter it when you add additional ticker symbols
> (unless you go over 200 of them). For example:
>
> =RCHGetYahooQuotes(OFFSET($B$2,1,0,200,1),"snl1d1")
>
> ...where cell $B$2 is the header cell of the column that contains the
> ticker cells. If you put that formula into another worksheet, you could
> even delete rows on your "working" worksheet, because row deletions don't
> affect how that OFFSET() function works.
>
> On Fri, Jan 11, 2013 at 1:54 PM, calhandon wrote:
>
> > Howdy Randy and Everyone,
> >
> > In Excel 2010, I was wondering if there is a good way to create a
> > worksheet that includes plans for future row and/or column
> > insertions/deletions -- into/from the middle of an array-entered-formula
> > range of cells?
> >
> > The only way I've figured out so far, is to leave an empty row and column
> > at the end of the row or column range. This may then be copied and pasted,
> > but then the array-entered formulas all need to be manually changed to
> > reflect the additional row/column.
> >
> > My current experimental worksheet contains 7 groups of columns and 2
> > groups of rows, that all contain array-entered formulas. The number of
> > both groups will increase quite a bit by the time I'm done.
> >
> > The column groupings are because I want non-downloaded data included in
> > these groups. The row groups are for individual portfolio accounts and
> > investment types.
> >
> > As an example, the first few column groups contain:
> > * Local
> > - Manually-entered Symbol,
> >
> > * smfGetTagContent to get the full Name,
> >
> > * Local
> > - Manually-entered Shares,
> > - Manually-entered Cost,
> > - Locally calculated Total Basis,
> >
> > * RCHGetYahooQuotes formula to obtain:
> > - Prev Close,
> > - Open,
> > - Last Trade,
> > - $Chg,
> > - %Chg,
> > - Vol,
> > - Avg Vol,
> > - 52-Wk Low,
> > - 52-Wk High
> >
> > * Local:
> > - 52-Wk Range,
> > - % From High,
> > - % From Low,
> > - 10-Day Trend (Sparkline chart)
> >
> > * then more RCHGetYahooQuotes
> >
> > The first two row groups are two IRA accounts, sub-grouped via Excel.
> >
> > Thus, if I end up with something like 15 column groups, and 20 row groups,
> > I would have to manually change up to 300 array-entered formula groups (big
> > frown). Thus my question above. And splitting all of this into individual
> > sheets and books doesn't get around the problem. It just moves it to a
> > different level.
> >
> > I am able to write VBA code, but am still learning it, even though it
> > looks like learning VBA for Excel will never stop (lol).
> >
> > Am also experimenting with writing a Windows Desktop app in Visual Studio
> > 2012 Pro, using C# and either VS Lightswitch (SQL Server), or SQLite via
> > their Managed code fork. Excel and VBA is much simpler, but it's
> > limitations on insert/delete within array-entered formula ranges may be a
> > deal-breaker.
> >
> > Thank you all, in advance, for any and all suggestions.
> >
>
I tried it with the daily quotes, but since it has to go out to the website for every symbol this way, instead of getting them all at once, it takes a while.
--- In smf_addin@yahoogrou
>
> What I do is remove the array-entered data from the sort area, or
> modifiable area.
>
> That is, columns A thru K may contain my sortable and modifiable database,
> but columns M thru Z may contain my RCHGetYahooQuotes(
> columns. Then, if I want column B to be a particular column from the M thru
> Z area, I would just put a formula of =N12 in cell B12. Using range names,
> you can even make this self-documenting, so that instead of =N12, you have
> =LastTradedPrice.
>
> This allows the data in columns A thru K to be sorted. When the column with
> the ticker symbols changes the rows that the ticker symbols are in, the
> RCHGetYahooQuotes(
> is array-entered, the update is quick.
>
> Then, instead of deleting rows, you just delete ranges. Just have the
> RCHGetYahooQuotes(
> then you don't need to re-enter it when you add additional ticker symbols
> (unless you go over 200 of them). For example:
>
> =RCHGetYahooQuotes(
>
> ...where cell $B$2 is the header cell of the column that contains the
> ticker cells. If you put that formula into another worksheet, you could
> even delete rows on your "working" worksheet, because row deletions don't
> affect how that OFFSET() function works.
>
> On Fri, Jan 11, 2013 at 1:54 PM, calhandon wrote:
>
> > Howdy Randy and Everyone,
> >
> > In Excel 2010, I was wondering if there is a good way to create a
> > worksheet that includes plans for future row and/or column
> > insertions/deletion
> > range of cells?
> >
> > The only way I've figured out so far, is to leave an empty row and column
> > at the end of the row or column range. This may then be copied and pasted,
> > but then the array-entered formulas all need to be manually changed to
> > reflect the additional row/column.
> >
> > My current experimental worksheet contains 7 groups of columns and 2
> > groups of rows, that all contain array-entered formulas. The number of
> > both groups will increase quite a bit by the time I'm done.
> >
> > The column groupings are because I want non-downloaded data included in
> > these groups. The row groups are for individual portfolio accounts and
> > investment types.
> >
> > As an example, the first few column groups contain:
> > * Local
> > - Manually-entered Symbol,
> >
> > * smfGetTagContent to get the full Name,
> >
> > * Local
> > - Manually-entered Shares,
> > - Manually-entered Cost,
> > - Locally calculated Total Basis,
> >
> > * RCHGetYahooQuotes formula to obtain:
> > - Prev Close,
> > - Open,
> > - Last Trade,
> > - $Chg,
> > - %Chg,
> > - Vol,
> > - Avg Vol,
> > - 52-Wk Low,
> > - 52-Wk High
> >
> > * Local:
> > - 52-Wk Range,
> > - % From High,
> > - % From Low,
> > - 10-Day Trend (Sparkline chart)
> >
> > * then more RCHGetYahooQuotes
> >
> > The first two row groups are two IRA accounts, sub-grouped via Excel.
> >
> > Thus, if I end up with something like 15 column groups, and 20 row groups,
> > I would have to manually change up to 300 array-entered formula groups (big
> > frown). Thus my question above. And splitting all of this into individual
> > sheets and books doesn't get around the problem. It just moves it to a
> > different level.
> >
> > I am able to write VBA code, but am still learning it, even though it
> > looks like learning VBA for Excel will never stop (lol).
> >
> > Am also experimenting with writing a Windows Desktop app in Visual Studio
> > 2012 Pro, using C# and either VS Lightswitch (SQL Server), or SQLite via
> > their Managed code fork. Excel and VBA is much simpler, but it's
> > limitations on insert/delete within array-entered formula ranges may be a
> > deal-breaker.
> >
> > Thank you all, in advance, for any and all suggestions.
> >
>
Tue Feb 5, 2013 11:52 pm (PST) . Posted by:
"T" thomas777crown
This table seems to have stop working
=RCHGetTableCell("http://www.barchart.com/quotes/stocks/AAPL ",1,">Stock Price Quote",">High")
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> Close isn't in a table, but the others are. For example:
>
> =0+smfGetTagContent("http://www.barchart.com/quotes/stocks/AAPL
> ","span",-1,"id=""dtaLast""")
> =RCHGetTableCell("http://www.barchart.com/quotes/stocks/AAPL ",1,">Stock
> Price Quote",">High")
>
> On Wed, Dec 5, 2012 at 7:54 PM, T wrote:
>
> >
> > I'm fairly new to this. I looked up the documentation. I was able to get
> > retrieve some items but I can't seem to retrieve the high, open, low, close
> > and volume with RCHGetTableCell for
> > http://www.barchart.com/quotes/stocks/AAPL
> >
>
=RCHGetTableCell(
--- In smf_addin@yahoogrou
>
> Close isn't in a table, but the others are. For example:
>
> =0+smfGetTagContent
> ","
> =RCHGetTableCell(
> Price Quote",
>
> On Wed, Dec 5, 2012 at 7:54 PM, T wrote:
>
> >
> > I'm fairly new to this. I looked up the documentation. I was able to get
> > retrieve some items but I can't seem to retrieve the high, open, low, close
> > and volume with RCHGetTableCell for
> > http://www.barchart
> >
>
Wed Feb 6, 2013 1:08 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Looks like they changed the format of the web page. Try:
=RCHGetTableCell("http://www.barchart.com/quotes/stocks/AAPL ",1,"View
Help",">High")
On Wed, Feb 6, 2013 at 12:52 AM, T thomas777crown@yahoo.com > wrote:
> This table seems to have stop working
> =RCHGetTableCell("http://www.barchart.com/quotes/stocks/AAPL ",1,">Stock
> Price Quote",">High")
>
=RCHGetTableCell(
Help","
On Wed, Feb 6, 2013 at 12:52 AM, T thomas777crown@
> This table seems to have stop working
> =RCHGetTableCell(
> Price Quote",
>
Tue Feb 5, 2013 11:58 pm (PST) . Posted by:
"T" thomas777crown
Hi,
I have a problem and a question it seems that barchart has changed it's table because I can't pull anything from this table below. Can some help me out and tell me what would I need to fix to pull this info from the table?
=RCHGetTableCell(
Best,
T
Wed Feb 6, 2013 8:16 am (PST) . Posted by:
"Steven" stevenletzer
In ADVFN, under "Search bt Sector", I want to bring in the tables without copy and paste. The URL http://www.advfn.com/p.php?pid=searchbysector&symbol=F,at table at the left of the page titled "Groups", listd the major sector groups.
The first is "Basic Materials", which opens a new table with "Sectors", the first is "Chemicals", at the URL http://www.advfn.com/p.php?pid=searchbysector&sscf=us§or=1&subsector=11
Click on "Chemicals" opens a third table "Sub-Sectors. The URL is http://www.advfn.com/p.php?pid=searchbysector&sscf=us§or=1&subsector=11
Click on the first "Sub-Sector" --> "Chemicals-Major Diversified" opens a table the the symbol and company name. The URL is http://www.advfn.com/p.php?pid=searchbysector&sscf=us§or=1&subsector=11&symbolsearch=110.
How do I set up a spreadsheet to bring the 4 tables in Excel? Idealy, the thirst 3 tables would maintain the hyperlinks to make the spreadsheet respond as the website does.
Any Ideas?
The first is "Basic Materials"
Click on "Chemicals&quo
Click on the first "Sub-Sector&qu
How do I set up a spreadsheet to bring the 4 tables in Excel? Idealy, the thirst 3 tables would maintain the hyperlinks to make the spreadsheet respond as the website does.
Any Ideas?
Wed Feb 6, 2013 8:43 am (PST) . Posted by:
"Randy Harmelink" rharmelink
What you're asking can be done, but it's not a trivial task.
For example, to get sectors, you need a series of formulas such as:
=smfGetTagContent("http://www.advfn.com/p.php?pid=searchbysector
","td",-1,"§or=")
...varying the -1 from 1 to "n" as well, where "n" is the number of
sectors. That would return:
style="text-decoration:none;white-space:nowrap;">BASIC MATERIALS
...from which you'd need to extract both the URL and the description.
Then, using that as your URL, you could get the next level with a series of
formulas such as:
=smfGetTagContent("
http://www.advfn.com/p.php?pid=searchbysector&sscf=us§or=1
","td",-1,"&subsector=")
...with the same variance of the "-1" value and similar extractions of URL
and descriptions.
Then, using the URL from that next level, you could get the next level with
a series of formulas such as:
=smfGetTagContent("
http://www.advfn.com/p.php?pid=searchbysector&sscf=us§or=1&subsector=11 ","td",-1,"&symbolsearch=")
...with the same variance of the "-1" value and similar extractions of URL
and descriptions.
Then, using the URL from that next level, you could get the list of ticker
symbols with something like:
=RCHGetTableCell("
http://www.advfn.com/p.php?pid=searchbysector&sscf=us§or=1&subsector=11&symbolsearch=110 ",1,"Company
Name",,,,1)
...varying that last "1" from 1 to "n".
You could use the values from the first three levels to load Data
Validation tables in EXCEL, so that you can have drop-down boxes to select
choices from, based on tables that you've extracted from the web pages.
On Wed, Feb 6, 2013 at 9:15 AM, Steven stevenletzer@yahoo.com > wrote:
> In ADVFN, under "Search bt Sector", I want to bring in the tables without
> copy and paste. The URL
> http://www.advfn.com/p.php?pid=searchbysector&symbol=F,at table at the
> left of the page titled "Groups", listd the major sector groups.
>
> The first is "Basic Materials", which opens a new table with "Sectors",
> the first is "Chemicals", at the URL
> http://www.advfn.com/p.php?pid=searchbysector&sscf=us§or=1&subsector=11
>
> Click on "Chemicals" opens a third table "Sub-Sectors. The URL is
> http://www.advfn.com/p.php?pid=searchbysector&sscf=us§or=1&subsector=11
>
> Click on the first "Sub-Sector" --> "Chemicals-Major Diversified" opens a
> table the the symbol and company name. The URL is
> http://www.advfn.com/p.php?pid=searchbysector&sscf=us§or=1&subsector=11&symbolsearch=110
> .
>
> How do I set up a spreadsheet to bring the 4 tables in Excel? Idealy, the
> thirst 3 tables would maintain the hyperlinks to make the spreadsheet
> respond as the website does.
>
> Any Ideas?
>
For example, to get sectors, you need a series of formulas such as:
=smfGetTagContent(
","
...varying the -1 from 1 to "n" as well, where "n" is the number of
sectors. That would return:
style="text-
...from which you'd need to extract both the URL and the description.
Then, using that as your URL, you could get the next level with a series of
formulas such as:
=smfGetTagContent(
http://www.advfn.
","
...with the same variance of the "-1" value and similar extractions of URL
and descriptions.
Then, using the URL from that next level, you could get the next level with
a series of formulas such as:
=smfGetTagContent(
http://www.advfn.
...with the same variance of the "-1" value and similar extractions of URL
and descriptions.
Then, using the URL from that next level, you could get the list of ticker
symbols with something like:
=RCHGetTableCell(
http://www.advfn.
Name",,
...varying that last "1" from 1 to "n"
You could use the values from the first three levels to load Data
Validation tables in EXCEL, so that you can have drop-down boxes to select
choices from, based on tables that you've extracted from the web pages.
On Wed, Feb 6, 2013 at 9:15 AM, Steven stevenletzer@
> In ADVFN, under "Search bt Sector"
> copy and paste. The URL
> http://www.advfn.
> left of the page titled "Groups"
>
> The first is "Basic Materials"
> the first is "Chemicals&quo
> http://www.advfn.
>
> Click on "Chemicals&quo
> http://www.advfn.
>
> Click on the first "Sub-Sector&qu
> table the the symbol and company name. The URL is
> http://www.advfn.
> .
>
> How do I set up a spreadsheet to bring the 4 tables in Excel? Idealy, the
> thirst 3 tables would maintain the hyperlinks to make the spreadsheet
> respond as the website does.
>
> Any Ideas?
>
Wed Feb 6, 2013 8:57 am (PST) . Posted by:
"Steven" stevenletzer
Thanks, I agree it is not trivial. Since the groups ans sectors do not frequently change, there is probably not much benefit. I have started a basic web query in Excel. This is time consuming, but for now is sufficient.
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> What you're asking can be done, but it's not a trivial task.
>
> For example, to get sectors, you need a series of formulas such as:
>
> =smfGetTagContent("http://www.advfn.com/p.php?pid=searchbysector
> ","td",-1,"§or=")
>
> ...varying the -1 from 1 to "n" as well, where "n" is the number of
> sectors. That would return:
>
> > style="text-decoration:none;white-space:nowrap;">BASIC MATERIALS
>
> ...from which you'd need to extract both the URL and the description.
>
> Then, using that as your URL, you could get the next level with a series of
> formulas such as:
>
> =smfGetTagContent("
> http://www.advfn.com/p.php?pid=searchbysector&sscf=us §or=1
> ","td",-1,"&subsector=")
>
> ...with the same variance of the "-1" value and similar extractions of URL
> and descriptions.
>
> Then, using the URL from that next level, you could get the next level with
> a series of formulas such as:
>
> =smfGetTagContent("
> http://www.advfn.com/p.php?pid=searchbysector&sscf=us §or=1&subsector=11","td",-1,"&symbolsearch=")
>
>
> ...with the same variance of the "-1" value and similar extractions of URL
> and descriptions.
>
> Then, using the URL from that next level, you could get the list of ticker
> symbols with something like:
>
> =RCHGetTableCell("
> http://www.advfn.com/p.php?pid=searchbysector&sscf=us §or=1&subsector=11&symbolsearch=110",1,"Company
> Name",,,,1)
>
> ...varying that last "1" from 1 to "n".
>
> You could use the values from the first three levels to load Data
> Validation tables in EXCEL, so that you can have drop-down boxes to select
> choices from, based on tables that you've extracted from the web pages.
>
> On Wed, Feb 6, 2013 at 9:15 AM, Steven wrote:
>
> > In ADVFN, under "Search bt Sector", I want to bring in the tables without
> > copy and paste. The URL
> > http://www.advfn.com/p.php?pid=searchbysector&symbol=F,at table at the
> > left of the page titled "Groups", listd the major sector groups.
> >
> > The first is "Basic Materials", which opens a new table with "Sectors",
> > the first is "Chemicals", at the URL
> > http://www.advfn.com/p.php?pid=searchbysector&sscf=us §or=1&subsector=11
> >
> > Click on "Chemicals" opens a third table "Sub-Sectors. The URL is
> > http://www.advfn.com/p.php?pid=searchbysector&sscf=us §or=1&subsector=11
> >
> > Click on the first "Sub-Sector" --> "Chemicals-Major Diversified" opens a
> > table the the symbol and company name. The URL is
> > http://www.advfn.com/p.php?pid=searchbysector&sscf=us §or=1&subsector=11&symbolsearch=110
> > .
> >
> > How do I set up a spreadsheet to bring the 4 tables in Excel? Idealy, the
> > thirst 3 tables would maintain the hyperlinks to make the spreadsheet
> > respond as the website does.
> >
> > Any Ideas?
> >
>
--- In smf_addin@yahoogrou
>
> What you're asking can be done, but it's not a trivial task.
>
> For example, to get sectors, you need a series of formulas such as:
>
> =smfGetTagContent(
> ","
>
> ...varying the -1 from 1 to "n" as well, where "n" is the number of
> sectors. That would return:
>
> > style="
>
> ...from which you'd need to extract both the URL and the description.
>
> Then, using that as your URL, you could get the next level with a series of
> formulas such as:
>
> =smfGetTagContent(
> http://www.advfn.
> ","
>
> ...with the same variance of the "-1" value and similar extractions of URL
> and descriptions.
>
> Then, using the URL from that next level, you could get the next level with
> a series of formulas such as:
>
> =smfGetTagContent(
> http://www.advfn.
>
>
> ...with the same variance of the "-1" value and similar extractions of URL
> and descriptions.
>
> Then, using the URL from that next level, you could get the list of ticker
> symbols with something like:
>
> =RCHGetTableCell(
> http://www.advfn.
> Name",,
>
> ...varying that last "1" from 1 to "n"
>
> You could use the values from the first three levels to load Data
> Validation tables in EXCEL, so that you can have drop-down boxes to select
> choices from, based on tables that you've extracted from the web pages.
>
> On Wed, Feb 6, 2013 at 9:15 AM, Steven wrote:
>
> > In ADVFN, under "Search bt Sector"
> > copy and paste. The URL
> > http://www.advfn.
> > left of the page titled "Groups"
> >
> > The first is "Basic Materials"
> > the first is "Chemicals&quo
> > http://www.advfn.
> >
> > Click on "Chemicals&quo
> > http://www.advfn.
> >
> > Click on the first "Sub-Sector&qu
> > table the the symbol and company name. The URL is
> > http://www.advfn.
> > .
> >
> > How do I set up a spreadsheet to bring the 4 tables in Excel? Idealy, the
> > thirst 3 tables would maintain the hyperlinks to make the spreadsheet
> > respond as the website does.
> >
> > Any Ideas?
> >
>
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/RCHGetTabl
Uploaded by : rharmelink rharmelink@gmail.
Description : A template that retrieves the stocks for a selected industry group from BarChart.com.
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.
Tidak ada komentar:
Posting Komentar