Kamis, 25 September 2014

[smf_addin] Digest Number 3189

8 Messages

Digest #3189
1b
Re: Date of 52-week High and Low by "Randy Harmelink" rharmelink
1d
Re: Date of 52-week High and Low by "Randy Harmelink" rharmelink
2b
Re: quick formula help to grab from cell by "Randy Harmelink" rharmelink
3a
Stock price data on a particular Day by "अनन्त:" ritchemie
3b
Re: Stock price data on a particular Day by "Randy Harmelink" rharmelink

Messages

Wed Sep 24, 2014 7:18 am (PDT) . Posted by:

rjemery7

How do I enter a table array formula?

1) Specifically for Row 2 in Column M, I have:

=SMFPricesBetween(A2,DATE(2013,9,19),DATE(2014,9,19),"06050403")

I wish to replicate that formula in subsequent rows, where A2 would change to A3, then A4, etc. Then for the 500 rows in question, make it all a table array. How do I do that?

If I replicate the formula, all I get is the A2 parameter in all rows.

2) What is the limit of the number of SMF calls that can be made from within one table array?

Wed Sep 24, 2014 7:54 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

You can't make it a table array. The function only returns a single row of
data, for a single ticker symbol.

You just need to enter it for one row, then copy that row down to get it to
work for the other rows. It will adjust your cell reference when you copy
the formula down.

On Wed, Sep 24, 2014 at 7:18 AM, rjemery@... wrote:

>
> How do I enter a table array formula?
>
> 1) Specifically for Row 2 in Column M, I have:
>
> =SMFPricesBetween(A2,DATE(2013,9,19),DATE(2014,9,19),"06050403")
>
> I wish to replicate that formula in subsequent rows, where A2 would change
> to A3, then A4, etc. Then for the 500 rows in question, make it all a
> table array. How do I do that?
>
> If I replicate the formula, all I get is the A2 parameter in all rows.
>
> 2) What is the limit of the number of SMF calls that can be made from
> within one table array?
>

Wed Sep 24, 2014 12:30 pm (PDT) . Posted by:

rjemery7

Yes, that certainly works, but then I obtain only the first data item in the specified fields. I could make each row an array, but to repeat that exercise for each of 500 rows would be tedious.

I could issue a separate function call for each of the four data items sought, but that makes for 2,000 calls, which seems inefficient.

What do you suggest?

Wed Sep 24, 2014 12:43 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I don't know why you only get the first data item -- you array-enter the
row of those four items. Then you'd need to copy that array of four cells
to the other 499 rows.

If I had made the function array-entered as a table, it wouldn't have made
it any more efficient. It still needs to get EACH set of historical quotes
to extract the four data items. So you would have 500 calls to the Internet
no matter what. I think it's easier dealing with 500 rows of data than a
table, because at least then you can sort the 500 rows if desired.

I typically only use array-entered functions where it makes the number of
the calls to the Internet less. Because it's such a pain to deal with
array-entered data. Can't make changes to individual fields. Can't sort.
Difficult to update the formula. Etc.

In this case, array-entering as a row meant one Internet call could
retrieve all 10 data items (or a subset of them).

On Wed, Sep 24, 2014 at 12:30 PM, rjemery@... wrote:

>
> Yes, that certainly works, but then I obtain only the first data item in
> the specified fields. I could make each row an array, but to repeat that
> exercise for each of 500 rows would be tedious.
>
> I could issue a separate function call for each of the four data items
> sought, but that makes for 2,000 calls, which seems inefficient.
>
> What do you suggest?
>

Wed Sep 24, 2014 1:38 pm (PDT) . Posted by:

friedman_steve

Using Ycharts link below, i'm having trouble entering formula to grab
1) the price quote
2) the price change for day


Also, is there any trick to help with the formula creation?


Thanks,
Steve


http://ycharts.com/companies/JBL http://ycharts.com/companies/JBL



Wed Sep 24, 2014 2:00 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The only "trick" is that you need to examine the source code to see how to
extract. In this case, price, change in price, and percent change:

=smfConvertData(smfGetTagContent("http://ycharts.com/companies/JBL
","span",-1,"upDn"))
=smfConvertData(smfGetTagContent("http://ycharts.com/companies/JBL
","span",2,"upDn"))
=smfConvertData(smfGetTagContent("http://ycharts.com/companies/JBL
","span",3,"upDn"))

On Wed, Sep 24, 2014 at 1:38 PM, friedman_steve@... wrote:

> Using Ycharts link below, i'm having trouble entering formula to grab
>
> 1) the price quote
>
> 2) the price change for day
>
> Also, is there any trick to help with the formula creation?
>

Wed Sep 24, 2014 4:04 pm (PDT) . Posted by:

"अनन्त:" ritchemie

randy,
on many website I see stock related article however the date is sometimes
old.
Is there a way to get the price data for any day, I am interested in past 1
to 2 years only.
I remember modifying a spreadsheet which pulls data from yahoo, but could
not find it.

Thanks for your help
Anant

Wed Sep 24, 2014 4:21 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

For something like that, I use smfPricesByDates() so that I can get
adjusted dates and total returns.

However, if you want the raw data, just use RCHGetYahooHistory().

On Wed, Sep 24, 2014 at 4:03 PM, अनन्त: 1998chemie@... wrote:

>
> on many website I see stock related article however the date is sometimes
> old.
> Is there a way to get the price data for any day, I am interested in past
> 1 to 2 years only.
> I remember modifying a spreadsheet which pulls data from yahoo, but could
> not find it.
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Poskan Komentar