8 New Messages
Digest #2828
2b
Re: Getting data for symbols that contain "-" or "." (hyphen or peri by "Randy Harmelink" rharmelink
Messages
Sat Oct 26, 2013 2:20 am (PDT) . Posted by:
boo1712
Hi Guys,
I have been using excel .igy file to login to www.shareinvestror.com for some time. But I think they sort of recently implemented java script login method and .igy method do not work anymore.
I tried using vba to login using the script below:
Sub SiAutoLogin()
Dim a As String
Set ie = CreateObject("InternetExplorer.Application")
On Error GoTo errhdl
With ie
.Visible = True
.Navigate "http://www.shareinvestor.com/sg"
Do Until .ReadyState = 4
DoEvents
Loop
.Document.all.Item("sic_login_username").Value = "****"
.Document.all.Item("sic_login_password").Value = "****"
.Document.all.Item("sic_login_submit").Click
End With
errhdl:
If Err <> 0 Then
Err.Clear
Exit Sub
End If
End Sub
The script works but excel and IE sort of did not share the same login cookie session or something like that. Login via IE does not mean login via excel.
I google for a long time and suspect it may be due to some url redirection.
Anyone of you guys have a solution for the problem?
Thanks a lot for reading my msg.
Regards,
Terry
I have been using excel .igy file to login to www.shareinvestror.
I tried using vba to login using the script below:
Sub SiAutoLogin(
Dim a As String
Set ie = CreateObject(
On Error GoTo errhdl
With ie
.Visible = True
.Navigate "http://www.shareinv
Do Until .ReadyState = 4
DoEvents
Loop
.Document.all.
.Document.all.
.Document.all.
End With
errhdl:
If Err <> 0 Then
Err.Clear
Exit Sub
End If
End Sub
The script works but excel and IE sort of did not share the same login cookie session or something like that. Login via IE does not mean login via excel.
I google for a long time and suspect it may be due to some url redirection.
Anyone of you guys have a solution for the problem?
Thanks a lot for reading my msg.
Regards,
Terry
Sat Oct 26, 2013 8:15 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
What I've found in the past is that that symptom means the IE object being
used by EXCEL and the IE application being used by the browser are two
different versions of IE. Not sure why it happens though.
On Sat, Oct 26, 2013 at 2:20 AM, <boo1712@yahoo.co.uk> wrote:
>
> The script works but excel and IE sort of did not share the same login
> cookie session or something like that. Login via IE does not mean login via
> excel.
>
used by EXCEL and the IE application being used by the browser are two
different versions of IE. Not sure why it happens though.
On Sat, Oct 26, 2013 at 2:20 AM, <boo1712@yahoo.
>
> The script works but excel and IE sort of did not share the same login
> cookie session or something like that. Login via IE does not mean login via
> excel.
>
Sat Oct 26, 2013 7:53 am (PDT) . Posted by:
sjagers
Hey Randy... Thanks for letting us use your SMF add-in. It's great and I use it every day.
If you're every looking for ways to improve it, I have a suggestion if you're interested.
Some stock symbols contain a hyphen ("-") or a period (".") depending on the source website that's used. Brown-Forman is a great example. At Yahoo the symbol is BF-B; at Zacks.com and other websites the symbol is BF.B.
So, depending on the SMF function that I use (and its data source, if I know it), I have to use both stock symbols to get the data that I want. If I use GetYahooQuotes or GetElementNumber that pulls data from Yahoo, I have to use the symbol BF-B. If I use GetElementNumber that uses another data source, I have to use the symbol BF.B.
Is there an easy work-around in your VBA coding to handle both stock symbols... or stock symbols that contain a period (".") or a hyphen ("-)?
There aren't too many symbols like that, but there are a few: BF-B, HUB-B, MOG-A.
Just FYI, if you're interested.
Thanks again for your add-in. We really appreciate your work.
If you're every looking for ways to improve it, I have a suggestion if you're interested.
Some stock symbols contain a hyphen ("-"
So, depending on the SMF function that I use (and its data source, if I know it), I have to use both stock symbols to get the data that I want. If I use GetYahooQuotes or GetElementNumber that pulls data from Yahoo, I have to use the symbol BF-B. If I use GetElementNumber that uses another data source, I have to use the symbol BF.B.
Is there an easy work-around in your VBA coding to handle both stock symbols... or stock symbols that contain a period ("."
There aren't too many symbols like that, but there are a few: BF-B, HUB-B, MOG-A.
Just FYI, if you're interested.
Thanks again for your add-in. We really appreciate your work.
Sat Oct 26, 2013 8:52 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I rarely use unusual ticker symbols, so don't run into the issue.
A long time ago, I wrote a routine that tried to address the issue --
smfConvertYahooTicker(). The RCHGetElementNumber() function automatically
uses that function to convert ticker symbols by Yahoo (establishing it as
the "base" service), based on the "Source" value assigned to the data
element.
A simple example of the function might look like this:
=smfConvertYahooTicker("BRK-A","MSN")
...and would convert the "BRK-A" ticker symbol on Yahoo to the "BRK/A" used
by MSN.
Right now, the only Zacks conversions are for the ".OB" and ".TO" suffixes
used by Yahoo. But I'm not sure Zacks has much coverage on those stocks
anyway. I just added the conversion of the "-" to "." for Zacks.
On Sat, Oct 26, 2013 at 7:53 AM, <sjagers@yahoo.com> wrote:
>
> If you're every looking for ways to improve it, I have a suggestion if
> you're interested.
>
> Some stock symbols contain a hyphen ("-") or a period (".") depending on
> the source website that's used. Brown-Forman is a great example. At Yahoo
> the symbol is BF-B; at Zacks.com and other websites the symbol is BF.B.
>
> So, depending on the SMF function that I use (and its data source, if I
> know it), I have to use both stock symbols to get the data that I want. If
> I use GetYahooQuotes or GetElementNumber that pulls data from Yahoo, I have
> to use the symbol BF-B. If I use GetElementNumber that uses another data
> source, I have to use the symbol BF.B.
>
> Is there an easy work-around in your VBA coding to handle both stock
> symbols... or stock symbols that contain a period (".") or a hyphen ("-)?
>
> There aren't too many symbols like that, but there are a few: BF-B,
> HUB-B, MOG-A.
>
> Just FYI, if you're interested.
>
A long time ago, I wrote a routine that tried to address the issue --
smfConvertYahooTick
uses that function to convert ticker symbols by Yahoo (establishing it as
the "base" service), based on the "Source" value assigned to the data
element.
A simple example of the function might look like this:
=smfConvertYahooTic
...and would convert the "BRK-A" ticker symbol on Yahoo to the "BRK/A" used
by MSN.
Right now, the only Zacks conversions are for the ".OB" and ".TO" suffixes
used by Yahoo. But I'm not sure Zacks has much coverage on those stocks
anyway. I just added the conversion of the "-" to "." for Zacks.
On Sat, Oct 26, 2013 at 7:53 AM, <sjagers@yahoo.
>
> If you're every looking for ways to improve it, I have a suggestion if
> you're interested.
>
> Some stock symbols contain a hyphen ("-"
> the source website that's used. Brown-Forman is a great example. At Yahoo
> the symbol is BF-B; at Zacks.com and other websites the symbol is BF.B.
>
> So, depending on the SMF function that I use (and its data source, if I
> know it), I have to use both stock symbols to get the data that I want. If
> I use GetYahooQuotes or GetElementNumber that pulls data from Yahoo, I have
> to use the symbol BF-B. If I use GetElementNumber that uses another data
> source, I have to use the symbol BF.B.
>
> Is there an easy work-around in your VBA coding to handle both stock
> symbols... or stock symbols that contain a period ("."
>
> There aren't too many symbols like that, but there are a few: BF-B,
> HUB-B, MOG-A.
>
> Just FYI, if you're interested.
>
Sat Oct 26, 2013 11:16 am (PDT) . Posted by:
"c1sander" c1sander
Randy,
I'm glad I asked. Thanks for the reply.
Craig
--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Yahoo posts it by ticker on their main quotes page. So try:
>
> =DATEVALUE(RCHGetTableCell("http://finance.yahoo.com/q?s="&B3,1,"Next
> Earnings Date"))
>
> Also, FinViz (check the LINKS area of the group to see how you can get
> multiple tickers in one Internet access):
>
> =RCHGetTableCell("http://finviz.com/quote.ashx?t="&B4,1,">Earnings")
>
> Or from Zacks:
>
> =DATEVALUE(RCHGetTableCell("http://www.zacks.com/stock/quote/"&B5,1,"Next
> Earnings Report Date"))
>
> On Thu, Oct 24, 2013 at 8:11 PM, Craig <craig.sander@...> wrote:
>
> >
> > There may be better ways to do this, but I found a way to look up the date
> > of earnings announcements for a given ticker. The ticker should be entered
> > in cell A1. The result is in text format, but with a little more work can
> > be converted to date format.****
> >
> > ** **
> >
> > As an example, for Starbucks, the URL is
> > http://biz.yahoo.com/research/earncal/s/sbux.html (“s†is the first
> > letter of the ticker.) For IBM you would use
> > http://biz.yahoo.com/research/earncal/i/ibm.html .****
> >
> > ** **
> >
> > I’d be interested in other ways or other sites that can be used to get
> > this data.****
> >
> > ** **
> >
> > Craig****
> >
> > -----------------****
> >
> > =IFERROR(RIGHT(RCHGetTableCell("http://biz.yahoo.com/research/earncal/"&LEFT(A1,1)&"/"&A1&";.html",1,"Week"),
> > LEN(RCHGetTableCell("http://biz.yahoo.com/research/earncal/
> > "&LEFT(A1,1)&"/"&A1&";.html",1,"Week"))-FIND(",",RCHGetTableCell("
> > http://biz.yahoo.com/research/earncal/
> > "&LEFT(A1,1)&"/"&A1&";.html",1,"Week"))-1),"--")
> >
>
I'm glad I asked. Thanks for the reply.
Craig
--- In smf_addin@yahoogrou
>
> Yahoo posts it by ticker on their main quotes page. So try:
>
> =DATEVALUE(RCHGetTa
> Earnings Date"))
>
> Also, FinViz (check the LINKS area of the group to see how you can get
> multiple tickers in one Internet access):
>
> =RCHGetTableCell(
>
> Or from Zacks:
>
> =DATEVALUE(RCHGetTa
> Earnings Report Date"))
>
> On Thu, Oct 24, 2013 at 8:11 PM, Craig <craig.sander@
>
> >
> > There may be better ways to do this, but I found a way to look up the date
> > of earnings announcements for a given ticker. The ticker should be entered
> > in cell A1. The result is in text format, but with a little more work can
> > be converted to date format.****
> >
> > ** **
> >
> > As an example, for Starbucks, the URL is
> > http://biz.yahoo.
> > letter of the ticker.) For IBM you would use
> > http://biz.yahoo.
> >
> > ** **
> >
> > I’d be interested in other ways or other sites that can be used to get
> > this data.****
> >
> > ** **
> >
> > Craig****
> >
> > ------------
> >
> > =IFERROR(RIGHT(
> > LEN(RCHGetTableCell
> > "&LEFT(
> > http://biz.yahoo.
> > "&LEFT(
> >
>
Sat Oct 26, 2013 12:29 pm (PDT) . Posted by:
"PeteA" option2z
RE "I support users who want to install and use the SMF_addin with Excel
2013. Will this work with that version of Excel?"
Yes it does; been using it in "production" for many months.
Thanks,
Pete A
<http://www.sundognorth.com/TtP-S/>
2013. Will this work with that version of Excel?"
Yes it does; been using it in "production&qu
Thanks,
Pete A
<http://www.sundogno
Sat Oct 26, 2013 12:33 pm (PDT) . Posted by:
"PeteA" option2z
RE: , I can recommend Peter Hoadley's powerful software package, much of
which is options-oriented.
I'd like to second that! I've used his add-in package for many years, and
it is both excellent and continuously improving. It is at the heart of my
excel based trading system. It is also very reasonably priced (I think at
about $130USD). Along with Randy's SMF it makes my programming life
surprisingly easy and quick.
Thanks,
Pete A
<http://www.sundognorth.com/TtP-S/>
which is options-oriented.
I'd like to second that! I've used his add-in package for many years, and
it is both excellent and continuously improving. It is at the heart of my
excel based trading system. It is also very reasonably priced (I think at
about $130USD). Along with Randy's SMF it makes my programming life
surprisingly easy and quick.
Thanks,
Pete A
<http://www.sundogno
Sat Oct 26, 2013 12:46 pm (PDT) . Posted by:
amitc@ymail.com
Hi Randy,
Thanks, will stick with the GetTableCell. Fragile as sometimes the text changes
I noticed the download data (as csv) link now on the quote page. I still did not see it get all the other fields, only till I, no 'R' if opened in excel.
Amit
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:
Fixed, your formula does it:
=RCHGetTableCell("http://finance.yahoo.com/q?s=SPY http://finance.yahoo.com/q?s=SPY",1,">P/E")
I'm not sure what you mean by "fragile". Certainly, it takes more time if you need a few of them.
But RCHGetYahooQuotes() can only return it if Yahoo populates it on the CSV file. Since the value is N/A, they apparently aren't. It used to be easy to get from the ETF browser, but they've overhauled that to the point where it's useless for extraction.
On Sat, Oct 26, 2013 at 12:04 AM, <ac11@... mailto:ac11@...> wrote:
I noticed P\E ratio for SPY is not being returned, while it works for IBM using
=RCHGetYahooQuotes("spy","r") n\a
=RCHGetYahooQuotes("ibm","r") 12.31
The Yahoo finance page shows it as 15 for SPY, http://finance.yahoo.com/q?s=spy http://finance.yahoo.com/q?s=spy
& 12.25 for http://finance.yahoo.com/q?s=ibm&ql=1 http://finance.yahoo.com/q?s=ibm&ql=1
The market is closed, so it is not changing.
Any thoughts on how to get it for SPY or other ETFs?
I can try something like RCHGetTableCell("http://finance.yahoo.com/q?s= http://finance.yahoo.com/q?s="SPY",1,">P/E")
but that is too fragile.
Thanks, will stick with the GetTableCell. Fragile as sometimes the text changes
I noticed the download data (as csv) link now on the quote page. I still did not see it get all the other fields, only till I, no 'R' if opened in excel.
Amit
---In smf_addin@yahoogrou
Fixed, your formula does it:
=RCHGetTableCell(
I'm not sure what you mean by "fragile"
But RCHGetYahooQuotes(
On Sat, Oct 26, 2013 at 12:04 AM, <ac11@... mailto:ac11@
I noticed P\E ratio for SPY is not being returned, while it works for IBM using
=RCHGetYahooQuotes(
=RCHGetYahooQuotes(
The Yahoo finance page shows it as 15 for SPY, http://finance.
& 12.25 for http://finance.
The market is closed, so it is not changing.
Any thoughts on how to get it for SPY or other ETFs?
I can try something like RCHGetTableCell(
but that is too fragile.
Tidak ada komentar:
Posting Komentar