7 New Messages
Digest #2462
Messages
Tue Jan 8, 2013 2:55 pm (PST) . Posted by:
"Steven" stevenletzer
The MSRB has a search program for municipal bond trades at:
http://emma.msrb.org/MarketActivity/RecentTrades.aspx
On the right side of the table, above the table headers is a search button to narrow the search. If I select state, MI in this case, then select SEARCH, a table will appear with all of the reported trades in MI as of the close of business. The webaddress remains as before: http://emma.msrb.org/MarketActivity/RecentTrades.
Each record in the table has two hyper links, (1) for CUSIP and (2) for description. I have tried to use an Excel web filter to capture the table, but the records change to something other than what I want and the CUSIP is left blank.
Depending on the trading day, more than a thousand trades could be reported.
Any ideas of how to bring all of the table records into Excel?
http://emma.
On the right side of the table, above the table headers is a search button to narrow the search. If I select state, MI in this case, then select SEARCH, a table will appear with all of the reported trades in MI as of the close of business. The webaddress remains as before: http://emma.
Each record in the table has two hyper links, (1) for CUSIP and (2) for description. I have tried to use an Excel web filter to capture the table, but the records change to something other than what I want and the CUSIP is left blank.
Depending on the trading day, more than a thousand trades could be reported.
Any ideas of how to bring all of the table records into Excel?
Tue Jan 8, 2013 3:27 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
The add-in would need a bookmarkable URL, so if the URL doesn't change when
you do a search process, the data won't be available to the add-in. It's
been a constant issue with ASP web pages.
In any case, as you've found out, the CUSIP is not data in the table (or on
any of their web pages) -- they generate an image for the two links, so the
raw CUSIP value is never displayed as text.
For example, the image for the first CUSIP is generated using:
../ImageGenerator.ashx?cusip9=AE634423F984CDE6C48F1AF11AE28A508&rowNum=1
So, this is the image it generates:
http://emma.msrb.org/ImageGenerator.ashx?cusip9=AE634423F984CDE6C48F1AF11AE28A508&rowNum=1
On Tue, Jan 8, 2013 at 3:55 PM, Steven stevenletzer@yahoo.com > wrote:
> The MSRB has a search program for municipal bond trades at:
> http://emma.msrb.org/MarketActivity/RecentTrades.aspx
>
> On the right side of the table, above the table headers is a search button
> to narrow the search. If I select state, MI in this case, then select
> SEARCH, a table will appear with all of the reported trades in MI as of the
> close of business. The webaddress remains as before:
> http://emma.msrb.org/MarketActivity/RecentTrades.
>
> Each record in the table has two hyper links, (1) for CUSIP and (2) for
> description. I have tried to use an Excel web filter to capture the table,
> but the records change to something other than what I want and the CUSIP is
> left blank.
>
> Depending on the trading day, more than a thousand trades could be
> reported.
>
> Any ideas of how to bring all of the table records into Excel?
>
you do a search process, the data won't be available to the add-in. It's
been a constant issue with ASP web pages.
In any case, as you've found out, the CUSIP is not data in the table (or on
any of their web pages) -- they generate an image for the two links, so the
raw CUSIP value is never displayed as text.
For example, the image for the first CUSIP is generated using:
../ImageGenerator.
So, this is the image it generates:
http://emma.
On Tue, Jan 8, 2013 at 3:55 PM, Steven stevenletzer@
> The MSRB has a search program for municipal bond trades at:
> http://emma.
>
> On the right side of the table, above the table headers is a search button
> to narrow the search. If I select state, MI in this case, then select
> SEARCH, a table will appear with all of the reported trades in MI as of the
> close of business. The webaddress remains as before:
> http://emma.
>
> Each record in the table has two hyper links, (1) for CUSIP and (2) for
> description. I have tried to use an Excel web filter to capture the table,
> but the records change to something other than what I want and the CUSIP is
> left blank.
>
> Depending on the trading day, more than a thousand trades could be
> reported.
>
> Any ideas of how to bring all of the table records into Excel?
>
Tue Jan 8, 2013 8:26 pm (PST) . Posted by:
"orpalms44" orpalms44
I am successfully using the RCHGetYahooHistory function. Now I'm trying to use the RCHGetYahooQuotes function. I simply want to get a stock quote price for a single stock and put it in a cell.
So as an example, I tried using the formula
=RCHGetYahooQuotes("IBM", k, , ,)
The cell returns a blank value. I tried using Ctrl-Shift-Enter to enter as an array - which I believe shouldn't be necessary in this case - but that doesn't work either.
This is so simple, but I can't figure out why it is not working.
Thanks so much for your help.
So as an example, I tried using the formula
=RCHGetYahooQuotes(
The cell returns a blank value. I tried using Ctrl-Shift-Enter to enter as an array - which I believe shouldn'
This is so simple, but I can't figure out why it is not working.
Thanks so much for your help.
Tue Jan 8, 2013 8:31 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
The second parameter needs to be a string, with the data codes of the
various items you want concatenated to each other. As it is, you're passing
it a range name of "k". Try:
=RCHGetYahooQuotes("IBM", "k")
But data code "k" is the 52-week high. If you want the last traded price:
=RCHGetYahooQuotes("IBM", "l1")
On Tue, Jan 8, 2013 at 9:26 PM, orpalms44 orpalms44@yahoo.com > wrote:
> I am successfully using the RCHGetYahooHistory function. Now I'm trying
> to use the RCHGetYahooQuotes function. I simply want to get a stock quote
> price for a single stock and put it in a cell.
>
> So as an example, I tried using the formula
> =RCHGetYahooQuotes("IBM", k, , ,)
>
> The cell returns a blank value. I tried using Ctrl-Shift-Enter to enter
> as an array - which I believe shouldn't be necessary in this case - but
> that doesn't work either.
>
> This is so simple, but I can't figure out why it is not working.
>
various items you want concatenated to each other. As it is, you're passing
it a range name of "k"
=RCHGetYahooQuotes(
But data code "k" is the 52-week high. If you want the last traded price:
=RCHGetYahooQuotes(
On Tue, Jan 8, 2013 at 9:26 PM, orpalms44 orpalms44@yahoo.
> I am successfully using the RCHGetYahooHistory function. Now I'm trying
> to use the RCHGetYahooQuotes function. I simply want to get a stock quote
> price for a single stock and put it in a cell.
>
> So as an example, I tried using the formula
> =RCHGetYahooQuotes(
>
> The cell returns a blank value. I tried using Ctrl-Shift-Enter to enter
> as an array - which I believe shouldn'
> that doesn't work either.
>
> This is so simple, but I can't figure out why it is not working.
>
Wed Jan 9, 2013 3:43 am (PST) . Posted by:
"orpalms44" orpalms44
That's it!. Thank you so much!!!
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> The second parameter needs to be a string, with the data codes of the
> various items you want concatenated to each other. As it is, you're passing
> it a range name of "k". Try:
>
> =RCHGetYahooQuotes("IBM", "k")
>
> But data code "k" is the 52-week high. If you want the last traded price:
>
> =RCHGetYahooQuotes("IBM", "l1")
>
> On Tue, Jan 8, 2013 at 9:26 PM, orpalms44 wrote:
>
> > I am successfully using the RCHGetYahooHistory function. Now I'm trying
> > to use the RCHGetYahooQuotes function. I simply want to get a stock quote
> > price for a single stock and put it in a cell.
> >
> > So as an example, I tried using the formula
> > =RCHGetYahooQuotes("IBM", k, , ,)
> >
> > The cell returns a blank value. I tried using Ctrl-Shift-Enter to enter
> > as an array - which I believe shouldn't be necessary in this case - but
> > that doesn't work either.
> >
> > This is so simple, but I can't figure out why it is not working.
> >
>
--- In smf_addin@yahoogrou
>
> The second parameter needs to be a string, with the data codes of the
> various items you want concatenated to each other. As it is, you're passing
> it a range name of "k"
>
> =RCHGetYahooQuotes(
>
> But data code "k" is the 52-week high. If you want the last traded price:
>
> =RCHGetYahooQuotes(
>
> On Tue, Jan 8, 2013 at 9:26 PM, orpalms44 wrote:
>
> > I am successfully using the RCHGetYahooHistory function. Now I'm trying
> > to use the RCHGetYahooQuotes function. I simply want to get a stock quote
> > price for a single stock and put it in a cell.
> >
> > So as an example, I tried using the formula
> > =RCHGetYahooQuotes(
> >
> > The cell returns a blank value. I tried using Ctrl-Shift-Enter to enter
> > as an array - which I believe shouldn'
> > that doesn't work either.
> >
> > This is so simple, but I can't figure out why it is not working.
> >
>
Tue Jan 8, 2013 11:09 pm (PST) . Posted by:
"Pete A" option2z
Thanks Randy,
Actually, I just modified the formula you gave me to find the OLDEST date, by looking at the page from which you got it. Right below it is the Latest available date. Minor change to the formula:
Latest Date: =DATEVALUE(smfGetTagContent("http://finance.yahoo.com/q/hp?s= "&$B$8,"option",0,"name=""a""","option selected")&" "&smfStrExtr(RCHGetWebData("http://finance.yahoo.com/q/hp?s= "&$B$8,"id=""endday""",100),"value=""","""")&", "&smfStrExtr(RCHGetWebData("http://finance.yahoo.com/q/hp?s= "&$B$8,"id=""endyear""",100),"value=""",""""))
Oldest Date:
=DATEVALUE(smfGetTagContent("http://finance.yahoo.com/q/hp?s= "&$B$8,"option",0,"name=""a""","option selected")&" "&smfStrExtr(RCHGetWebData("http://finance.yahoo.com/q/hp?s= "&B8,"id=""startday""",100),"value=""","""")&", "&smfStrExtr(RCHGetWebData("http://finance.yahoo.com/q/hp?s= "&B8,"id=""startyear""",100),"value=""",""""))
SEE!? You CAN teach an old dog new tricks; thanks for taking time to do it, arf, arf.
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> For the latest trading date of history, this should work in VBA:
>
> RCHGetYahooHistory("MMM",,,,,,,,"D",0,,,1,1)(1,1)
>
> But right now, that will get you a date earlier than today, so I'm not sure
> it gets you what you want?
>
> You should be able to check if the historical quotes has been updated for
> the day by comparing the above with:
>
> RCHGetYahooQuotes("MMM","d1",,,,1,1)(1,1)
>
> On Mon, Jan 7, 2013 at 10:54 AM, Pete A wrote:
>
> > Thanks Randy. That works for finding the earliest data date available, in
> > a formula. I didn't make it clear that I also need the latest date
> > available. Some stocks have stopped trading, or changed tickers, so when
> > there is a "latest date" earlier than today, more research is merited.
> > I'll convert the formula for earliest date into vba. Thanks! Pete A
> >
>
Actually, I just modified the formula you gave me to find the OLDEST date, by looking at the page from which you got it. Right below it is the Latest available date. Minor change to the formula:
Latest Date: =DATEVALUE(smfGetTa
Oldest Date:
=DATEVALUE(smfGetTa
SEE!? You CAN teach an old dog new tricks; thanks for taking time to do it, arf, arf.
--- In smf_addin@yahoogrou
>
> For the latest trading date of history, this should work in VBA:
>
> RCHGetYahooHistory(
>
> But right now, that will get you a date earlier than today, so I'm not sure
> it gets you what you want?
>
> You should be able to check if the historical quotes has been updated for
> the day by comparing the above with:
>
> RCHGetYahooQuotes(
>
> On Mon, Jan 7, 2013 at 10:54 AM, Pete A wrote:
>
> > Thanks Randy. That works for finding the earliest data date available, in
> > a formula. I didn't make it clear that I also need the latest date
> > available. Some stocks have stopped trading, or changed tickers, so when
> > there is a "latest date" earlier than today, more research is merited.
> > I'll convert the formula for earliest date into vba. Thanks! Pete A
> >
>
Tue Jan 8, 2013 11:46 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I think the ending date you are picking up there will always be the current
date...? For example, right now I get 2013-01-09 using your formula,
although the oldest historical quote is from 2013-01-08. You can pull that
last date from the page with:
=DATEVALUE(RCHGetTableCell("http://finance.yahoo.com/q/hp?s=
"&B8,1,">Date",,,,1))
On Wed, Jan 9, 2013 at 12:09 AM, Pete A optionzz@gmail.com > wrote:
> Thanks Randy,
> Actually, I just modified the formula you gave me to find the OLDEST date,
> by looking at the page from which you got it. Right below it is the Latest
> available date. Minor change to the formula:
> Latest Date: =DATEVALUE(smfGetTagContent("http://finance.yahoo.com/q/hp?s= "&$B$8,"option",0,"name=""a""","option
> selected")&" "&smfStrExtr(RCHGetWebData("http://finance.yahoo.com/q/hp?s= "&$B$8,"id=""endday""",100),"value=""","""")&",
> "&smfStrExtr(RCHGetWebData("http://finance.yahoo.com/q/hp?s=
> "&$B$8,"id=""endyear""",100),"value=""",""""))
> Oldest Date:
> =DATEVALUE(smfGetTagContent("http://finance.yahoo.com/q/hp?s= "&$B$8,"option",0,"name=""a""","option
> selected")&" "&smfStrExtr(RCHGetWebData("http://finance.yahoo.com/q/hp?s= "&B8,"id=""startday""",100),"value=""","""")&",
> "&smfStrExtr(RCHGetWebData("http://finance.yahoo.com/q/hp?s=
> "&B8,"id=""startyear""",100),"value=""",""""))
>
> SEE!? You CAN teach an old dog new tricks; thanks for taking time to do
> it, arf, arf.
>
date...? For example, right now I get 2013-01-09 using your formula,
although the oldest historical quote is from 2013-01-08. You can pull that
last date from the page with:
=DATEVALUE(RCHGetTa
"&B8,1,
On Wed, Jan 9, 2013 at 12:09 AM, Pete A optionzz@gmail.
> Thanks Randy,
> Actually, I just modified the formula you gave me to find the OLDEST date,
> by looking at the page from which you got it. Right below it is the Latest
> available date. Minor change to the formula:
> Latest Date: =DATEVALUE(smfGetTa
> selected"
> "&smfStrExtr(
> "&$B$8,
> Oldest Date:
> =DATEVALUE(smfGetTa
> selected"
> "&smfStrExtr(
> "&B8,"
>
> SEE!? You CAN teach an old dog new tricks; thanks for taking time to do
> it, arf, arf.
>
Tidak ada komentar:
Posting Komentar