Kamis, 26 September 2013

[smf_addin] Digest Number 2786

15 New Messages

Digest #2786

Messages

Thu Sep 26, 2013 2:04 am (PDT) . Posted by:

jovica.bozic


Great work. Thank you.




Why not use RCHGetHTMLTable with index/match function? Data
retrieval time is much faster?


Didn't have time to finish complete workbook, but with 10yr
financial + 10 quarters and with Income statement


completed (index/match) it takes only 4 seconds to retrieve data.



---In smf_addin@yahoogroups.com, <scasty@...> wrote:


John, thanks for creating that spreadsheet. Any clue why
NASDAQ:ATVI is not working with your spreadsheet? Can't figure it
out. I did notice one thing though-- ATVI is not part of your
webcrawler spreadsheet.


---In smf_addin@yahoogroups.com, <smf_addin@yahoogroups.com>
wrote:


I was impressed with yweinstock&#39;s work but found that ADVFN had
changed their structure again, making the URLs not work. I took
his spreadsheet and updated it to work with the ADVFN's new
structure. I left the URL in one cell so that if they change it
again, it will be easy to update. The resulting spr




dmatu2000's discovery of the "company-" placeholder was a big
help, but it doesn't work to pages with 10 years of financial
data. The site redirects you to the page with the company that
only shows 5 years of data. The only way that I could figure out
to work around that is to use a lookup table in Excel that lists
the ADVFN URL "company name" for each ticker symbol. So you
enter the ticker, the table populates the URL company name and
exchange, then sends the fully formed URL to ADVFN.com. I
populated it with about the top 3000 companies.




The resulting spreadsheet is uploaded to the "Upload by Forum
Members" directory. I hope that you find it helpful.



--- In smf_addin@yahoogroups.com, <david_matuszak@...> wrote:


I was up late last night and made some progress




The info that bushpilote provided helped. However, I am unable to
retrieve anything prior to 2012 for the financial statements. And
I am unable to retrieve anything prior to 2009 for the ratios.





Replying to what bushpilote said here: "In ADVFN's beta version
access to a company's financial data requires both the company
name (the name they have in their database) and ticker along
with the exchange ticker symbol."




After poking around, I've been able to get this path to work:

http://www.advfn.com/common/financial/"&Exchange&&quot;/company-"&Tick&#92;
er&"/full-financials?isfull=1&bsfull=1&cffull=1




Meaning the company name is not required as long as there is a
placeholder.






--- In smf_addin@yahoogroups.com, <david_matuszak@...> wrote:


yweinstock your work is superb.





Did you have any luck with bushpilote&#39;s suggestions? I've been
trying to get it to work but still having issues.



--- In smf_addin@yahoogroups.com, <david_matuszak@...> wrote:


Does anyone have an update on ADVFN? Are they still working on
their layout?



--- In smf_addin@yahoogroups.com, <bushpilote@...> wrote:





yweinstock your great work on the new ADVFN financial template is
not all for not. The reason for the errors is that in both the
annual and quartely data the url's are incorrect.




To pull in the ANNUAL data for microsoft replace in the
RCHGetTableCell formula
"http://www.advfn.com/common/financial/"&Exchange&&quot;/"&TickerADVFN&#92;
&"/financials?btn=annual_reports&mode=company_data&isfull=1&bsful&#92;
l=1&cffull=1&effull=1&vrfull=1&grfull=1&fhfull=1&prfull=1" with "




"http://www.advfn.com/common/financial/NASDAQ/microsoft-MSFT/full&#92;
-financials?isfull=1&bsfull=1&cffull=1"




and for the QUARTELY data replace
"http://www.advfn.com/exchanges/"&Exchange&&quot;/"&Ticker&"/financial&#92;
s?btn=annual_reports&mode=company_data&isfull=1&bsfull=1&cffull=1\
&effull=1&vrfull=1&grfull=1&fhfull=1&prfull=1&pm=Q"

with
"http://www.advfn.com/common/financial/NASDAQ/microsoft-MSFT/full&#92;
-financials?isfull=1&bsfull=1&cffull=1&pm=Q"




.....leave the remaining content of the RCHGetTableCell formula
as is in both cases.




Replacing "microsoft-MSFT" with "intel-INTC" will get you Intel's
financials. For IBM's financials you replace NASDAQ with NYSE and
"microsoft-MSFT" with "ibm-international-business-machines-IBM".




