8 Messages
Digest #3297
Messages
Tue Jan 13, 2015 6:55 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Ahhh. OK. The IFERROR() function was new to EXCEL 2007. However, the add-in
has its equivalent. Just do a "Find and Replace All" and change that
"_xlfn." prefix in front of the function to be nothing.
To use the macro, just bring up the macro dialog (alt-F8), type in the name
of the macro, then click on the "Run" button when it activates.
The "read only" issue is because it wants to prevent you from overwriting
the XLSX file with an XLS file and losing the EXCEL 2007 functionality. But
it shouldn't be a problem. Just do a "save as" after you have fixed the
links.
On Tue, Jan 13, 2015 at 7:41 PM, bradk8605@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> No I didn't move the XLA file, in the formula bar it shows this as the
> first thing: _xlfn.IFERROR
>
> I dont know how to use the smfFixLinks because im using Excel 2003 ( I
> know #OLD) LOL.
>
> When I open the file it says
>
> "The file has been opened in read-only mode to protect the original file."
>
> "One or more functions in this workbook are not available in this version
> of excel. When recalculated these functions will return #NAME?"
>
has its equivalent. Just do a "Find and Replace All" and change that
"_xlfn." prefix in front of the function to be nothing.
To use the macro, just bring up the macro dialog (alt-F8), type in the name
of the macro, then click on the "Run" button when it activates.
The "read only" issue is because it wants to prevent you from overwriting
the XLSX file with an XLS file and losing the EXCEL 2007 functionality. But
it shouldn't be a problem. Just do a "save as" after you have fixed the
links.
On Tue, Jan 13, 2015 at 7:41 PM, bradk8605@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> No I didn't move the XLA file, in the formula bar it shows this as the
> first thing: _xlfn.IFERROR
>
> I dont know how to use the smfFixLinks because im using Excel 2003 ( I
> know #OLD) LOL.
>
> When I open the file it says
>
> "The file has been opened in read-only mode to protect the original file."
>
> "One or more functions in this workbook are not available in this version
> of excel. When recalculated these functions will return #NAME?"
>
Tue Jan 13, 2015 7:08 pm (PST) . Posted by:
bradk8605
Perfection! Works perfectly now thank you for the tip to delete the " xlfn" that was what caused the problem.
Thank you for all your help.
Thank you for all your help.
Tue Jan 13, 2015 6:57 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Sorry, I meant the "Works in Progress" folder (or web page) on the add-in
web site. That "Read me" file you mention would have pointed you to the web
site if you had read it. :)
On Tue, Jan 13, 2015 at 7:50 PM, electronicjason@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I looked in the "Works In Progress" folder but there are only 2 files,
> ReadMe and Get888. There's another folder "Need to be looked at" but that
> doesn't have any files from Oct14 in it.
>
web site. That "Read me" file you mention would have pointed you to the web
site if you had read it. :)
On Tue, Jan 13, 2015 at 7:50 PM, electronicjason@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I looked in the "Works In Progress" folder but there are only 2 files,
> ReadMe and Get888. There's another folder "Need to be looked at" but that
> doesn't have any files from Oct14 in it.
>
Tue Jan 13, 2015 7:13 pm (PST) . Posted by:
electronicjason
Awesome. That worked. Strange thing is #3848-3853 is Google "Shares Outstanding" for FY1-FY6. Numbers 3848-3851 (FY1-FY4) work but the last two #3852-3853 do not work (FY5-FY6). Is the google link just dead?
Also, what is the end of year Google closing price called? I need it for FY1-FY6 as well. I've been PgDn and PgUp through the list and can't find the name. Maybe I can CTRL-F it.
Also, what is the end of year Google closing price called? I need it for FY1-FY6 as well. I've been PgDn and PgUp through the list and can't find the name. Maybe I can CTRL-F it.
Tue Jan 13, 2015 8:12 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Google carried six years of data when I created the elements, before they
trimmed it down to 4.
I don't think the closing price would be on any of the financial statements
listings. But you could retrieve them with either the smfPricesByDates() or
RCHGetYahooHistory() functions, using the fiscal year-end dates, depending
on whether you need adjusted closing prices (for computing total returns)
or unadjusted closing prices.
On Tue, Jan 13, 2015 at 8:13 PM, electronicjason@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Awesome. That worked. Strange thing is #3848-3853 is Google "Shares
> Outstanding" for FY1-FY6. Numbers 3848-3851 (FY1-FY4) work but the last two
> #3852-3853 do not work (FY5-FY6). Is the google link just dead?
>
> Also, what is the end of year Google closing price called? I need it for
> FY1-FY6 as well. I've been PgDn and PgUp through the list and can't find
> the name. Maybe I can CTRL-F it.
>
trimmed it down to 4.
I don't think the closing price would be on any of the financial statements
listings. But you could retrieve them with either the smfPricesByDates() or
RCHGetYahooHistory() functions, using the fiscal year-end dates, depending
on whether you need adjusted closing prices (for computing total returns)
or unadjusted closing prices.
On Tue, Jan 13, 2015 at 8:13 PM, electronicjason@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Awesome. That worked. Strange thing is #3848-3853 is Google "Shares
> Outstanding" for FY1-FY6. Numbers 3848-3851 (FY1-FY4) work but the last two
> #3852-3853 do not work (FY5-FY6). Is the google link just dead?
>
> Also, what is the end of year Google closing price called? I need it for
> FY1-FY6 as well. I've been PgDn and PgUp through the list and can't find
> the name. Maybe I can CTRL-F it.
>
Tue Jan 13, 2015 8:23 pm (PST) . Posted by:
electronicjason
I ended up using
=RCHGetTableCell("http://www.gurufocus.com/financials/" & A2 & "",1,">Fiscal Period",">Month")
where "A2" contains the ticker. That gives me the month end close price from the annuals which works for what I'm doing.
I'm not sure if " & A2 & " is the most elegant way to get a ticker from a cell into the URL but it works so I'll take it.
Thank you! Thank you!
=RCHGetTableCell("http://www.gurufocus.com/financials/" & A2 & ""
where "A2" contains the ticker. That gives me the month end close price from the annuals which works for what I'm doing.
I'm not sure if " & A2 & " is the most elegant way to get a ticker from a cell into the URL but it works so I'll take it.
Thank you! Thank you!
Tue Jan 13, 2015 9:59 pm (PST) . Posted by:
electronicjason
I also spoke too soon.
=RCHGetTableCell("http://www.gurufocus.com/financials/" & $A2 & "",6,">Fiscal Period",">Month End")
I'm using this on a 2,500 row spreadsheet. Each row is a seperate ticker. The first 935 rows worked fine. Then row 936 and all after display "Error".
I think this is simply a timeout error. It took a real long time (over ten minutes) for Excel to import all the HTML data to populate my cells when I Double Clicked the bottom right grab bar to fill this formula down to all the cells.
How can I get the timeout errors to go away? Maybe it's something else ..... ?? I should also note that randomly on the first 935 rows there were some rows that get all "Error" going across. I'm guessing these are timeout errors as well.
Thank you.
=RCHGetTableCell("http://www.gurufocus.com/financials/" & $A2 & ""
I'm using this on a 2,500 row spreadsheet. Each row is a seperate ticker. The first 935 rows worked fine. Then row 936 and all after display "Error"
I think this is simply a timeout error. It took a real long time (over ten minutes) for Excel to import all the HTML data to populate my cells when I Double Clicked the bottom right grab bar to fill this formula down to all the cells.
How can I get the timeout errors to go away? Maybe it's something else ..... ?? I should also note that randomly on the first 935 rows there were some rows that get all "Error" going across. I'm guessing these are timeout errors as well.
Thank you.
Tue Jan 13, 2015 10:27 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Ouch! Why do you need data for so many companies? The purpose of the add-in
is for ad hoc data retrieval, not for the building of databases. The last
thing I want is to encourage the free data providers to change how their
web site works so something like the add-in can no longer access their
data. Get a few add-in users doing the same thing at the same time and it
might look like a DDoS attack. :)
In any case, the add-in saves a web page into a slot before extracting data
from it. It only has 1000 slots. So once those slots are filled up, all
further data requests are set to "Error". You'd need to run the
smfForceRecalculation macro or exit and restart EXCEL to empty all those
slots. However, note that that means restarting data retrievals from the
first item -- ALL workbook cells will recalculate. So you would just end up
with different data items getting the "Error" message, depending on the
order of how EXCEL executed the data request.
On average, my GuruFocus requests take about 0.39 seconds each. So that
would be about 6-7 minutes for a thousand web page requests, so 10 minutes
sounds a bit long. Be glad it's not Amazon. Those average about 2 seconds
each for me.
Also, for what it sounds like you're doing, you should consider using the
smfUpdateDownloadTable process. It saves values into a workbook so you
don't have to keep worrying about recalculation times.
PS: If you're using the data to build a database for screening, you should
look at Portfolio123.com. Or AAII's Stock Investor Pro database.
On Tue, Jan 13, 2015 at 10:59 PM, electronicjason@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I also spoke too soon.
>
> =RCHGetTableCell("http://www.gurufocus.com/financials/" & $A2 &
> "",6,">Fiscal Period",">Month End")
>
> I'm using this on a 2,500 row spreadsheet. Each row is a seperate ticker.
> The first 935 rows worked fine. Then row 936 and all after display "Error".
>
> I think this is simply a timeout error. It took a real long time (over ten
> minutes) for Excel to import all the HTML data to populate my cells when I
> Double Clicked the bottom right grab bar to fill this formula down to all
> the cells.
>
> How can I get the timeout errors to go away? Maybe it's something else
> ..... ?? I should also note that randomly on the first 935 rows there were
> some rows that get all "Error" going across. I'm guessing these are timeout
> errors as well.
>
is for ad hoc data retrieval, not for the building of databases. The last
thing I want is to encourage the free data providers to change how their
web site works so something like the add-in can no longer access their
data. Get a few add-in users doing the same thing at the same time and it
might look like a DDoS attack. :)
In any case, the add-in saves a web page into a slot before extracting data
from it. It only has 1000 slots. So once those slots are filled up, all
further data requests are set to "Error". You'd need to run the
smfForceRecalculation macro or exit and restart EXCEL to empty all those
slots. However, note that that means restarting data retrievals from the
first item -- ALL workbook cells will recalculate. So you would just end up
with different data items getting the "Error" message, depending on the
order of how EXCEL executed the data request.
On average, my GuruFocus requests take about 0.39 seconds each. So that
would be about 6-7 minutes for a thousand web page requests, so 10 minutes
sounds a bit long. Be glad it's not Amazon. Those average about 2 seconds
each for me.
Also, for what it sounds like you're doing, you should consider using the
smfUpdateDownloadTable process. It saves values into a workbook so you
don't have to keep worrying about recalculation times.
PS: If you're using the data to build a database for screening, you should
look at Portfolio123.com. Or AAII's Stock Investor Pro database.
On Tue, Jan 13, 2015 at 10:59 PM, electronicjason@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I also spoke too soon.
>
> =RCHGetTableCell("http://www.gurufocus.com/financials/" & $A2 &
> ""
>
> I'm using this on a 2,500 row spreadsheet. Each row is a seperate ticker.
> The first 935 rows worked fine. Then row 936 and all after display "Error"
>
> I think this is simply a timeout error. It took a real long time (over ten
> minutes) for Excel to import all the HTML data to populate my cells when I
> Double Clicked the bottom right grab bar to fill this formula down to all
> the cells.
>
> How can I get the timeout errors to go away? Maybe it's something else
> ..... ?? I should also note that randomly on the first 935 rows there were
> some rows that get all "Error" going across. I'm guessing these are timeout
> errors as well.
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar