8 New Messages
Digest #2785
Messages
Wed Sep 25, 2013 6:02 am (PDT) . Posted by:
friedman_steve
This is awesome. Great work.
But, it looks like the data is less detailed than the following
ADVfn site I liked to use (at bottom).
Anyhow, any idea if its possible to alter this new workaround
spreadsheet to grab this more detailed table of financials?
this is link to more detailed level. (I also note, it seems
ADVfn hasn't even updated this data for June quarter, so perhaps
they've abandoned it?)
http://www.advfn.
ly_reports&mode=
<http://www.advfn.
rly_reports&mode=
Thanks in advance for any assistance.
---In smf_addin@yahoogrou
Amazing work. Thank you as well.
--- In smf_addin@yahoogrou
Thanks John.
--- In smf_addin@yahoogrou
john_babcock_
<http://us-mg6.
0ADVFN%20-%20New%
Thank you for uploading the updated file ADVFN - 10 year data -
workaround.xlsx. It is very helpful. One small thing - should you
remove older version?
J.C.H.
--- In smf_addin@yahoogrou
Thanks again John!
--- In smf_addin@yahoogrou
As promised, both the updated database and the web crawler are
posted in the "Upload by Members" area. The web crawler is
really crude, but it served it's purpose. The ADVFN sheet seems
to work fine.
--- In smf_addin@yahoogrou
wrote:
Thanks John! That would be awesome!
--- In smf_addin@yahoogrou
wrote:
To build the company database, I built a crude web crawler in
Excel using the VBA Web control. It goes down the list of
tickers, sends ADVFN the "-company"
dmatu2000 suggested. When ADVFN forwards to the correct URL that
includes company name, it parses that URL, extracts the company
name, adds it to the list and goes on to the next one. It takes
about 5 seconds per company.
At that pace, I didn't have time to scan all major market tickers
yesterday and I wanted to see if it was of some use to the group
before running them all. I'll do two things: 1) I'll post the
crawler to the "uploaded by members" section in case someone else
wants to improve it. It's super rough. 2) I'll just leave my
laptop to complete the crawls of NYSE, AMEX, and NASDAQ. That
might take a day or two.
--- In smf_addin@yahoogrou
wrote:
Hi John
A personal thanks from me for this as I used to use advfn for my
financial data source and started working on a whole new version
yesterday from morningstar to get around the advfn issue.
Just a question with regards to the URL for individual stocks.
How did you get the correct url for each ticker? I track 3800
companies some of which are on your list but at least 600 ain't.
Is there an easy way to get them?
Thanks for your help
Ohann
--- In smf_addin@yahoogrou
wrote:
I was impressed with yweinstock&#
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'
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@yahoogrou
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'
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.
er&"/full-
Meaning the company name is not required as long as there is a
placeholder.
--- In smf_addin@yahoogrou
yweinstock your work is superb.
Did you have any luck with bushpilote&#
trying to get it to work but still having issues.
--- In smf_addin@yahoogrou
Does anyone have an update on ADVFN? Are they still working on
their layout?
--- In smf_addin@yahoogrou
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.
&"/financials?
l=1&cffull=1&effull
"http://www.advfn.
-financials?
and for the QUARTELY data replace
"http://www.advfn.
s?btn=annual_
&effull=1&vrfull=
with
"http://www.advfn.
-financials?
.....leave the remaining content of the RCHGetTableCell formula
as is in both cases.
Replacing "microsoft-
financials. For IBM's financials you replace NASDAQ with NYSE and
"microsoft-
In ADVFN's beta version access to a company'
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'
the new norm once they go live with their redesigned website.
Hope this helps.
--- In smf_addin@yahoogrou
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@yahoogrou
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.
> wrote:
It appears that AdvFN is going to be day-to-day for a while.
Consider any changes you make to be temporary.
Wed Sep 25, 2013 10:39 am (PDT) . Posted by:
fincoolio
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@yahoogrou
wrote:
I was impressed with yweinstock&#
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'
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@yahoogrou
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'
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.
er&"/full-
Meaning the company name is not required as long as there is a
placeholder.
--- In smf_addin@yahoogrou
yweinstock your work is superb.
Did you have any luck with bushpilote&#
trying to get it to work but still having issues.
--- In smf_addin@yahoogrou
Does anyone have an update on ADVFN? Are they still working on
their layout?
--- In smf_addin@yahoogrou
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.
&"/financials?
l=1&cffull=1&effull
"http://www.advfn.
-financials?
and for the QUARTELY data replace
"http://www.advfn.
s?btn=annual_
&effull=1&vrfull=
with
"http://www.advfn.
-financials?
.....leave the remaining content of the RCHGetTableCell formula
as is in both cases.
Replacing "microsoft-
financials. For IBM's financials you replace NASDAQ with NYSE and
"microsoft-
In ADVFN's beta version access to a company'
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'
the new norm once they go live with their redesigned website.
Hope this helps.
--- In smf_addin@yahoogrou
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@yahoogrou
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.
> wrote:
It appears that AdvFN is going to be day-to-day for a while.
Consider any changes you make to be temporary.
Wed Sep 25, 2013 6:12 am (PDT) . Posted by:
mlawson3
I'm glad you find it useful.
The exceptions table is set up as a simple Ticker-ADVFN Company
Name reference list. Its located to the right of the I/S with an
orange header column using green text (I may have actually made
it a named range - "Overrides&quo
like you describe, I surf out to the beta financials site and
input the ticker so I can see what ADVFN is using in the URL.
Then I add the ticker/company name combo to the table. After I
update the table, I cycle the sheet through a ticker I know works
and then back to the ticker I want.
Taking LQDT as an example, ADVFN's uses
"liquidity-
financials URL. So, I added "LQDT" to the first column and
"liquidity-
in a known-good ticker before trying LQDT again. Voila, twenty
quarters and ten years of financial data.
Obviously, the exceptions table doesn't lend itself to mass
ticker processing. I thought about trying to fill out the entire
table myself manually, but that seemed a bit overwhelming.
Besides, ADVFN's final format for its new financials may obviate
my efforts, so it may not be worthwhile.
---In smf_addin@yahoogrou
Hey ML, thanks a lot for your different attempt. Just a quick
question on your template....
For example, I tried to pull 20 quarters of data for LQDT and
INWK. Both of those companies have the "mm" and "inc" issues in
the URL. If the URL is not correct, it looks like it defaults to
the non-full history site so I'm only able to pull the last 4
quarters. Any help would be much appreciated. Thanks again for
all your efforts!
kk
---In smf_addin@yahoogrou
wrote:
The "Upload by Members" section now has my attempt at working
with ADVFN's new website and data. It has data for the trailing
12 quarters as well as the last 10 years. It shouldn'
hard to adjust if you want more or less quarterly or annual data.
Teasing the exchange and the company name ADVFN uses in the URL
from the smfGetWebPage function took an obnoxious amount of text
parsing. In the end, I had to set up an exceptions table for
when the parsing just didn't provide the correct company name.
Its not elegant, but its not overly burdensome, either. On the
other hand, every single bit of data is pulled using the
RCHGetTableCell function, so any recalculations take a bit to
complete. Those with older computers beware??
Wed Sep 25, 2013 6:34 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
I have no problem accessing Barchart.com. I do not access it on a regular basis. But when I do like just now it works fine.
I have idea what your error might be.
Sorry,
Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of yellowdistiler@yahoo.com
Sent: Tuesday, September 24, 2013 9:06 PM
To: smf_addin@yahoogroups.com
Subject: RE: RE: Re: [smf_addin] RE: SMF-Template-Barchart-Market-Momentum.xls returns errors
I did get access to barchart.com's Terms of Use page
http://realtime.barchart.com/agreement.asp
"You may not use any data mining, robots, or similar data gathering and extraction tools on the Content"
Perhaps my access with SMF was interpreted as a robot and my IP was blocked. It seems use of SMF for access to their site may have to be limited so as not to appear as a robot. I have no idea what their limits or algorithms may be in making that decision. But if one is accessing their site with SMF, it seems you may have to be frugal in using it.
If Kermit W. Prather in the original message used the technical template first it probably worked. But then using the other templates a short time later triggered the "robot" algorithm. If he had not accessed barchart.com before using the templates, then it seems that the threshold may not be very high for being id'd as a robot. It would be interesting to see if Kermit can access barchart.com with a browser if the "Error" is still being returned for the templates.
---In smf_addin@yahoogroups.com, <smf_addin@yahoogroups.com> wrote:
I just tried again using your links via the internet or with the SMF function and still get the 403 Forbidden message and "Error". I had never had a problem downloading moving averages for a greater number of stocks with the same spreadsheet from their site (I think all the moving averages for a stock are on a single web page). I only renew them every few months.
Since others can access the site I'll have to presume that my IP is blocked. I'll try again in a week or so and use other sources for now. If I get back on their site, I will limit the number of stocks accessed at any one time. I don't know if this is peculiar for barchart.com to block access. The same number of stocks has run fine on every other site I use for over 5 years. But...things change...
---In smf_addin@yahoogroups.com, <smf_addin@yahoogroups.com> wrote:
I just tried these templates:
SMF-Template-Barchart-Market-Momentum.xls
SMF-Template-Barchart-Technical-Indicators.xls
....and both worked fine for me. Their source pages, similar to:
http://www.barchart.com/opinions/stocks/MMM
http://www.barchart.com/stocks/momentum.php
...both work fine for me in FireFox as well.
Were you using the add-in to access a large number of web pages on their site? I don't know if they are blocking you or not. Can you access their home page?
On Tue, Sep 24, 2013 at 2:06 PM, <yellowdistiler@...> wrote:
On Monday Sep 23, 2013 I was running an update on moving averages from www.barchart.com and it was working fine until in the middle of the process I kept getting "Error" values. I checked and this occurred for RCHGetElementNumber() numbers 701--717 and 1146--1284 for all stock symbols I tried, even ones that had worked just minutes before. I am using Windows XP, Excel 2000, and the most current SMF version. My internet connection is OK.
I then tried going directly to the barchart web sites. It seems something has changed on www.barchart.com (I also tried the addresses used by RCHGetElementNumber()) or perhaps they are blocking IPs. I get the web page that shows the message at the end of this reply. That explains why I keep getting "Error" when attempting to retrieve data from any "barchart"; web site. I also get all "Error"s from all the Barchart templates.
Have others had this problem beginning Sep 23, 2013? Is there a workaround that anyone has found?
I have idea what your error might be.
Sorry,
Kermit
From: smf_addin@yahoogrou
Sent: Tuesday, September 24, 2013 9:06 PM
To: smf_addin@yahoogrou
Subject: RE: RE: Re: [smf_addin] RE: SMF-Template-
I did get access to barchart.com&#
http://realtime.
"You may not use any data mining, robots, or similar data gathering and extraction tools on the Content"
Perhaps my access with SMF was interpreted as a robot and my IP was blocked. It seems use of SMF for access to their site may have to be limited so as not to appear as a robot. I have no idea what their limits or algorithms may be in making that decision. But if one is accessing their site with SMF, it seems you may have to be frugal in using it.
If Kermit W. Prather in the original message used the technical template first it probably worked. But then using the other templates a short time later triggered the "robot" algorithm. If he had not accessed barchart.com before using the templates, then it seems that the threshold may not be very high for being id'd as a robot. It would be interesting to see if Kermit can access barchart.com with a browser if the "Error" is still being returned for the templates.
---In smf_addin@yahoogrou
I just tried again using your links via the internet or with the SMF function and still get the 403 Forbidden message and "Error"
Since others can access the site I'll have to presume that my IP is blocked. I'll try again in a week or so and use other sources for now. If I get back on their site, I will limit the number of stocks accessed at any one time. I don't know if this is peculiar for barchart.com to block access. The same number of stocks has run fine on every other site I use for over 5 years. But...things change...
---In smf_addin@yahoogrou
I just tried these templates:
SMF-Template-
SMF-Template-
....and both worked fine for me. Their source pages, similar to:
http://www.barchart
http://www.barchart
...both work fine for me in FireFox as well.
Were you using the add-in to access a large number of web pages on their site? I don't know if they are blocking you or not. Can you access their home page?
On Tue, Sep 24, 2013 at 2:06 PM, <yellowdistiler@
On Monday Sep 23, 2013 I was running an update on moving averages from www.barchart.
I then tried going directly to the barchart web sites. It seems something has changed on www.barchart.
Have others had this problem beginning Sep 23, 2013? Is there a workaround that anyone has found?
Wed Sep 25, 2013 10:42 am (PDT) . Posted by:
derrickhnj
Awesome, working now, that scared me for a second. I converted
all my workbooks from ADVFN to GuruFocus model and was going to
go nuts if GuruFocus was doing the same as ADVFN. Again, thanks
so much for this stuff, it takes hours off my investing decision
making.
---In smf_addin@yahoogrou
Thanks I was wondering why the gurufocus 10-year spreadsheet
wasn't updating.
---In smf_addin@yahoogrou
When I try to go to gurufocus.com <http://gurufocus.
now, it tells me the website is down.
Verified by: http://www.isitdown
<http://www.isitdown
On Tue, Sep 24, 2013 at 7:41 PM, <derrickhorvath@
<mailto:derrickh
Anyone having trouble with this model lately? It was working
fine for me just yesterday and today it is not pulling in any
amounts. My sheet is on auto calculate and the addin is
correctly installed because I'm also pulling MSN data. Just
don't know what's happening.
Wed Sep 25, 2013 12:57 pm (PDT) . Posted by:
gbullr
Thank you!!!!
AWESOME!!!!
AWESOME!!!!
Wed Sep 25, 2013 2:00 pm (PDT) . Posted by:
joshi_mandar
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",\
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=",A1,"&m=",B1,""""),1,CONCATENATE("""",C1,""""))
It throws an error. Not sure what I am doing wrong. Can anyone
help?
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(
1,"FAZ1150117C
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.=RCHGetTableCe
q/op?s=",A1,"
It throws an error. Not sure what I am doing wrong. Can anyone
help?
Wed Sep 25, 2013 7:21 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
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=
",A1,"&m=",B1),1,C1)
I prefer to use the concatenation operator (i.e. the ampersand):
=RCHGetTableCell("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@...<joshi_mandar@hotmail.com>
> 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",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=
> ",A1,"&m=",B1,""""),1,CONCATENATE("""",C1,""""))
>
> It throws an error. Not sure what I am doing wrong. Can anyone help?
>
append back and front are not good. All you need is just:
=RCHGetTableCell(
",A1,"
I prefer to use the concatenation operator (i.e. the ampersand):
=RCHGetTableCell(
Also, I prefer to use this function:
=smfGetOptionQuotes
If you have the symbol, expiration date, strike price, and option type in
cells of their own (makes it easy to change):
=smfGetOptionQuotes
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@
> 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(
> 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.=RCHGetTableCe
> http://finance.
> ",A1,"
>
> It throws an error. Not sure what I am doing wrong. Can anyone help?
>
Tidak ada komentar:
Posting Komentar