In ADVFN's beta version access to a company's financial data
requires both the company name (the name they have in their
database) and ticker along with the exchange ticker symbol. It
remains to be seen if the need to use the company's name will be
the new norm once they go live with their redesigned website.




Hope this helps.


--- In smf_addin@yahoogroups.com, <yweinsto@...> wrote:


Yeah.... Looks like I spoke too soon.

The spreadsheet returns errors now as the site reverted to the
old structure. Perhaps we caught a glimpse of an unintended
early release. Guess it's back to the waiting game..


--- In smf_addin@yahoogroups.com, <rharmelink@...> wrote:

Exactly. They don't seem to have their act together at all.

Makes me have second thoughts about the quality of the data!

On Wed, Sep 11, 2013 at 10:36 AM, <petekoch@...
<https://mail.google.com/mail/?view=cm&fs=1&tf=1&to=petekoch@...>
> wrote:

It appears that AdvFN is going to be day-to-day for a while.
Consider any changes you make to be temporary.


Thu Sep 26, 2013 3:15 am (PDT) . Posted by:

jovica.bozic

I didn't check everything but what is strange on the first glance
is that Total Operating Expenses could be 0 (talking about AAPL)
for 2008 and earlier, and from 2009 until today is very large
number that doesn't fit at all 100bil. (checked EDGAR and numbers
are wrong).
Also D&A numbers are different if you compare IS & CFS.
Who know what else is incorrect.

If some numbers are so wrong, question is if their data should be
used at all.

LINK :
http://www.advfn.com/common/financial/NASDAQ/apple-computer-inc-A\
APL/full-financials?isfull=1&bsfull=1&cffull=1&grfull=1&prfull=1&\
effull=1&vrfull=1

Thu Sep 26, 2013 3:31 am (PDT) . Posted by:

jovica.bozic


Total Operating Expenses on ADVFN includes Cost of Revenue that's
why those numbers are so high, but that doesn't explain why Total
Operating Expenses is 0 prior to 2009.



---In smf_addin@yahoogroups.com, <smf_addin@yahoogroups.com>
wrote:

I didn't check everything but what is strange on the first glance
is that Total Operating Expenses could be 0 (talking about AAPL)
for 2008 and earlier, and from 2009 until today is very large
number that doesn't fit at all 100bil. (checked EDGAR and numbers
are wrong).
Also D&A numbers are different if you compare IS & CFS.
Who know what else is incorrect.

If some numbers are so wrong, question is if their data should be
used at all.

LINK :
http://www.advfn.com/common/financial/NASDAQ/apple-computer-inc-A\
APL/full-financials?isfull=1&bsfull=1&cffull=1&grfull=1&prfull=1&\
effull=1&vrfull=1

Thu Sep 26, 2013 11:29 am (PDT) . Posted by:

friedman_steve


i noticed some weird stuff on EBAY too.

specifically in the operating expenses. comparing two different
locations from ADVfn, gets different results....not sure why.










using this link that I used to use for ADVfn, with more detail:

http://www.advfn.com/exchanges/NASDAQ/EBAY/financials?btn=annual_\
reports&istart_date=57&mode=quarterly_reports
<http://www.advfn.com/exchanges/NASDAQ/EBAY/financials?btn=annual&#92;
_reports&istart_date=57&mode=quarterly_reports>








or using link from ADVfn workaround spreadsheet

http://www.advfn.com/common/financial/NASDAQ/ebay-EBAY/full-finan\
cials?isfull=1&bsfull=1&cffull=1&grfull=1&prfull=1&effull=1&vrful&#92;
l=1
<http://www.advfn.com/common/financial/NASDAQ/ebay-EBAY/full-fina\
ncials?isfull=1&bsfull=1&cffull=1&grfull=1&prfull=1&effull=1&vrfu\
ll=1>





---In smf_addin@yahoogroups.com, <jovica.bozic@...> wrote:


Total Operating Expenses on ADVFN includes Cost of Revenue that's
why those numbers are so high, but that doesn't explain why Total
Operating Expenses is 0 prior to 2009.



---In smf_addin@yahoogroups.com, <smf_addin@yahoogroups.com>
wrote:

I didn't check everything but what is strange on the first glance
is that Total Operating Expenses could be 0 (talking about AAPL)
for 2008 and earlier, and from 2009 until today is very large
number that doesn't fit at all 100bil. (checked EDGAR and numbers
are wrong).
Also D&A numbers are different if you compare IS & CFS.
Who know what else is incorrect.

