15 New Messages
Digest #2693
Messages
Sun Jul 14, 2013 5:13 am (PDT) . Posted by:
"happenedby" happenedby
Randy,
That construction works. Thanks!
Be aware that when I used OX as a source returning option ticker symbols (i.e. "z" code), the ticker symbols were returned with "^^" right after the stock symbol - not a problem with Yahoo though.
Thanks again.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> From the smfGetOptionQuotes() documentation:
>
> "The "ITMn" and "OTMn" strike price options may not work properly where
> Yahoo intermixes the listings of the quarter-end and month-end option
> expirations on the same page. A better alternative would be to use the
> smfGetOptionStrikes() function."
>
> It would actually apply to ANY option page where they intermix options,
> including the minis or adjusted options.
>
> Have you looked at the option item in the LINKS area of the group? I wrote
> up a step-by-step example of using some of the additional functions I wrote
> that can help on issues like this -- such as the smfGetOptionStrikes()
> function mentioned above.
>
> On Fri, Jul 12, 2013 at 5:21 AM, happenedby <happenedby@...> wrote:
>
> >
> > Answer is all of them.
> >
> > I am using using successive calls to OTM8 thru ITM8 strikes.
> >
> > Here is a couple of examples:
> >
> > =smfGetOptionQuotes("AAPL 10 2013 OTM8 Call","z",0,"Y") returns a regular
> > symbol (i.e. 100 share contract)
> > =smfGetOptionQuotes("AAPL 10 2013 OTM7 Call","z",0,"Y") returns a mini
> > symbol (i.e. 10 share contract)
> >
> > This is using your regular add-in (as opposed to the one in the
> > "work-in-progress folder". Haven't tried it yet.
> >
> > Sorting by Ticker to separate them out for now.
> >
>
That construction works. Thanks!
Be aware that when I used OX as a source returning option ticker symbols (i.e. "z" code), the ticker symbols were returned with "^^" right after the stock symbol - not a problem with Yahoo though.
Thanks again.
--- In smf_addin@yahoogrou
>
> From the smfGetOptionQuotes(
>
> "The "ITMn" and "OTMn" strike price options may not work properly where
> Yahoo intermixes the listings of the quarter-end and month-end option
> expirations on the same page. A better alternative would be to use the
> smfGetOptionStrikes
>
> It would actually apply to ANY option page where they intermix options,
> including the minis or adjusted options.
>
> Have you looked at the option item in the LINKS area of the group? I wrote
> up a step-by-step example of using some of the additional functions I wrote
> that can help on issues like this -- such as the smfGetOptionStrikes
> function mentioned above.
>
> On Fri, Jul 12, 2013 at 5:21 AM, happenedby <happenedby@
>
> >
> > Answer is all of them.
> >
> > I am using using successive calls to OTM8 thru ITM8 strikes.
> >
> > Here is a couple of examples:
> >
> > =smfGetOptionQuotes
> > symbol (i.e. 100 share contract)
> > =smfGetOptionQuotes
> > symbol (i.e. 10 share contract)
> >
> > This is using your regular add-in (as opposed to the one in the
> > "work-in-
> >
> > Sorting by Ticker to separate them out for now.
> >
>
Sun Jul 14, 2013 8:50 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
That's the ticker symbol format that OX uses...
On Sun, Jul 14, 2013 at 5:13 AM, happenedby <happenedby@yahoo.com > wrote:
>
> Be aware that when I used OX as a source returning option ticker symbols
> (i.e. "z" code), the ticker symbols were returned with "^^" right after the
> stock symbol - not a problem with Yahoo though.
>
On Sun, Jul 14, 2013 at 5:13 AM, happenedby <happenedby@yahoo.
>
> Be aware that when I used OX as a source returning option ticker symbols
> (i.e. "z" code), the ticker symbols were returned with "^^" right after the
> stock symbol - not a problem with Yahoo though.
>
Sun Jul 14, 2013 9:41 am (PDT) . Posted by:
"kkm2706" kkm2706
Hi,
I have two questions about using the RCHGetYahooQuotes & smfGetOptionQuotes functions:
1. If I need to get quotes for 100 stocks + 100 options, is it more efficient to call these functions 100 times each or to create an array formula?
2. Every time I update any cell in my excel, it looks like the RCH and/or smf functions get called. How do I stop this behavior?
Also, I am not using the refresh (NOW()) parameter when I am calling these functions.
Thanks,
Krishna
I have two questions about using the RCHGetYahooQuotes & smfGetOptionQuotes functions:
1. If I need to get quotes for 100 stocks + 100 options, is it more efficient to call these functions 100 times each or to create an array formula?
2. Every time I update any cell in my excel, it looks like the RCH and/or smf functions get called. How do I stop this behavior?
Also, I am not using the refresh (NOW()) parameter when I am calling these functions.
Thanks,
Krishna
Sun Jul 14, 2013 9:55 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
RCHGetYahooQuotes() is MUCH more efficient if you gather all the data with
one array-entered function. That's because it grabs a CSV file from Yahoo
based on the parameters you pass. So the function only needs to go to the
Internet once to grab the data and parse it out into the range you've given
it. If you enter the function cell by cell, EACH cell needs to go to the
Internet to get its data. Very slow if you're getting hundreds of data
items, that you could have gotten with a single Internet access.
However, smfGetOptionQuotes() ends up using the RCHGetTableCell() function
for each data item, so there's really no advantage to array-entering the
function. It will still need to go out and get each unique web page you're
accessing, to scrape the data. I only set it up to be array-entered for
consistency's sake. Also, note that to update the smfGetOptionQuotes()
data, you'll need to run the smfForceRecalculation macro (see LINKS area of
group for more info). That's because the macro is needed to purge all saved
web pages and get fresh ones from the Internet.
If your functions are recalculating every time you change something, it's
because you made them volatile in some way (e.g. adding the NOW() function
to the invocation). In general, the add-in functions are non-volatile and
will not recalculate unless one of their parameters changes value (which is
what NOW() is doing). And it really only works with RCHGetYahooQuotes(),
since the other functions generally extract data from a saved web page, so
for those you do need to run the smfForceRecalculation macro to force new
web pages to be retrieved.
If they are updating without you using the NOW() function, something
strange is going on. One other way for non-volatile functions to
recalculate is if you're inserting or deleting rows or columns. But if you
just change a cell value, non of the non-volatile add-in functions should
be recalculating. Are they somehow referring to a cell with a volatile
function?
I always use the smfForceRecalculation macro to do all of my updates. I
have it as a button on my Quick Access Toolbar.
On Sun, Jul 14, 2013 at 9:41 AM, kkm2706 <kkm5848@gmail.com > wrote:
>
> I have two questions about using the RCHGetYahooQuotes &
> smfGetOptionQuotes functions:
>
> 1. If I need to get quotes for 100 stocks + 100 options, is it more
> efficient to call these functions 100 times each or to create an array
> formula?
>
> 2. Every time I update any cell in my excel, it looks like the RCH and/or
> smf functions get called. How do I stop this behavior?
>
> Also, I am not using the refresh (NOW()) parameter when I am calling these
> functions.
>
one array-entered function. That's because it grabs a CSV file from Yahoo
based on the parameters you pass. So the function only needs to go to the
Internet once to grab the data and parse it out into the range you've given
it. If you enter the function cell by cell, EACH cell needs to go to the
Internet to get its data. Very slow if you're getting hundreds of data
items, that you could have gotten with a single Internet access.
However, smfGetOptionQuotes(
for each data item, so there's really no advantage to array-entering the
function. It will still need to go out and get each unique web page you're
accessing, to scrape the data. I only set it up to be array-entered for
consistency&#
data, you'll need to run the smfForceRecalculati
group for more info). That's because the macro is needed to purge all saved
web pages and get fresh ones from the Internet.
If your functions are recalculating every time you change something, it's
because you made them volatile in some way (e.g. adding the NOW() function
to the invocation). In general, the add-in functions are non-volatile and
will not recalculate unless one of their parameters changes value (which is
what NOW() is doing). And it really only works with RCHGetYahooQuotes(
since the other functions generally extract data from a saved web page, so
for those you do need to run the smfForceRecalculati
web pages to be retrieved.
If they are updating without you using the NOW() function, something
strange is going on. One other way for non-volatile functions to
recalculate is if you're inserting or deleting rows or columns. But if you
just change a cell value, non of the non-volatile add-in functions should
be recalculating. Are they somehow referring to a cell with a volatile
function?
I always use the smfForceRecalculati
have it as a button on my Quick Access Toolbar.
On Sun, Jul 14, 2013 at 9:41 AM, kkm2706 <kkm5848@gmail.
>
> I have two questions about using the RCHGetYahooQuotes &
> smfGetOptionQuotes functions:
>
> 1. If I need to get quotes for 100 stocks + 100 options, is it more
> efficient to call these functions 100 times each or to create an array
> formula?
>
> 2. Every time I update any cell in my excel, it looks like the RCH and/or
> smf functions get called. How do I stop this behavior?
>
> Also, I am not using the refresh (NOW()) parameter when I am calling these
> functions.
>
Sun Jul 14, 2013 10:13 am (PDT) . Posted by:
"kkm2706" kkm2706
This is very helpful.
It turns out that I had a hidden worksheet that had the following code for a number of stocks/options:
=IF(Q32="","",IFERROR(MSNStockQuote.Functions.MSNStockQuote($A32,"Last Price","US"),RCHGetYahooQuotes(A32,"l1")))
=IFERROR(IF(Q12="","",IF(I12="buy",-1,1)*IFERROR(MSNStockQuote.Functions.MSNStockQuote($D12,"Last Price","US"),smfGetOptionQuotes($E12,"l1"))),"")
This sheet was a backup from when I had just started using the smf/RCH functions (and removing the MSNStockQuote functions).
Since MSNStockQuote is now broken, my guess is that it may somehow have made the RCH/smf calls volatile but not sure.
When I deleted the worksheet, the problem went away.
Best Regards,
Krishna
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> RCHGetYahooQuotes() is MUCH more efficient if you gather all the data with
> one array-entered function. That's because it grabs a CSV file from Yahoo
> based on the parameters you pass. So the function only needs to go to the
> Internet once to grab the data and parse it out into the range you've given
> it. If you enter the function cell by cell, EACH cell needs to go to the
> Internet to get its data. Very slow if you're getting hundreds of data
> items, that you could have gotten with a single Internet access.
>
> However, smfGetOptionQuotes() ends up using the RCHGetTableCell() function
> for each data item, so there's really no advantage to array-entering the
> function. It will still need to go out and get each unique web page you're
> accessing, to scrape the data. I only set it up to be array-entered for
> consistency's sake. Also, note that to update the smfGetOptionQuotes()
> data, you'll need to run the smfForceRecalculation macro (see LINKS area of
> group for more info). That's because the macro is needed to purge all saved
> web pages and get fresh ones from the Internet.
>
> If your functions are recalculating every time you change something, it's
> because you made them volatile in some way (e.g. adding the NOW() function
> to the invocation). In general, the add-in functions are non-volatile and
> will not recalculate unless one of their parameters changes value (which is
> what NOW() is doing). And it really only works with RCHGetYahooQuotes(),
> since the other functions generally extract data from a saved web page, so
> for those you do need to run the smfForceRecalculation macro to force new
> web pages to be retrieved.
>
> If they are updating without you using the NOW() function, something
> strange is going on. One other way for non-volatile functions to
> recalculate is if you're inserting or deleting rows or columns. But if you
> just change a cell value, non of the non-volatile add-in functions should
> be recalculating. Are they somehow referring to a cell with a volatile
> function?
>
> I always use the smfForceRecalculation macro to do all of my updates. I
> have it as a button on my Quick Access Toolbar.
>
> On Sun, Jul 14, 2013 at 9:41 AM, kkm2706 <kkm5848@...> wrote:
>
> >
> > I have two questions about using the RCHGetYahooQuotes &
> > smfGetOptionQuotes functions:
> >
> > 1. If I need to get quotes for 100 stocks + 100 options, is it more
> > efficient to call these functions 100 times each or to create an array
> > formula?
> >
> > 2. Every time I update any cell in my excel, it looks like the RCH and/or
> > smf functions get called. How do I stop this behavior?
> >
> > Also, I am not using the refresh (NOW()) parameter when I am calling these
> > functions.
> >
>
It turns out that I had a hidden worksheet that had the following code for a number of stocks/options:
=IF(Q32="
=IFERROR(IF(
This sheet was a backup from when I had just started using the smf/RCH functions (and removing the MSNStockQuote functions).
Since MSNStockQuote is now broken, my guess is that it may somehow have made the RCH/smf calls volatile but not sure.
When I deleted the worksheet, the problem went away.
Best Regards,
Krishna
--- In smf_addin@yahoogrou
>
> RCHGetYahooQuotes(
> one array-entered function. That's because it grabs a CSV file from Yahoo
> based on the parameters you pass. So the function only needs to go to the
> Internet once to grab the data and parse it out into the range you've given
> it. If you enter the function cell by cell, EACH cell needs to go to the
> Internet to get its data. Very slow if you're getting hundreds of data
> items, that you could have gotten with a single Internet access.
>
> However, smfGetOptionQuotes(
> for each data item, so there's really no advantage to array-entering the
> function. It will still need to go out and get each unique web page you're
> accessing, to scrape the data. I only set it up to be array-entered for
> consistency&#
> data, you'll need to run the smfForceRecalculati
> group for more info). That's because the macro is needed to purge all saved
> web pages and get fresh ones from the Internet.
>
> If your functions are recalculating every time you change something, it's
> because you made them volatile in some way (e.g. adding the NOW() function
> to the invocation). In general, the add-in functions are non-volatile and
> will not recalculate unless one of their parameters changes value (which is
> what NOW() is doing). And it really only works with RCHGetYahooQuotes(
> since the other functions generally extract data from a saved web page, so
> for those you do need to run the smfForceRecalculati
> web pages to be retrieved.
>
> If they are updating without you using the NOW() function, something
> strange is going on. One other way for non-volatile functions to
> recalculate is if you're inserting or deleting rows or columns. But if you
> just change a cell value, non of the non-volatile add-in functions should
> be recalculating. Are they somehow referring to a cell with a volatile
> function?
>
> I always use the smfForceRecalculati
> have it as a button on my Quick Access Toolbar.
>
> On Sun, Jul 14, 2013 at 9:41 AM, kkm2706 <kkm5848@
>
> >
> > I have two questions about using the RCHGetYahooQuotes &
> > smfGetOptionQuotes functions:
> >
> > 1. If I need to get quotes for 100 stocks + 100 options, is it more
> > efficient to call these functions 100 times each or to create an array
> > formula?
> >
> > 2. Every time I update any cell in my excel, it looks like the RCH and/or
> > smf functions get called. How do I stop this behavior?
> >
> > Also, I am not using the refresh (NOW()) parameter when I am calling these
> > functions.
> >
>
Sun Jul 14, 2013 10:29 am (PDT) . Posted by:
"kkm2706" kkm2706
I consolidated my stocks/options into a single pivot table so that I can call the RCH functions as a array formula.
I than had to create a refreshAll macro to refresh all the pivot tables to include any new rows that I add in my stock table. I would like to call the smfForceRecalculation within this function but I am not sure why the below code isn't working. The call to smfForceRecalculation gives Runtime error 424.
Sub refreshAll()
ThisWorkbook.refreshAll
Call RCH_Stock_Market_Functions.modUtilities.smfForceRecalculation
End Sub
Does anyone know what I am doing wrong?
Krishna
I than had to create a refreshAll macro to refresh all the pivot tables to include any new rows that I add in my stock table. I would like to call the smfForceRecalculati
Sub refreshAll()
ThisWorkbook.
Call RCH_Stock_Market_
End Sub
Does anyone know what I am doing wrong?
Krishna
Sun Jul 14, 2013 10:52 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I usually just set up the add-in as a reference library for the VBE project
and then do:
Call smfForceRecalculation
I've never seen a call done the way you're doing it. Not that it wouldn't
work. Just never saw it that way.
On Sun, Jul 14, 2013 at 10:29 AM, kkm2706 <kkm5848@gmail.com > wrote:
> I consolidated my stocks/options into a single pivot table so that I can
> call the RCH functions as a array formula.
>
> I than had to create a refreshAll macro to refresh all the pivot tables to
> include any new rows that I add in my stock table. I would like to call
> the smfForceRecalculation within this function but I am not sure why the
> below code isn't working. The call to smfForceRecalculation gives Runtime
> error 424.
>
>
>
> Sub refreshAll()
> ThisWorkbook.refreshAll
> Call RCH_Stock_Market_Functions.modUtilities.smfForceRecalculation
> End Sub
>
> Does anyone know what I am doing wrong?
>
and then do:
Call smfForceRecalculati
I've never seen a call done the way you're doing it. Not that it wouldn't
work. Just never saw it that way.
On Sun, Jul 14, 2013 at 10:29 AM, kkm2706 <kkm5848@gmail.
> I consolidated my stocks/options into a single pivot table so that I can
> call the RCH functions as a array formula.
>
> I than had to create a refreshAll macro to refresh all the pivot tables to
> include any new rows that I add in my stock table. I would like to call
> the smfForceRecalculati
> below code isn't working. The call to smfForceRecalculati
> error 424.
>
>
>
> Sub refreshAll()
> ThisWorkbook.
> Call RCH_Stock_Market_
> End Sub
>
> Does anyone know what I am doing wrong?
>
Sun Jul 14, 2013 11:02 am (PDT) . Posted by:
"kkm2706" kkm2706
Thanks. I didn't know to add it as a reference library (I am relatively new to VBA).
I got annoyed with refreshing the pivot tables, so that's why I had a macro to refresh them all with the click of a button.
Best Regards,
Krishna
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> I usually just set up the add-in as a reference library for the VBE project
> and then do:
>
> Call smfForceRecalculation
>
> I've never seen a call done the way you're doing it. Not that it wouldn't
> work. Just never saw it that way.
>
> On Sun, Jul 14, 2013 at 10:29 AM, kkm2706 <kkm5848@...> wrote:
>
> > I consolidated my stocks/options into a single pivot table so that I can
> > call the RCH functions as a array formula.
> >
> > I than had to create a refreshAll macro to refresh all the pivot tables to
> > include any new rows that I add in my stock table. I would like to call
> > the smfForceRecalculation within this function but I am not sure why the
> > below code isn't working. The call to smfForceRecalculation gives Runtime
> > error 424.
> >
> >
> >
> > Sub refreshAll()
> > ThisWorkbook.refreshAll
> > Call RCH_Stock_Market_Functions.modUtilities.smfForceRecalculation
> > End Sub
> >
> > Does anyone know what I am doing wrong?
> >
>
I got annoyed with refreshing the pivot tables, so that's why I had a macro to refresh them all with the click of a button.
Best Regards,
Krishna
--- In smf_addin@yahoogrou
>
> I usually just set up the add-in as a reference library for the VBE project
> and then do:
>
> Call smfForceRecalculati
>
> I've never seen a call done the way you're doing it. Not that it wouldn't
> work. Just never saw it that way.
>
> On Sun, Jul 14, 2013 at 10:29 AM, kkm2706 <kkm5848@
>
> > I consolidated my stocks/options into a single pivot table so that I can
> > call the RCH functions as a array formula.
> >
> > I than had to create a refreshAll macro to refresh all the pivot tables to
> > include any new rows that I add in my stock table. I would like to call
> > the smfForceRecalculati
> > below code isn't working. The call to smfForceRecalculati
> > error 424.
> >
> >
> >
> > Sub refreshAll()
> > ThisWorkbook.
> > Call RCH_Stock_Market_
> > End Sub
> >
> > Does anyone know what I am doing wrong?
> >
>
Sun Jul 14, 2013 11:06 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
That was one of the things I hated about pivot tables. These days, I
usually use my own table construction, using the EXCEL SUMPRODUCT()
function. Then, the tables are exactly as I design them, instead of in the
Pivot Table layout.
On Sun, Jul 14, 2013 at 11:02 AM, kkm2706 <kkm5848@gmail.com > wrote:
>
> I got annoyed with refreshing the pivot tables, so that's why I had a
> macro to refresh them all with the click of a button.
>
usually use my own table construction, using the EXCEL SUMPRODUCT()
function. Then, the tables are exactly as I design them, instead of in the
Pivot Table layout.
On Sun, Jul 14, 2013 at 11:02 AM, kkm2706 <kkm5848@gmail.
>
> I got annoyed with refreshing the pivot tables, so that's why I had a
> macro to refresh them all with the click of a button.
>
Sun Jul 14, 2013 12:17 pm (PDT) . Posted by:
"kkm2706" kkm2706
The only reason I am using pivot tables is to de-duplicate and filter my stock and option tickers. I figured it would be better from a performance perspective vs having lots of duplicates and having the smf/RCH functions get the same quote over and over again. Also, filtering removes the things that cause errors and really trips up the smf/RCH functions (esp. when used in an array formula).
Krishna
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> That was one of the things I hated about pivot tables. These days, I
> usually use my own table construction, using the EXCEL SUMPRODUCT()
> function. Then, the tables are exactly as I design them, instead of in the
> Pivot Table layout.
>
> On Sun, Jul 14, 2013 at 11:02 AM, kkm2706 <kkm5848@...> wrote:
>
> >
> > I got annoyed with refreshing the pivot tables, so that's why I had a
> > macro to refresh them all with the click of a button.
> >
>
Krishna
--- In smf_addin@yahoogrou
>
> That was one of the things I hated about pivot tables. These days, I
> usually use my own table construction, using the EXCEL SUMPRODUCT()
> function. Then, the tables are exactly as I design them, instead of in the
> Pivot Table layout.
>
> On Sun, Jul 14, 2013 at 11:02 AM, kkm2706 <kkm5848@
>
> >
> > I got annoyed with refreshing the pivot tables, so that's why I had a
> > macro to refresh them all with the click of a button.
> >
>
Sun Jul 14, 2013 11:04 am (PDT) . Posted by:
"Randy H" rharmelink
You really have to wade through the source code of the web page and the
JavaScript code to find the underlying sourced web pages for the
MorningStar dynamic web pages.
Right now, the only two static pages I know of with financial statements
data:
http://quotes.morningstar.com/ stock/c-financials?t=MSFT
<http://quotes.morningstar.com/stock/c-financials?t=MSFT >
http://financials.morningstar.com/financials/getFinancePart.html?t=MSFT
The first is very limited. The second is not a trivial extraction.
However, you can grab the export files from the web page you cite below
-- see:
http://finance.groups.yahoo.com/group/smf_addin/message/19776
PS: You need to send add-in questions to the Yahoo group. I have email
filters that direct them to a folder. I found this message in my SPAM
folder, which means it could easily have deleted without me ever seeing
it.
On Sun, Jul 14, 2013 at 10:21 AM, José Azevedo <jmazevedo@...
<mailto:jmazevedo@gmail.com > > wrote:
How find this links?
Example, I try at moment extract this:http://financials.morningstar.
com/cash-flow/cf.html?t=MSFT& region=USA&culture=en-us
<http://financials.morningstar.com/cash-flow/cf.html?t=MSFT®ion=USA&c\
ulture=en-us >
It is possible know link for it?
JavaScript code to find the underlying sourced web pages for the
MorningStar dynamic web pages.
Right now, the only two static pages I know of with financial statements
data:
http://quotes.
<http://quotes.
http://financials.
The first is very limited. The second is not a trivial extraction.
However, you can grab the export files from the web page you cite below
-- see:
http://finance.
PS: You need to send add-in questions to the Yahoo group. I have email
filters that direct them to a folder. I found this message in my SPAM
folder, which means it could easily have deleted without me ever seeing
it.
On Sun, Jul 14, 2013 at 10:21 AM, José Azevedo <jmazevedo@
<mailto:jmazevedo@gmail.
How find this links?
Example, I try at moment extract this:http://financials.
com/cash-flow/
<http://financials.
ulture=en-us
It is possible know link for it?
Sun Jul 14, 2013 12:08 pm (PDT) . Posted by:
"Lawrence" lawleesh
Hi Randy
If I want to retrieve Cash and Short Term Investments for 2012 from this page
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=G05:SES&subview=BalanceSheet
what formula should I key into Excel?
thanks
LL
If I want to retrieve Cash and Short Term Investments for 2012 from this page
http://markets.
what formula should I key into Excel?
thanks
LL
Sun Jul 14, 2013 12:20 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Easy peasy lemon-squeezy:
=RCHGetTableCell("
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=G05:SES&subview=BalanceSheet ",1,">Cash
and Short Term Investments")
...will get the first column of data. It may not always be for 2012 though.
It will depend on the company's fiscal year and when they announce their
results (and when FT posts those results).
On Sun, Jul 14, 2013 at 12:08 PM, Lawrence <lawrence.leesh@gmail.com > wrote:
>
> If I want to retrieve Cash and Short Term Investments for 2012 from this
> page
>
> http://markets.ft.com/research/Markets/Tearsheets/Financials?s=G05:SES&subview=BalanceSheet
> what formula should I key into Excel?
>
=RCHGetTableCell(
http://markets.
and Short Term Investments"
...will get the first column of data. It may not always be for 2012 though.
It will depend on the company'
results (and when FT posts those results).
On Sun, Jul 14, 2013 at 12:08 PM, Lawrence <lawrence.leesh@
>
> If I want to retrieve Cash and Short Term Investments for 2012 from this
> page
>
> http://markets.
> what formula should I key into Excel?
>
Sun Jul 14, 2013 9:03 pm (PDT) . Posted by:
"Lawrence" lawleesh
Thanks Randy. I managed to get the figure.
If I want the formula to get "1", "G05.SI" and "Cash and Short Term Investments" from cell values, is that possible? So when I change the cells it would retrieve another piece of info from another stock from another year.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Easy peasy lemon-squeezy:
>
> =RCHGetTableCell("
> http://markets.ft.com/research/Markets/Tearsheets/Financials?s=G05:SES&subview=BalanceSheet ",1,">Cash
> and Short Term Investments")
>
> ...will get the first column of data. It may not always be for 2012 though.
> It will depend on the company's fiscal year and when they announce their
> results (and when FT posts those results).
>
> On Sun, Jul 14, 2013 at 12:08 PM, Lawrence <lawrence.leesh@...> wrote:
>
> >
> > If I want to retrieve Cash and Short Term Investments for 2012 from this
> > page
> >
> > http://markets.ft.com/research/Markets/Tearsheets/Financials?s=G05:SES&subview=BalanceSheet
> > what formula should I key into Excel?
> >
>
If I want the formula to get "1"
--- In smf_addin@yahoogrou
>
> Easy peasy lemon-squeezy:
>
> =RCHGetTableCell(
> http://markets.
> and Short Term Investments"
>
> ...will get the first column of data. It may not always be for 2012 though.
> It will depend on the company'
> results (and when FT posts those results).
>
> On Sun, Jul 14, 2013 at 12:08 PM, Lawrence <lawrence.
>
> >
> > If I want to retrieve Cash and Short Term Investments for 2012 from this
> > page
> >
> > http://markets.
> > what formula should I key into Excel?
> >
>
Sun Jul 14, 2013 9:38 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Those are just be normal EXCEL processing, so sure. You need to concatenate
a string to change the URL, and I usually move the parameter for the
concatenation to the end of the URL so it's just a single concatenation
instead of two. So, something like:
=RCHGetTableCell("
http://markets.ft.com/research/Markets/Tearsheets/Financials?subview=BalanceSheet&s= "&B3,C3,">Cash
and Short Term Investments")
...where B3 is your ticker symbol and C3 is which offset of year you want.
On Sun, Jul 14, 2013 at 9:03 PM, Lawrence <lawrence.leesh@gmail.com > wrote:
>
> If I want the formula to get "1", "G05.SI" and "Cash and Short Term
> Investments" from cell values, is that possible? So when I change the cells
> it would retrieve another piece of info from another stock from another
> year.
>
a string to change the URL, and I usually move the parameter for the
concatenation to the end of the URL so it's just a single concatenation
instead of two. So, something like:
=RCHGetTableCell(
http://markets.
and Short Term Investments"
...where B3 is your ticker symbol and C3 is which offset of year you want.
On Sun, Jul 14, 2013 at 9:03 PM, Lawrence <lawrence.leesh@
>
> If I want the formula to get "1"
> Investments" from cell values, is that possible? So when I change the cells
> it would retrieve another piece of info from another stock from another
> year.
>
Tidak ada komentar:
Posting Komentar