Kamis, 08 Agustus 2013

[smf_addin] Digest Number 2727

15 New Messages

Digest #2727
1
FINRA security description by "Steven" stevenletzer
2a
FINRA Bond Description by "Steven" stevenletzer
2b
Re: FINRA Bond Description by "gbullr" gbullr
2c
Re: FINRA Bond Description by "Randy Harmelink" rharmelink
3a
Re: Next EX-Dividend Date by "Kermit W. Prather" kermitpra
3b
Re: Next EX-Dividend Date by "justvotin1" justvotin1

Messages

Thu Aug 8, 2013 6:00 am (PDT) . Posted by:

"Steven" stevenletzer

FINRA.org has changed their web site. I am looking to adapt my formula to capture bond description data. For example:

http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=M5591283926625&symbol=

For the data labled "Moody's Rating"; I entered this formula, where the data is in table form:

RCHGetTableCell("http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=M5591283926625",1,"Moody's Rating")

I tried several different variations, only to receive the ERROR

Any ideas?

If you are able to solve this problem, I am also looking to capture data where the label is above the data. For example:

WEST BLOOMFIELD MICH SCH DIST is below the label "Bond Detail".

Example of other data where the label is above the data.
Coupon Rate
5.000%

Call Date

05/01/2015 -> a line is between the label and the data

Thu Aug 8, 2013 9:09 am (PDT) . Posted by:

"gbullr" gbullr

Just tried to get infor on Smf Bond Pricin.xls (User Forum Section) and it works..

You need to put the bond id in the column that says ID and copy row down.

I am not good enough to code element number off the finra page.


--- In smf_addin@yahoogroups.com, "Steven" <stevenletzer@...> wrote:
>
> FINRA.org has changed their web site. I am looking to adapt my formula to capture bond description data. For example:
>
> http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=M5591283926625&symbol=
>
> For the data labled "Moody&#39;s Rating"; I entered this formula, where the data is in table form:
>
> RCHGetTableCell("http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=M5591283926625",1,"Moody's Rating")
>
> I tried several different variations, only to receive the ERROR
>
> Any ideas?
>
> If you are able to solve this problem, I am also looking to capture data where the label is above the data. For example:
>
> WEST BLOOMFIELD MICH SCH DIST is below the label "Bond Detail".
>
> Example of other data where the label is above the data.
> Coupon Rate
> 5.000%
>
> Call Date
>
> 05/01/2015 -> a line is between the label and the data
>

Thu Aug 8, 2013 2:04 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I get a page not found in my browser for your URLs below. But I suggest you
look at gbullr's workbook.

This thread goes over how that uploaded workbook came about:

http://finance.groups.yahoo.com/group/smf_addin/messages/22998?threaded=1&m=e

On Thu, Aug 8, 2013 at 6:00 AM, Steven <stevenletzer@yahoo.com> wrote:

> FINRA.org has changed their web site. I am looking to adapt my formula to
> capture bond description data. For example:
>
>
> http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=M5591283926625&symbol=
>
> For the data labled "Moody&#39;s Rating"; I entered this formula, where the
> data is in table form:
>
> RCHGetTableCell("
> http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=M5591283926625",1,"Moody's
> Rating")
>
> I tried several different variations, only to receive the ERROR
>
> Any ideas?
>
> If you are able to solve this problem, I am also looking to capture data
> where the label is above the data. For example:
>
> WEST BLOOMFIELD MICH SCH DIST is below the label "Bond Detail".
>
> Example of other data where the label is above the data.
> Coupon Rate
> 5.000%
>
> Call Date
>
> 05/01/2015 -> a line is between the label and the data
>

Thu Aug 8, 2013 8:27 am (PDT) . Posted by:

"Kermit W. Prather" kermitpra

Why not just use the yahoo ex-dividend date or use www.dividata.com upcoming
dividends?

Another useful site is
http://www.dividendstocksonline.com/ex-dividend-date/august/
It is only updated once a month the last week of the prior month. They have
free and fee.

I have checkout several subscription based site and have not found one that
updates more than once a month.
www.dividata.com seems to update more often than the paid sites.

You could open an account with www.dividend.com they claim to have the very
latest ex-div dates.
Again, they only update once a month, last week of prior month.
You can sign up for a 2 week free trial but as I found out you cannot get
access to everything in that 2 weeks.

I have been an Etrade users since they first opened and I still do not trust
their data.
I, also, have TDameritrade and Schwab accounts. None of which provide much
help on Ex-dividend dates.

kermit

-----Original Message-----
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of justvotin1
Sent: Wednesday, August 07, 2013 11:15 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Next EX-Dividend Date

Is there a way to capture that so I don't have to open a web dialog every
time my browser gets cleared? I would like to be able to just transport the
excel file around, and if it meant copying one or two small files to the
same folder that would be great. I don't know the mechanism it uses to
authenticate though and I'm having trouble finding any VBA info about it.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> When I first created it, I got "Error" as well.
>
> Then:
>
> 1. I opened up the EXCEL Web Query dialog, 2. I went to
> https://us.etrade.com/home 3. I entered a ticker symbol in the box 4.
> After the snapshot came up, I cancelled out of the EXCEL Web Query
> dialog.
> 5. I ran smfForceRecalculation to get a fresh copy of the web page
>
> I got back the ex-dividend date. I then changed the ticker symbol, and
> got the ex-dividend date for that ticker symbol.
>
> And, yes, if you haven't gone through the steps above, you will get
> the secure logon page.
>
> BTW -- I'm not an eTrade customer, so I can't log on.
>
> On Tue, Aug 6, 2013 at 12:47 PM, Kermit W. Prather
> <kermitp@...>wrote:
>
> >
> > I just tired the link and I get an Error.****
> >
> > ** **
> >
> > =RCHGetTableCell("
> > https://www.etrade.wallst.com/v1/stocks/snapshot/snapshot.asp?symbol
> > =MMM",
> > 1,">Ex-Dividend Date")****
> >
> > ** **
> >
> > When I post it into a browser it puts up the Etrade secure logon
> > page. ***
> > *
> >
> > I logged onto Etrade and it still failed.
> >
>

------------------------------------

Yahoo! Groups Links

Thu Aug 8, 2013 8:06 pm (PDT) . Posted by:

"justvotin1" justvotin1

If you have the one that worked on 2003, I'd be willing to try and revamp it, but the msxml object is kind of new to me and I've never seen any examples on how to capture a certificate or present it or a cookie to a html request.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I'm sure there is a way to do it by interacting with the IE object.
>
> However, I no longer use VBA to interact with the IE object, because I had
> many such routines that broke going from EXCEL 2003 to EXCEL 2007. With so
> many versions of IE and EXCEL out there, it just became a waste of time to
> work on something that doesn't work with all the permutations of those
> versions that can be out there. Especially since I'd have no way to test
> anything -- I only have my version of IE and my version of EXCEL.
>
> On Wed, Aug 7, 2013 at 8:15 PM, justvotin1 <justvotin1@...> wrote:
>
> > Is there a way to capture that so I don't have to open a web dialog every
> > time my browser gets cleared? I would like to be able to just transport the
> > excel file around, and if it meant copying one or two small files to the
> > same folder that would be great. I don't know the mechanism it uses to
> > authenticate though and I'm having trouble finding any VBA info about it.
> >
>

Thu Aug 8, 2013 1:14 pm (PDT) . Posted by:

"Manish Babla" bablam

Randy:

I am having the problem again with the quarters being repeated, and I have
tried everything: (a) I only go to the U.S. site, (b) I set Internet
Options properly per your instruction to "Every time..." both from within
IE and using Control Panel. One thing I noticed though, no matter how many
times I set it, it reverts back to Automatic from Control Panel.

I love your product, and now have to use it, but this problem is really
putting a brake on it. I am hoping that you are able to help with, either
because you have encountered this before, or maybe you have a creative
suggestion out of this.

Thanks again,

Manish

On Tue, Aug 6, 2013 at 9:26 PM, Randy Harmelink <rharmelink@gmail.com>wrote:

> **
>
>
> Do you have your Internet Options set to always get a fresh copy of the
> web page every time you visit it? It could be that IE is caching the web
> pages, and then the add-in is picking up those cached web pages. Are you
> using the smfForceRecalculation macro to see if getting a fresh copy of the
> web page still has the same behavior? Both of these have items in the LINKS
> area of the Yahoo group...
>
> But if you're routing through an Australian proxy, and seeing the same
> behavior in your browser, I think that's what you're going to see in the
> add-in. Were you not using the proxy originally, before this problem
> started?
>
> Did the "www." address stay at "www."? Or switch to an Australian version
> of the URL?
>
>
> On Tue, Aug 6, 2013 at 5:55 PM, Manish Babla <mbabla@gmail.com> wrote:
>
>>
>> I have some more info on the problem.
>>
>> So, I use a proxy when I update the data on the spreadsheet. In my case,
>> I use Australia. I noticed though when I went to the Australia website for
>> advfn, and tried to get ticker financial info, it gives you the latest five
>> quarters no matter which quarters you select from the drop-down menu on the
>> website, i.e., replicating what my sheet is doing.
>>
>> Now, the interesting part is that I had used CSCO to get financial info
>> earlier, and since it wasn't one of the 12 tickers I had in the original
>> spreadsheet, it worked fine. Now, when I typed in CSCO on the
>> AdvFN Australia website, and then went back to my spreadsheet, CSCO no
>> longer works, i.e., it repeats the quarters like it does for my other 12
>> tickers. So, it seems like the problem has to do with somehow the
>> spreadsheet or macro or ?? storing the connection for the Australia
>> website, so even if I connected locally from the U.S. it still gives me the
>> wrong repeated five quarters.
>>
>> I hope that is clear.
>>
>> Your product is outstanding, Randy, but I need help with this to get it
>> to work. And I am guessing whatever is causing this for me, is likely to
>> repeat itself, unless resolved.
>>
>>
>
>

Thu Aug 8, 2013 1:15 pm (PDT) . Posted by:

"Manish Babla" bablam

P.S.: I also tried the smfforcerelcalculate macro, and that didn't help
either

On Tue, Aug 6, 2013 at 9:47 PM, Manish Babla <mbabla@gmail.com> wrote:

> Amazing, Thanks Randy. You have an excellent product, and absolutely great
> service.
>
> It was the first thing you mentioned. IE caching wasn't set up properly,
> so it was still using the old page data from the Australian website.
> Strange though, now when I connect through Australian proxy, it works
> correctly.
>
> Thanks again.
>
> Manish
>
>
> On Tue, Aug 6, 2013 at 9:26 PM, Randy Harmelink <rharmelink@gmail.com>wrote:
>
>> **
>>
>>
>> Do you have your Internet Options set to always get a fresh copy of the
>> web page every time you visit it? It could be that IE is caching the web
>> pages, and then the add-in is picking up those cached web pages. Are you
>> using the smfForceRecalculation macro to see if getting a fresh copy of the
>> web page still has the same behavior? Both of these have items in the LINKS
>> area of the Yahoo group...
>>
>> But if you're routing through an Australian proxy, and seeing the same
>> behavior in your browser, I think that's what you're going to see in the
>> add-in. Were you not using the proxy originally, before this problem
>> started?
>>
>> Did the "www." address stay at "www."? Or switch to an Australian version
>> of the URL?
>>
>>
>> On Tue, Aug 6, 2013 at 5:55 PM, Manish Babla <mbabla@gmail.com> wrote:
>>
>>>
>>> I have some more info on the problem.
>>>
>>> So, I use a proxy when I update the data on the spreadsheet. In my case,
>>> I use Australia. I noticed though when I went to the Australia website for
>>> advfn, and tried to get ticker financial info, it gives you the latest five
>>> quarters no matter which quarters you select from the drop-down menu on the
>>> website, i.e., replicating what my sheet is doing.
>>>
>>> Now, the interesting part is that I had used CSCO to get financial info
>>> earlier, and since it wasn't one of the 12 tickers I had in the original
>>> spreadsheet, it worked fine. Now, when I typed in CSCO on the
>>> AdvFN Australia website, and then went back to my spreadsheet, CSCO no
>>> longer works, i.e., it repeats the quarters like it does for my other 12
>>> tickers. So, it seems like the problem has to do with somehow the
>>> spreadsheet or macro or ?? storing the connection for the Australia
>>> website, so even if I connected locally from the U.S. it still gives me the
>>> wrong repeated five quarters.
>>>
>>> I hope that is clear.
>>>
>>> Your product is outstanding, Randy, but I need help with this to get it
>>> to work. And I am guessing whatever is causing this for me, is likely to
>>> repeat itself, unless resolved.
>>>
>>>
>>
>>
>
>

Thu Aug 8, 2013 1:50 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I just recently had someone say the change undid itself and went back to
automatically, so you aren't alone. But other than that, I've never heard
of or seen this behavior before.

I did a few Google searches and did find one possible culprit -- do you
have some type of "Internet Accelerator" tool installed? If so, it may be
changing that setting back to "Automatically" in order to speed up your
Internet access.

Other than that, I haven't a clue.

On Thu, Aug 8, 2013 at 1:14 PM, Manish Babla <mbabla@gmail.com> wrote:

>
> I am having the problem again with the quarters being repeated, and I have
> tried everything: (a) I only go to the U.S. site, (b) I set Internet
> Options properly per your instruction to "Every time..." both from within
> IE and using Control Panel. One thing I noticed though, no matter how many
> times I set it, it reverts back to Automatic from Control Panel.
>
> I love your product, and now have to use it, but this problem is really
> putting a brake on it. I am hoping that you are able to help with, either
> because you have encountered this before, or maybe you have a creative
> suggestion out of this.
>
>

Thu Aug 8, 2013 3:04 pm (PDT) . Posted by:

"bablam" bablam

Hi Randy:

Thanks for your response. I re-installed IE, and that allowed the setting to stay at "Every time I visit the webpage" but the problem persists so it seems that that wasn't the culprit.

I am really at my wit's end with this one. FYI- I almost never use IE, so I am not tied to it in any way. Is there any other solution that you can think of esp. given that. i.e., if I made chrome my default browser would that solve it?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I just recently had someone say the change undid itself and went back to
> automatically, so you aren't alone. But other than that, I've never heard
> of or seen this behavior before.
>
> I did a few Google searches and did find one possible culprit -- do you
> have some type of "Internet Accelerator" tool installed? If so, it may be
> changing that setting back to "Automatically" in order to speed up your
> Internet access.
>
> Other than that, I haven't a clue.
>
> On Thu, Aug 8, 2013 at 1:14 PM, Manish Babla <mbabla@...> wrote:
>
> >
> > I am having the problem again with the quarters being repeated, and I have
> > tried everything: (a) I only go to the U.S. site, (b) I set Internet
> > Options properly per your instruction to "Every time..." both from within
> > IE and using Control Panel. One thing I noticed though, no matter how many
> > times I set it, it reverts back to Automatic from Control Panel.
> >
> > I love your product, and now have to use it, but this problem is really
> > putting a brake on it. I am hoping that you are able to help with, either
> > because you have encountered this before, or maybe you have a creative
> > suggestion out of this.
> >
> >
>

Thu Aug 8, 2013 4:32 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

What you use as a browser is irrelevant. The add-in is using the MicroSoft
DLL libraries to access the Internet, which is why it uses IE settings,
cookies, and temporary Internet files.

AHA! I'm now seeing the same results you are, both in the browser and from
the add-in.

It looks to me like AdvFN is rolling out a new URL structure. Where it used
to be:

http://www.advfn.com/p.php?pid=financials&symbol=mmm

...it is now:

http://www.advfn.com/exchanges/NYSE/MMM/financials

However, the drop-down box to go to earlier quarters doesn't work. The
annual appears to be working, but not the quarterly.

On Thu, Aug 8, 2013 at 3:04 PM, bablam <mbabla@gmail.com> wrote:

>
> Thanks for your response. I re-installed IE, and that allowed the setting
> to stay at "Every time I visit the webpage" but the problem persists so it
> seems that that wasn't the culprit.
>
> I am really at my wit's end with this one. FYI- I almost never use IE, so
> I am not tied to it in any way. Is there any other solution that you can
> think of esp. given that. i.e., if I made chrome my default browser would
> that solve it?
>

Thu Aug 8, 2013 4:33 pm (PDT) . Posted by:

"itrade4dough" itrade4dough

I am having the same problem. I have a smfgetADFNelement going back 16 qtrs and it just repeats the current 5 qtrs over and over. I have tried the smfforcerecalculation as well as properly setting the internet options, and closing out excel and reopening, but yet it still gives me the same problem.

Any other ideas???

Thanks in advance

--- In smf_addin@yahoogroups.com, "bablam" <mbabla@...> wrote:
>
> Hi,
>
> I am using a sheet I previously built to analyze another stock, and noticed that unlike before, the sheet returns wrong dates for Qtr. End., and hence all the data from AdvFN is also wrong for those qtrs.
>
> Let me explain,
> Codes 8006-8025 for RCHGetElementNumber refer to Qtr End Dates FQ1-FQ20, which in this case would be sequentially Mar 2013, Dec 2012....
>
> However, using the same codes I used before, for the same sheet, I am getting instead, -- 2013/03, 2012/12, 2012/09, 2012/06, and then it starts with wrong dates, the next one being, 2013/03, 2012/12, 2012/09, 2012/06, 2012/03, and then again 2012/12, 2012/09, 2012/06, repeating itself again and again, while the no. it is referring to are in the correct sequence 8006-8025.
>
> Can you help?
>
> Thanks,
>
> Manish
>

Thu Aug 8, 2013 4:49 pm (PDT) . Posted by:

"bablam" bablam

Okay, well, that's good to know.

In case that continues to not work, as they have changed the url structure, or otherwise, Randy, do you typically update the add-in to reflect that? And is it typically done soon? Just so that I can plan accordingly.

Thanks for all your efforts in making this available.

Manish

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> What you use as a browser is irrelevant. The add-in is using the MicroSoft
> DLL libraries to access the Internet, which is why it uses IE settings,
> cookies, and temporary Internet files.
>
> AHA! I'm now seeing the same results you are, both in the browser and from
> the add-in.
>
> It looks to me like AdvFN is rolling out a new URL structure. Where it used
> to be:
>
> http://www.advfn.com/p.php?pid=financials&symbol=mmm
>
> ...it is now:
>
> http://www.advfn.com/exchanges/NYSE/MMM/financials
>
> However, the drop-down box to go to earlier quarters doesn't work. The
> annual appears to be working, but not the quarterly.
>
> On Thu, Aug 8, 2013 at 3:04 PM, bablam <mbabla@...> wrote:
>
> >
> > Thanks for your response. I re-installed IE, and that allowed the setting
> > to stay at "Every time I visit the webpage" but the problem persists so it
> > seems that that wasn't the culprit.
> >
> > I am really at my wit's end with this one. FYI- I almost never use IE, so
> > I am not tied to it in any way. Is there any other solution that you can
> > think of esp. given that. i.e., if I made chrome my default browser would
> > that solve it?
> >
>

Thu Aug 8, 2013 5:40 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Actually, the smfGetAdvFNElement() function came about because of the last
time they changed things. So, yes, I'm usually amenable to updating things
-- when I can see what is new is stable and working.

At this point, there's nothing to change. It does appear that the site is
correctly redirecting to the new web pages, where the site itself is
working.

However, the need for the exchange in the URL may present an issue.

I recall first seeing this URL structure when someone was asking about
accessing via the "asia" prefix, so they must have rolled it out first
there. Not sure why they didn't notice the quarterlies weren't working.

On Thu, Aug 8, 2013 at 4:49 PM, bablam <mbabla@gmail.com> wrote:

> Okay, well, that's good to know.
>
> In case that continues to not work, as they have changed the url
> structure, or otherwise, Randy, do you typically update the add-in to
> reflect that? And is it typically done soon? Just so that I can plan
> accordingly.
>
> Thanks for all your efforts in making this available.
>

Thu Aug 8, 2013 5:42 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

It's now apparent that this is an AdvFN problem.

Until AdvFN fixes things at the website, the most recent five quarters are
the only ones available, either using a browser or the add-in.

On Thu, Aug 8, 2013 at 3:23 PM, itrade4dough <itrade4dough@yahoo.com> wrote:

> I am having the same problem. I have a smfgetADFNelement going back 16
> qtrs and it just repeats the current 5 qtrs over and over. I have tried the
> smfforcerecalculation as well as properly setting the internet options, and
> closing out excel and reopening, but yet it still gives me the same problem.
>
> Any other ideas???
>
Diposting oleh EXCEL FEED LEARNING di 20.06

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)