If some numbers are so wrong, question is if their data should be
used at all.

LINK :
http://www.advfn.com/common/financial/NASDAQ/apple-computer-inc-A\
APL/full-financials?isfull=1&bsfull=1&cffull=1&grfull=1&prfull=1&\
effull=1&vrfull=1

Thu Sep 26, 2013 8:58 am (PDT) . Posted by:

"wolfiyah" wolfiyah

Hi Randy,

Shares Short - quotes retrieved from Yahoo are not the actual ones from their Key Statistics page. For example, for the Symbol SNE (Sony), they are from the 6/28/13 (prior) & 7/15/13 (actual) instead of the 8/30/13 & 9/13/13.
I was using RCHGetElementNumber(xxx,988) and RCHGetElementNumber(xxx,985) respectively.

Peter W.

Thu Sep 26, 2013 9:12 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Hmmm. I'm getting the numbers from the Key Statistics page -- 6.24M and
7.53M.

It doesn't look like they have the 9/13 numbers yet.

On Thu, Sep 26, 2013 at 8:58 AM, wolfiyah <p.wolfensberger@datacomm.ch>wrote:

>
> Shares Short - quotes retrieved from Yahoo are not the actual ones from
> their Key Statistics page. For example, for the Symbol SNE (Sony), they are
> from the 6/28/13 (prior) & 7/15/13 (actual) instead of the 8/30/13 &
> 9/13/13.
> I was using RCHGetElementNumber(xxx,988) and RCHGetElementNumber(xxx,985)
> respectively.
>

Thu Sep 26, 2013 11:01 am (PDT) . Posted by:

"wolfiyah" wolfiyah

The values you mention would be both the actual and correct ones.
The Problem is, that other ones - as I have stated, are retrieved by this method.

Thu Sep 26, 2013 11:32 am (PDT) . Posted by:

"wolfiyah" wolfiyah

Randy,

You are right. I have found that I would need to force recalculation!!

Thanks for the help and your continued effort to Keep the Add-in up to date.

Peter W.

--- In smf_addin@yahoogroups.com, "wolfiyah"; <p.wolfensberger@...> wrote:
>
> The values you mention would be both the actual and correct ones.
> The Problem is, that other ones - as I have stated, are retrieved by this method.
>

Thu Sep 26, 2013 12:22 pm (PDT) . Posted by:

"wolfiyah" wolfiyah

Randy,

Yahoo is very slow in updating Shares Short.
Would it be possible to get the data from nasdaq.com?

Peter

--- In smf_addin@yahoogroups.com, "wolfiyah"; <p.wolfensberger@...> wrote:
>
> Randy,
>
> You are right. I have found that I would need to force recalculation!!
>
> Thanks for the help and your continued effort to Keep the Add-in up to date.
>
> Peter W.
>
> --- In smf_addin@yahoogroups.com, "wolfiyah"; <p.wolfensberger@> wrote:
> >
> > The values you mention would be both the actual and correct ones.
> > The Problem is, that other ones - as I have stated, are retrieved by this method.
> >
>

Thu Sep 26, 2013 12:33 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I'd be very surprised if nasdaq.com was faster. Are you getting a lot of
them?

In any case:

=RCHGetTableCell("http://www.nasdaq.com/symbol/mmm/short-interest",column,">Avg
Daily Share",,,,row)

Just vary row from 1 to whatever and column from 1 to 4, depending on what
data you want to retrieve.

On Thu, Sep 26, 2013 at 12:21 PM, wolfiyah <p.wolfensberger@datacomm.ch>wrote:

>
> Yahoo is very slow in updating Shares Short.
> Would it be possible to get the data from nasdaq.com?
>
>

Thu Sep 26, 2013 1:14 pm (PDT) . Posted by:

joshi_mandar


Thanks Randy for quick response. That worked! Will the FAZ1
formula work for any non-standard option? OR just selected few
that you have coded into the tool?


---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:

EXCEL already knows it's a string, so the double quotes you're
trying to append back and front are not good. All you need is
just:

=RCHGetTableCell(CONCATENATE("http://finance.yahoo.com/q/op?s=
<http://finance.yahoo.com/q/op?s=> ",A1,"&m=",B1),1,C1)

I prefer to use the concatenation operator (i.e. the ampersand):

=RCHGetTableCell("http://finance.yahoo.com/q/op?s=
<http://finance.yahoo.com/q/op?s=> " & A1 & "&m="&B1,1,C1)

Also, I prefer to use this function:

=smfGetOptionQuotes("FAZ1 1/17 2015 $13 Call","l")

If you have the symbol, expiration date, strike price, and option
type in cells of their own (makes it easy to change):

=smfGetOptionQuotes(E13&TEXT(F13," m/d yyyy ")&G13&" "&H13,"l")

Note, however, if you do need the FAZ1 option, you'll need the
beta version of the add-in from the "Works in Progress" folder
from the FILES area of the group.

Also, there is an entry in the LINKS area of the Yahoo group on
some of the various option functions.

On Wed, Sep 25, 2013 at 2:00 PM, <joshi_mandar@...
<mailto:joshi_mandar@...> > wrote:

All - I have some non-standard options in my portfolio that I
would like to get quotes for. For example: FAZ1150117C00013000
from yahoo quotes.
I want to get the latest quote from Yahoo which I get by using
this formula:
=RCHGetTableCell("http://finance.yahoo.com/q/op?s=FAZ&m=2015-01
<http://finance.yahoo.com/q/op?s=FAZ&m=2015-01>
",1,"FAZ1150117C00013000"). However, I want to avoid hard-coding
within my spreadsheet.
Therefore, I entered FAZ in cell A1, 2015-01 in B1 and
FAZ1150117C00013000 in C1. Then tried to use concatenate
function to get the latest quote. something like
this.=RCHGetTableCell(CONCATENATE("""","http://finance.yahoo.com/\
q/op?s= <http://finance.yahoo.com/q/op?s=>
",A1,"&m=",B1,""""),1,CONCATENATE("""",C1,""""))
It throws an error. Not sure what I am doing wrong. Can anyone
help?

Thu Sep 26, 2013 1:37 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

It should work for any non-standard option, for Yahoo as a data source. If
the rightmost character is numeric, it treats the ticker symbol as a
non-standard option.

For example, "AAPL7" refers to the mini-options for AAPL.

The routine just strips off the numeric portion for determining the URL of
the web page, but still uses the numeric portion when searching for the
option symbol on that web page.

On Thu, Sep 26, 2013 at 1:14 PM, <joshi_mandar@hotmail.com> wrote:

> Will the FAZ1 formula work for any non-standard option? OR just selected
> few that you have coded into the tool?
>

Thu Sep 26, 2013 2:20 pm (PDT) . Posted by:

joshi_mandar


Another related question. As you know AAPL7 mini-options gives
one a right but not obligation to buy/sell 10 shares instead of
standard 100 shares. That's why they are non-standard options.
Likewise FAZ1 gives one a right for 25 shares instead of standard
100 shares. How do I get that multiplication-numbers like 10 or
25? I can't see that information on Yahoo. But I am sure it
exists somewhere. Any idea?

Thu Sep 26, 2013 2:30 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I don't know of a source, sorry.

On Thu, Sep 26, 2013 at 2:20 PM, <joshi_mandar@hotmail.com> wrote:

> Another related question. As you know AAPL7 mini-options gives one a
> right but not obligation to buy/sell 10 shares instead of standard 100
> shares. That's why they are non-standard options. Likewise FAZ1 gives one
> a right for 25 shares instead of standard 100 shares. How do I get that
> multiplication-numbers like 10 or 25? I can't see that information on
> Yahoo. But I am sure it exists somewhere. Any idea?
>

Thu Sep 26, 2013 4:26 pm (PDT) . Posted by:

"Anand Marphatia" Marphatia

Randy,
 
I joined the group recently, but was unsuccessful in getting the add in to work
 
Can you guide me
 
Anand Marphatia
Houston, TX


________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, September 26, 2013 4:30 PM
Subject: Re: Re: [smf_addin] RE: using CONCATENATE function with RCHGetTableCell

 

I don't know of a source, sorry.

On Thu, Sep 26, 2013 at 2:20 PM, <joshi_mandar@hotmail.com> wrote:
Another related question.  As you know AAPL7 mini-options gives one a right but not obligation to buy/sell 10 shares instead of standard 100 shares.  That's why they are non-standard options.  Likewise FAZ1 gives one a right for 25 shares instead of standard 100 shares.  How do I get that multiplication-numbers like 10 or 25?  I can't see that information on Yahoo.  But I am sure it exists somewhere.  Any idea?
>
Diposting oleh EXCEL FEED LEARNING di 16.26

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)