Minggu, 15 Februari 2015

[smf_addin] Digest Number 3324

9 Messages

Digest #3324

Messages

Sat Feb 14, 2015 5:40 am (PST) . Posted by:

rjemery7

1) I might be interested in total return later. By what automated method do you recommend adding dividend data?

2) smfConvertData() appears to be an undocumented function in Version 2.1.2014.01.30. I can find no page that describes it and its options. If there is one, please advise where I might find it.

3) The combination of smfConvertData(smfGetTagContent()) returns a value but also with an unwanted hyperlink. How can I have the result be a number only?

Sat Feb 14, 2015 7:59 am (PST) . Posted by:

"Randy Harmelink" rharmelink

On Sat, Feb 14, 2015 at 6:40 AM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> 1) I might be interested in total return later. By what automated method
> do you recommend adding dividend data?
>

No suggestions to offer. That's why I use Yahoo adjusted prices, avoid
using index quotes, and avoid using Google historical quotes.

> 2) smfConvertData() appears to be an undocumented function in Version
> 2.1.2014.01.30. I can find no page that describes it and its options. If
> there is one, please advise where I might find it.
>

There's really nothing to document, other than what it's name indicates. It
converts data. I used to offer the suggestion of:

0+smfGetTagContent()

...to convert the text returned into a numeric value. However, if the text
returned by the function is not numeric, that results in #VALUE!. And I'd
get questions about the wrong error. But smfConvertData() will leave the
text "as is" so the true issue is shown.

3) The combination of smfConvertData(smfGetTagContent()) returns a value
> but also with an unwanted hyperlink. How can I have the result be a number
> only?
>

Functions can only return values. Are you saying it's returning a URL as
the text in the cell? Or that the cell has a hyperlink attribute on it? If
the latter, that's a format you added to the cell. A function can't add
formats to a cell.

Sat Feb 14, 2015 5:30 pm (PST) . Posted by:

rjemery7

Randy,

How would you modify your function to retrieve the close:

1) on the first trading day of the year?
2) any specific trading day?

Sat Feb 14, 2015 5:52 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

For (1), you'd need to know the date ahead of time and set the starting and
ending dates to that date.

For (2), just do the same as we did for year-end. Set the ending date to
the specific date and the starting date at least 4 oir 5 days earlier (to
account for possible weekends and holidays). That would give you the latest
trading date that is no later than the ending date.

But I'm just guessing. You would need to try some applicable examples to
find out the specifics.

On Sat, Feb 14, 2015 at 6:30 PM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> How would you modify your function to retrieve the close:
>
> 1) on the first trading day of the year?
> 2) any specific trading day?
>

Sat Feb 14, 2015 8:52 pm (PST) . Posted by:

rjemery7

Your responses have been extremely helpful and useful.

I would like to take your formula one step forward by parametrizing the symbol and dates. If the symbol were in $B14 and the end_date only in N$3, how would the formula be rewritten? The start_date would need to be the end_date minus five days calculated inline.

I've tried substitution, but the result is "Error".

Sat Feb 14, 2015 10:18 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I used different cells, but it would look something like:

=smfConvertData(smfGetTagContent("
https://www.google.com/finance/historical?q="&B6&"&startdate="&TEXT(C6-5,"mmm
dd, yyyy")&"&enddate="&TEXT(C6,"mmm dd, yyyy"),"td",5,">Volume"))

On Sat, Feb 14, 2015 at 9:52 PM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I would like to take your formula one step forward by parametrizing the
> symbol and dates. If the symbol were in $B14 and the end_date only in N$3,
> how would the formula be rewritten? The start_date would need to be the
> end_date minus five days calculated inline.
>
> I've tried substitution, but the result is "Error".
>

Sun Feb 15, 2015 1:48 am (PST) . Posted by:

rjemery7

What modification would need to be made to accommodate absolute addressing, as in $B6 or B$6 or $B$6?

Sun Feb 15, 2015 1:59 am (PST) . Posted by:

"Randy Harmelink" rharmelink

The cell references can be of any type you desire. That's just normal EXCEL.

On Sun, Feb 15, 2015 at 2:48 AM, rjemery@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> What modification would need to be made to accommodate absolute
> addressing, as in $B6 or B$6 or $B$6?
>

Sat Feb 14, 2015 5:56 am (PST) . Posted by:

"Manish Sahajwani" manish_sahajwani

Hi Randy,

First I would like to thank you for publishing and maintaining such as
great add-in. It's really useful. I run a small site for finance
professionals, and just wrote about the add-in over there to let my
followers know about it.

Here's the link:

http://financetrain.com/stock-market-functions-smf-add-get-financial-data-excel/

I've linked to the add-in download page so that people can follow and
download the same.

Thanks,
Manish
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 03.18

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)