15 New Messages
Digest #2642
Messages
Tue Jun 4, 2013 7:18 am (PDT) . Posted by:
"Steven" sdavis81
I was looking over the documentation for pulling weekly VXX quotes. My sheet uses the Yahoo query and returns an error for weeklies.
It looks like for a single data item I would use smfGetOXOptionQuote say for "last price".
My sheet currently pulls the bid and ask and divides them though. Is there a way to do this with one query or does it need to be entered as a formula with 2 queries?
Thanks as always.
It looks like for a single data item I would use smfGetOXOptionQuote say for "last price".
My sheet currently pulls the bid and ask and divides them though. Is there a way to do this with one query or does it need to be entered as a formula with 2 queries?
Thanks as always.
Tue Jun 4, 2013 7:56 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
It would need to be done with two functions in a single formula. However,
the web page would only be retrieved once. The add-in saves a web page
before it extracts data from it, so further queries against that same web
page would just come from the saved copy and not need to go back to the
Internet.
On Tue, Jun 4, 2013 at 7:18 AM, Steven <sdavis81@yahoo.com > wrote:
> I was looking over the documentation for pulling weekly VXX quotes. My
> sheet uses the Yahoo query and returns an error for weeklies.
>
> It looks like for a single data item I would use smfGetOXOptionQuote say
> for "last price".
>
> My sheet currently pulls the bid and ask and divides them though. Is
> there a way to do this with one query or does it need to be entered as a
> formula with 2 queries?
>
the web page would only be retrieved once. The add-in saves a web page
before it extracts data from it, so further queries against that same web
page would just come from the saved copy and not need to go back to the
Internet.
On Tue, Jun 4, 2013 at 7:18 AM, Steven <sdavis81@yahoo.
> I was looking over the documentation for pulling weekly VXX quotes. My
> sheet uses the Yahoo query and returns an error for weeklies.
>
> It looks like for a single data item I would use smfGetOXOptionQuote say
> for "last price".
>
> My sheet currently pulls the bid and ask and divides them though. Is
> there a way to do this with one query or does it need to be entered as a
> formula with 2 queries?
>
Tue Jun 4, 2013 7:50 am (PDT) . Posted by:
"Matthew" mlawson3
First, thank you for maintaining this add-in. It's quite useful.
Second, and you probably already know this, the St. Louis Fed has published its own add-in for every economic bit of data the U.S. government produces (as far as I can tell). It even produces official Fed looking charts.
Third, what is the benefit of using the smfGetADVFNElement function over the more-general RCHGetElementNumber function?
Fourth, have you ever attempted to pull the financial data from the SEC's EDGAR website? In addition to the plain old web pages, filers must now provide xbrl versions, which would seem to lend itself to smf's functionality. Or is it far more complicated than it appears?
Second, and you probably already know this, the St. Louis Fed has published its own add-in for every economic bit of data the U.S. government produces (as far as I can tell). It even produces official Fed looking charts.
Third, what is the benefit of using the smfGetADVFNElement function over the more-general RCHGetElementNumber function?
Fourth, have you ever attempted to pull the financial data from the SEC's EDGAR website? In addition to the plain old web pages, filers must now provide xbrl versions, which would seem to lend itself to smf's functionality. Or is it far more complicated than it appears?
Tue Jun 4, 2013 8:00 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The RCHGetElementNumber() functions just use the smfGetAdvFNElement()
functions to retrieve the data. However, only 10 annual periods and 20
quarterly periods are defined for RCHGetElementNumber(), so
smfGetAdvFNElement() would allow you to get data from further back.
The last time I looked at XBRL, there were two issues to deal with:
-- The XBRL files didn't have a full range of history, so multiple files
would be needed for a range of data
-- There was no easy naming convention, so it would require multiple
Internet queries to even figure out which files to read.
On Tue, Jun 4, 2013 at 7:04 AM, Matthew <mlawson3@yahoo.com > wrote:
> First, thank you for maintaining this add-in. It's quite useful.
>
> Second, and you probably already know this, the St. Louis Fed has
> published its own add-in for every economic bit of data the U.S. government
> produces (as far as I can tell). It even produces official Fed looking
> charts.
>
> Third, what is the benefit of using the smfGetADVFNElement function over
> the more-general RCHGetElementNumber function?
>
> Fourth, have you ever attempted to pull the financial data from the SEC's
> EDGAR website? In addition to the plain old web pages, filers must now
> provide xbrl versions, which would seem to lend itself to smf's
> functionality. Or is it far more complicated than it appears?
>
functions to retrieve the data. However, only 10 annual periods and 20
quarterly periods are defined for RCHGetElementNumber
smfGetAdvFNElement(
The last time I looked at XBRL, there were two issues to deal with:
-- The XBRL files didn't have a full range of history, so multiple files
would be needed for a range of data
-- There was no easy naming convention, so it would require multiple
Internet queries to even figure out which files to read.
On Tue, Jun 4, 2013 at 7:04 AM, Matthew <mlawson3@yahoo.
> First, thank you for maintaining this add-in. It's quite useful.
>
> Second, and you probably already know this, the St. Louis Fed has
> published its own add-in for every economic bit of data the U.S. government
> produces (as far as I can tell). It even produces official Fed looking
> charts.
>
> Third, what is the benefit of using the smfGetADVFNElement function over
> the more-general RCHGetElementNumber function?
>
> Fourth, have you ever attempted to pull the financial data from the SEC's
> EDGAR website? In addition to the plain old web pages, filers must now
> provide xbrl versions, which would seem to lend itself to smf's
> functionality. Or is it far more complicated than it appears?
>
Tue Jun 4, 2013 8:46 am (PDT) . Posted by:
"Xin L" liucyndy
Thanks a lot. I am pretty new to this function and I guess I need to install the add-in to my excel right? how can I do that?
Thanks!
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> I just copied and pasted your formula below and it worked fine for me.
>
> I had MMM and IBM in cells A3 and A5, and 1, 2, and 3, in cells B1, C1, and
> D1. I'll assume your #VALUE! error has to do with something in the input
> cells?
>
> 1 2 3 Ticker Company Sector MMM IBM International Business Machines
> Corp. Technology IBM MMM 3M Co. Conglomerates
>
> On Mon, Jun 3, 2013 at 11:10 AM, Xin L <jgr1230@...> wrote:
>
> > I tried the finviz csv formula, it returns #value! can you let me know
> > where I may be wrong? here is the formula I used
> > =smfGetCSVFile("http://finviz.com/export.ashx?v=151&T=
> > "&sfJoin(A3:A12,",")&"&c="&sfJoin(B1:G1,","))
> >
>
Thanks!
--- In smf_addin@yahoogrou
>
> I just copied and pasted your formula below and it worked fine for me.
>
> I had MMM and IBM in cells A3 and A5, and 1, 2, and 3, in cells B1, C1, and
> D1. I'll assume your #VALUE! error has to do with something in the input
> cells?
>
> 1 2 3 Ticker Company Sector MMM IBM International Business Machines
> Corp. Technology IBM MMM 3M Co. Conglomerates
>
> On Mon, Jun 3, 2013 at 11:10 AM, Xin L <jgr1230@
>
> > I tried the finviz csv formula, it returns #value! can you let me know
> > where I may be wrong? here is the formula I used
> > =smfGetCSVFile(
> > "&sfJoin(
> >
>
Tue Jun 4, 2013 8:49 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Just exit EXCEL and follow the instructions in the overview documentation
from the "Documentation" folder of the FILES area.
There are also several step-by-step instruction entries in the LINKS area
of the group, written by other people.
On Tue, Jun 4, 2013 at 8:46 AM, Xin L <jgr1230@gmail.com > wrote:
> Thanks a lot. I am pretty new to this function and I guess I need to
> install the add-in to my excel right? how can I do that?
>
from the "Documentation
There are also several step-by-step instruction entries in the LINKS area
of the group, written by other people.
On Tue, Jun 4, 2013 at 8:46 AM, Xin L <jgr1230@gmail.
> Thanks a lot. I am pretty new to this function and I guess I need to
> install the add-in to my excel right? how can I do that?
>
Tue Jun 4, 2013 10:03 am (PDT) . Posted by:
"Matthew" mlawson3
The function is straightforward enough, so I am not sure where I have gone wrong. When I use 99 as my choice and reference a webpage, including StockCharts, the cell just says Error. Do I need a different URL from the source? Or is it something else?
Also, the VBA code shows options # 97, 98 and 99. Are those beta or live?
Win7, Excel 2007, SMF v. 2.1.2012.12.29
Also, the VBA code shows options # 97, 98 and 99. Are those beta or live?
Win7, Excel 2007, SMF v. 2.1.2012.12.
Tue Jun 4, 2013 10:07 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You need the URL of an image, not a web page. A web page can contain many
different images.
On Tue, Jun 4, 2013 at 10:03 AM, Matthew <mlawson3@yahoo.com > wrote:
> The function is straightforward enough, so I am not sure where I have gone
> wrong. When I use 99 as my choice and reference a webpage, including
> StockCharts, the cell just says Error. Do I need a different URL from the
> source? Or is it something else?
>
> Also, the VBA code shows options # 97, 98 and 99. Are those beta or live?
>
> Win7, Excel 2007, SMF v. 2.1.2012.12.29
>
different images.
On Tue, Jun 4, 2013 at 10:03 AM, Matthew <mlawson3@yahoo.
> The function is straightforward enough, so I am not sure where I have gone
> wrong. When I use 99 as my choice and reference a webpage, including
> StockCharts, the cell just says Error. Do I need a different URL from the
> source? Or is it something else?
>
> Also, the VBA code shows options # 97, 98 and 99. Are those beta or live?
>
> Win7, Excel 2007, SMF v. 2.1.2012.12.
>
Tue Jun 4, 2013 10:26 am (PDT) . Posted by:
"MLawson" mlawson3
Ah, well...file that under newb questions.
Excluding StockCharts, have you found any other worthwhile sources for price charts? Or is it not worth the hassle or not viable (Flash / Silverlight charts)?
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> You need the URL of an image, not a web page. A web page can contain many
> different images.
>
> On Tue, Jun 4, 2013 at 10:03 AM, Matthew <mlawson3@...> wrote:
>
> > The function is straightforward enough, so I am not sure where I have gone
> > wrong. When I use 99 as my choice and reference a webpage, including
> > StockCharts, the cell just says Error. Do I need a different URL from the
> > source? Or is it something else?
> >
> > Also, the VBA code shows options # 97, 98 and 99. Are those beta or live?
> >
> > Win7, Excel 2007, SMF v. 2.1.2012.12.29
> >
>
Excluding StockCharts, have you found any other worthwhile sources for price charts? Or is it not worth the hassle or not viable (Flash / Silverlight charts)?
--- In smf_addin@yahoogrou
>
> You need the URL of an image, not a web page. A web page can contain many
> different images.
>
> On Tue, Jun 4, 2013 at 10:03 AM, Matthew <mlawson3@
>
> > The function is straightforward enough, so I am not sure where I have gone
> > wrong. When I use 99 as my choice and reference a webpage, including
> > StockCharts, the cell just says Error. Do I need a different URL from the
> > source? Or is it something else?
> >
> > Also, the VBA code shows options # 97, 98 and 99. Are those beta or live?
> >
> > Win7, Excel 2007, SMF v. 2.1.2012.12.
> >
>
Tue Jun 4, 2013 1:08 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I don't use it much. It must be at least 6 months since I've used it for
something I'd been using.
A sampling of a variety of charts from MorningStar and GuruFocus:
http://ogres-crypt.com/php/chart-mscf.php?s=MMM
http://ogres-crypt.com/php/chart-gf.php?s=MMM
On Tue, Jun 4, 2013 at 10:25 AM, MLawson <mlawson3@yahoo.com > wrote:
>
> Excluding StockCharts, have you found any other worthwhile sources for
> price charts? Or is it not worth the hassle or not viable (Flash /
> Silverlight charts)?
>
something I'd been using.
A sampling of a variety of charts from MorningStar and GuruFocus:
http://ogres-
http://ogres-
On Tue, Jun 4, 2013 at 10:25 AM, MLawson <mlawson3@yahoo.
>
> Excluding StockCharts, have you found any other worthwhile sources for
> price charts? Or is it not worth the hassle or not viable (Flash /
> Silverlight charts)?
>
Tue Jun 4, 2013 6:16 pm (PDT) . Posted by:
"weekeewawa" weekeewawa
hello randy
on this page
http://investing.businessweek.com/research/stocks/snapshot/snapshot.asp?ticker=IBM
i tried to extract information for example using this code
=RCHGetTableCell("http://investing.businessweek.com/research/stocks/snapshot/snapshot.asp?ticker=IBM ",1,"Open")
to extract the open price
but the result that i was given is ''Open$208.60''
is it possible to just have a code that extracts $208.60 instead?
similarly,
under this page
http://investing.businessweek.com/research/stocks/financials/ratios.asp?ticker=IBM
while using this code
=RCHGetTableCell("http://investing.businessweek.com/research/stocks/financials/ratios.asp?ticker=IBM ",0,"Return on Assets")
the result i was given is ''ReturnOnAssetsIndustryComparison11.68%"
is there a way to extract just 11.68%?
thanks
--- In smf_addin@yahoogroups.com , "weekeewawa" <weekeewawa@...> wrote:
>
> hello randy,
>
> yes i did tried it
>
> but when i create the url string independently, it seems ok. but when i started to combine, i either missed out a concatenation symbol before or either after, i guess i neeed even more practice
>
> but regarding the problem, it works well!
>
> thanks randy
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > You need a concatenation symbol AFTER the A1 as well.
> >
> > As I said, you should try creating the URL string independent of the
> > formula first, until you're more experienced and more comfortable doing
> > concatenations.
> >
> > When I entered your formula, EXCEL pointed me directly to where the problem
> > was.
> >
> > On Tue, May 28, 2013 at 10:07 AM, weekeewawa <weekeewawa@> wrote:
> >
> > >
> > > tried to extract balance sheet from
> > >
> > >
> > > http://investing.businessweek.com/research/stocks/financials/financials.asp?ticker=IBM&dataset=balanceSheet&period=A ¤cy=native
> > >
> > > with this code
> > >
> > > =RCHGetHTMLTable("
> > > http://investing.businessweek.com/research/stocks/financials/financials.asp?ticker=IBM&dataset=balanceSheet&period=A ¤cy=native","US
> > > Dollars",-1,"",1)
> > >
> > > and it works
> > >
> > > but when i tried to point to cell A1 with this code
> > >
> > > =RCHGetHTMLTable("
> > > http://investing.businessweek.com/research/stocks/financials/financials.asp?ticker= "&A1"&dataset=balanceSheet&period=A¤cy=native","US
> > > Dollars",-1,"",1)
> > >
> > > something wrong with it?
> > >
> >
>
on this page
http://investing.
i tried to extract information for example using this code
=RCHGetTableCell(
to extract the open price
but the result that i was given is ''Open$
is it possible to just have a code that extracts $208.60 instead?
similarly,
under this page
http://investing.
while using this code
=RCHGetTableCell(
the result i was given is ''ReturnOnA
is there a way to extract just 11.68%?
thanks
--- In smf_addin@yahoogrou
>
> hello randy,
>
> yes i did tried it
>
> but when i create the url string independently, it seems ok. but when i started to combine, i either missed out a concatenation symbol before or either after, i guess i neeed even more practice
>
> but regarding the problem, it works well!
>
> thanks randy
>
> --- In smf_addin@yahoogrou
> >
> > You need a concatenation symbol AFTER the A1 as well.
> >
> > As I said, you should try creating the URL string independent of the
> > formula first, until you're more experienced and more comfortable doing
> > concatenations.
> >
> > When I entered your formula, EXCEL pointed me directly to where the problem
> > was.
> >
> > On Tue, May 28, 2013 at 10:07 AM, weekeewawa <weekeewawa@
> >
> > >
> > > tried to extract balance sheet from
> > >
> > >
> > > http://investing.
> > >
> > > with this code
> > >
> > > =RCHGetHTMLTable(
> > > http://investing.
> > > Dollars"
> > >
> > > and it works
> > >
> > > but when i tried to point to cell A1 with this code
> > >
> > > =RCHGetHTMLTable(
> > > http://investing.
> > > Dollars"
> > >
> > > something wrong with it?
> > >
> >
>
Tue Jun 4, 2013 6:53 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Well, if you had looked at the web page coding, you would have seen that
the table cells have multiple information in them, which is why you're
seeing the labels and the amounts together. Also, using "open" as a search
string was finding you the wrong "open" string, because it's too general a
search term. It's just coincidental that the first table cell happened to
be the open price.
Instead, try:
=smfConvertData(smfGetTagContent("
http://investing.businessweek.com/research/stocks/snapshot/snapshot.asp?ticker=IBM
","div",1,">Open"))
=smfConvertData(smfGetTagContent("
http://investing.businessweek.com/research/stocks/financials/ratios.asp?ticker=IBM ","div",3,">Return
on Assets"))
On Tue, Jun 4, 2013 at 6:16 PM, weekeewawa <weekeewawa@yahoo.com > wrote:
>
> on this page
>
>
> http://investing.businessweek.com/research/stocks/snapshot/snapshot.asp?ticker=IBM
>
> i tried to extract information for example using this code
>
> =RCHGetTableCell("
> http://investing.businessweek.com/research/stocks/snapshot/snapshot.asp?ticker=IBM
> ",1,"Open")
>
> to extract the open price
>
> but the result that i was given is ''Open$208.60''
>
> is it possible to just have a code that extracts $208.60 instead?
>
> similarly,
>
> under this page
>
>
> http://investing.businessweek.com/research/stocks/financials/ratios.asp?ticker=IBM
>
> while using this code
>
> =RCHGetTableCell("
> http://investing.businessweek.com/research/stocks/financials/ratios.asp?ticker=IBM ",0,"Return
> on Assets")
>
> the result i was given is ''ReturnOnAssetsIndustryComparison11.68%"
>
> is there a way to extract just 11.68%?
>
>
the table cells have multiple information in them, which is why you're
seeing the labels and the amounts together. Also, using "open" as a search
string was finding you the wrong "open" string, because it's too general a
search term. It's just coincidental that the first table cell happened to
be the open price.
Instead, try:
=smfConvertData(
http://investing.
","
=smfConvertData(
http://investing.
on Assets"
On Tue, Jun 4, 2013 at 6:16 PM, weekeewawa <weekeewawa@yahoo.
>
> on this page
>
>
> http://investing.
>
> i tried to extract information for example using this code
>
> =RCHGetTableCell(
> http://investing.
> ",1,"
>
> to extract the open price
>
> but the result that i was given is ''Open$
>
> is it possible to just have a code that extracts $208.60 instead?
>
> similarly,
>
> under this page
>
>
> http://investing.
>
> while using this code
>
> =RCHGetTableCell(
> http://investing.
> on Assets"
>
> the result i was given is ''ReturnOnA
>
> is there a way to extract just 11.68%?
>
>
Tue Jun 4, 2013 6:44 pm (PDT) . Posted by:
"racecar00" racecar00
Hi Randy,
I am a new user - thank you for making smf available to us.
I searched the posts first but wanted some help incorporating a cell reference into the following link:
=RCHGetTableCell("http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=tpinx ",1,">Average Credit Quality")
As I understand it, the symbol argument would be moved to the end of the string. After trying symbol=&A1 as the alternative (as well as other permutations, no luck). Could you explain the solution for me?
Thanks in advance
I am a new user - thank you for making smf available to us.
I searched the posts first but wanted some help incorporating a cell reference into the following link:
=RCHGetTableCell(
As I understand it, the symbol argument would be moved to the end of the string. After trying symbol=&A1 as the alternative (as well as other permutations, no luck). Could you explain the solution for me?
Thanks in advance
Tue Jun 4, 2013 6:59 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Moving the ticker symbol to the end of the string just makes the
concatenation easier, but it's not necessary.
Suppose you have five strings or cell references to concatenate:
"...string1..."
A1
"...string2..."
A2
"...string3..."
You just need to put ampersand operators between each, so it would be:
"...string1..." & A1 & "...string2..." & A2 & "...string3..."
In your specific case, you have two separate items to concatenate:
"http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol= " & A1
If you do:
"http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=&A1 "
...you just have a single string, and the "&A1" is part of the entire
string literal instead of being a cell reference.
Some find it easier to understand if they use the EXCEL function:
=CONCATENATE("
http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol= ", A1)
On Tue, Jun 4, 2013 at 6:12 PM, racecar00 <agelhausen@gmail.com > wrote:
>
> I searched the posts first but wanted some help incorporating a cell
> reference into the following link:
>
> =RCHGetTableCell("
> http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol=tpinx ",1,">Average
> Credit Quality")
>
> As I understand it, the symbol argument would be moved to the end of the
> string. After trying symbol=&A1 as the alternative (as well as other
> permutations, no luck). Could you explain the solution for me?
>
concatenation easier, but it's not necessary.
Suppose you have five strings or cell references to concatenate:
"...string1.
A1
"...string2.
A2
"...string3.
You just need to put ampersand operators between each, so it would be:
"...string1.
In your specific case, you have two separate items to concatenate:
"http://quicktake.
If you do:
"http://quicktake.
...you just have a single string, and the "&A1" is part of the entire
string literal instead of being a cell reference.
Some find it easier to understand if they use the EXCEL function:
=CONCATENATE(
http://quicktake.
On Tue, Jun 4, 2013 at 6:12 PM, racecar00 <agelhausen@gmail.
>
> I searched the posts first but wanted some help incorporating a cell
> reference into the following link:
>
> =RCHGetTableCell(
> http://quicktake.
> Credit Quality"
>
> As I understand it, the symbol argument would be moved to the end of the
> string. After trying symbol=&A1 as the alternative (as well as other
> permutations, no luck). Could you explain the solution for me?
>
Tue Jun 4, 2013 7:42 pm (PDT) . Posted by:
"MLawson" mlawson3
I'd like to populate a typical financial statement spreadsheet, i.e., categories running from top to bottom along the left side of a table while the reporting periods run left to right across the top, using a function similar to the smfdowloadtableupdate (?name?). Before I start declaring arrays and piggy-backing off the code you have written, do you have an off-the-shelf version laying around? I am trying to avoid creating a spreadsheet with a few hundred formulas in it for 10 years worth of financial data.
Tidak ada komentar:
Posting Komentar