Selasa, 23 Mei 2017

[smf_addin] Digest Number 4028[1 Attachment]

15 Messages

Digest #4028
1a
1b
Re: installation. by "Randy Harmelink" rharmelink
2
Price by dates error by dzi6ewzrdnxj7wkxo5f6mlhps7ydjxexnjzqjzdg
3a
Re: force calculate. by "Randy Harmelink" rharmelink
5a
Re: Another question about smfGetYahooHistory by "Kermit W. Prather" kermitpra
5b
Re: Another question about smfGetYahooHistory by "Randy Harmelink" rharmelink
6a
Simplest function to return last dividend/share & pay date for ETFs by "Praedial Serf" akhiqg4v4nvkwldqgxmyxmejakyrix2iboxnk7fq
7a
smfgetyahoohistory function by "Jeff Sanders" jeffsand2
7b
Re: smfgetyahoohistory function by "Randy Harmelink" rharmelink
8a
SMF Convert data by stumpy_chris
8b
Re: SMF Convert data by stumpy_chris
8c
Re: SMF Convert data by "Randy Harmelink" rharmelink

Messages

Tue May 23, 2017 1:43 pm (PDT) . Posted by:

"Wasi"

Hi,

When I typed in =RCHGetElementNumber("Version"), excel responded as: Stock Market Functions add-in, Version 2.1.2017.04.17 (C:\SMF Add In; Windows (32-bit) NT 10.00; 16.0; ; ; 1)

I got this spreadsheet from internet called "Template Morningstar" I used to get financials for aapl. In cell C10, the #Name? error has this formulae:

'C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!smfGetCSVFile("http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t="&C2&"&reportType="&C3&"&period="&C4&"&dataType="&C5&"&order="&C6&"&columnYear="&C7&"&rounding=3&denominatorView="&C8)

Besides, when I try to retrieve financial using "Get External Data" the web page for any stock cannot be displayed, saying "This page cannot be displayed" Any thoughts on that. I use windows 10, excel 2016.

Your guidance is appreciated.

Thanks,

wasi

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, May 22, 2017 3:20 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] installation.

So what did you get with:

=RCHGetElementNumber("Version")

What formula are you getting #NAME? results for?

On Mon, May 22, 2017 at 12:10 PM, 'Wasi' wasi.rahim@

​...wrote:

I have download the new version of smf addin. But still I am getting errors, like "Name" I have confirmed that the addin is installed though.

Any suggestions?

Thanks

wasi

From: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> [mailto:smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> ]
Sent: Monday, May 22, 2017 1:14 PM
To: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com>
Subject: Re: [smf_addin] Re: Replace RCHGetYahooHistory call

See:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32237

On Mon, May 22, 2017 at 10:11 AM, lewglenn@

​...wrote:

What's the url for the 5/21 version?

Tue May 23, 2017 3:59 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The MorningStar CSV files were documented here:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/19776

Based on that, both of these currently work for me:

=smfGetCSVFile("
http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=GOOG&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw
")

=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=GOOG")

Because you have a location in front of the formula, I would say you have
location errors. You should have gotten a warning when opening the file.
They can usually be easily fixed with the smfFixLinks macro. Note that the
file location returned by your "Version" function is different than the
file location on your smfGetCSVFile() function. You have placed the add-in
a different folder than where it was when I saved the template. No
problems. Just use the smfFixLinks macro to fix the links, save the file,
and it should re-open fine. Unless you move the add-in after you save it. :)

I currently use the "C:\SMF Add-in" folder on my two machines. You'll be
consistent with all the templates on the website if you use that folder.
Otherwise, you'll need to do the smfFixLinks process above each time you
open template from the website.

On Tue, May 23, 2017 at 11:16 AM, 'Wasi' wasi.rahim@
​...wrote:

>
>
> When I typed in =RCHGetElementNumber("Version"), excel responded as: Stock
> Market Functions add-in, Version 2.1.2017.04.17 (C:\SMF Add In; Windows
> (32-bit) NT 10.00; 16.0; ; ; 1)
>
> I got this spreadsheet from internet called "Template Morningstar" I used
> to get financials for aapl. In cell C10, the #Name? error has this
> formulae:
>
> 'C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!smfGetCSVFile("
> http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=
> "&C2&"&reportType="&C3&"&period="&C4&"&dataType="&C5&
> "&order="&C6&"&columnYear="&C7&"&rounding=3&denominatorView="&C8)
>
> Besides, when I try to retrieve financial using "Get External Data" the
> web page for any stock cannot be displayed, saying "This page cannot be
> displayed" Any thoughts on that. I use windows 10, excel 2016.
>
>
>
>

Tue May 23, 2017 1:45 pm (PDT) . Posted by:

dzi6ewzrdnxj7wkxo5f6mlhps7ydjxexnjzqjzdg

Hello Randy, all,


I've a problem with the smfPricesByDates-Example-Stocks-By-Dates http://ogres-crypt.com/SMF/Templates/smfPricesByDates-Example-Stocks-By-Dates.xls SMF file.
The file returns for all fields and tickers an #N/A error.


I'm using Excel 2007. Other details: Stock Market Functions add-in, Version 2.1.2017.05.03 (C:\SMF Add-IN; Windows (32-bit) NT 6.02; 12.0; ; ; 31)


I've attached a screenshot of the errors.


Could you please advise on what to do?
Many thanks in advance!


Regards,
Rico

Attachment(s) from
1 of 1 Photo(s)

Tue May 23, 2017 2:32 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The smfForceRecalculation macro is part of the add-in.

On Tue, May 23, 2017 at 3:57 AM, nanofanboyyeahbaby@
​...wrote:

> Group has grown so large that I was unsuccessful at searching for this
> topic. New computer/new install. Can't remember the steps involved to
> import force calculate into macro any help would be much appreciated. I'm
> running excel 2010. thanks for the continued sort, Randy. Wish I knew how
> to do what you do. Always impressed.
>
>

Tue May 23, 2017 2:38 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You'll need the experimental beta version of the add-in for
smfPricesByDates() to work...

See:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32237

On Tue, May 23, 2017 at 11:16 AM, Rico Teikotte ricoteikotte@
​...wrote:

>
> I've a problem with the smfPricesByDates-Example-Stocks-By-Dates
> <http://ogres-crypt.com/SMF/Templates/smfPricesByDates-Example-Stocks-By-Dates.xls> SMF
> file.
> The file returns for all fields and tickers an #N/A error.
>
> I'm using Excel 2007. Other details: Stock Market Functions add-in,
> Version 2.1.2017.05.03 (C:\SMF Add-IN; Windows (32-bit) NT 6.02; 12.0; ; ;
> 31)
>
> I've attached a screenshot of the errors.
>
> Could you please advise on what to do?
> Many thanks in advance!
>
>

Tue May 23, 2017 2:58 pm (PDT) . Posted by:

"Kermit W. Prather" kermitpra

Randy has asked several times that users not use his tool to pull bulk amounts of data.

If I am reading you correctly your are asking for up to 20,000 rows.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Tuesday, May 23, 2017 1:12 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Another question about smfGetYahooHistory


I was previously using the call:

Range("A1:B20000") = RCHGetYahooHistory("IEF", , , , , , , , "DA", 1, 1, 1, 20000, 2) to get Dates (in chronological order in Column A and Adjusted Close in column B (with headers in A1 and B1).

With the switch to smfGetYahooHistory I used:

Range("A1:B20000") = smfGetYahooHistory("IEF", , , "d", "dc", 0)

This gave me un-formatted dates in Column A (which only gets me the dates I want if I formally format the column as Date) and adjusted close in column B. In addition, both columns are now in reverse chronological order and there are no headers, with data beginning in row 1 instead of row 2.

What parameter settings should be used in the smfGetYahooHistory call to get results identical to those with the old RCHGetYahooHistory call?

Many thanks for your help.


Tue May 23, 2017 4:11 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

This one is not so much of an issue. It's still a single Internet request.
That's quite different than asking for 20 days worth of data on 1000
different stocks, or using individual smfPricesByDates() functions to grab
1000 individual dates, one at a time.

On Tue, May 23, 2017 at 2:55 PM, 'Kermit W. Prather' kermitp@
​...wrote:

>
> Randy has asked several times that users not use his tool to pull bulk
> amounts of data.
>
>
>
> If I am reading you correctly your are asking for up to 20,000 rows.
>
> *From:* smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
> *Sent:* Tuesday, May 23, 2017 1:12 PM
> *To:* smf_addin@yahoogroups.com
> *Subject:* [smf_addin] Another question about smfGetYahooHistory
>
>
> I was previously using the call:
>
>
> *Range("A1:B20000") = RCHGetYahooHistory("IEF", , , , , , , , "DA", 1, 1,
> 1, 20000, 2) to get Dates (in chronological order in Column A and Adjusted
> Close in column B (with headers in A1 and B1).*
> With the switch to smfGetYahooHistory I used:
>
> *Range("A1:B20000") = smfGetYahooHistory("IEF", , , "d", "dc", 0)*
>
> This gave me un-formatted dates in Column A (which only gets me the dates
> I want if I formally format the column as Date) and adjusted close in
> column B. In addition, both columns are now in *reverse chronological
> orde*r and there are no headers, with data beginning in row 1 instead of
> row 2.
>
> What parameter settings should be used in the smfGetYahooHistory call to
> get results identical to those with the old RCHGetYahooHistory call?
>
>

Tue May 23, 2017 4:53 pm (PDT) . Posted by:

lewglenn

That works just fine. Thanks.

I know you're up to your eyeballs at the moment but any chance on providing an option that converts EXCEL serial sates to the string values that RCHGetYahooHistory used to provide? I think most folks will not find the EXCEL serial dates to be particularly useful since the function DateValue in vba doesn't recognize the EXCEL serial dates in the argument. I realize it's possible to write a macro segment that does the conversion but it would be nice if the smfGetYahooHistory does it when it extracts the data.

Tue May 23, 2017 3:13 pm (PDT) . Posted by:

"Praedial Serf" akhiqg4v4nvkwldqgxmyxmejakyrix2iboxnk7fq

Hi Randy,

I just joined. I am confused by the many options. My goal is simple: one
Excel cell to return the last dividend per share figure for a specific ETF
and another to return the pay date for that dividend. I would appreciate
some guidance on the simplest formula/source to return this information.
Thanks!

Tue May 23, 2017 4:40 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

That was tough, because the pay date on ETFs is a difficult one to find.
Try:

=smfGetTagContent("
http://performance.morningstar.com/Performance/stock/dividend-history.action?y=1&t=SPY",
"td",1,"Cash Dividend",,,,1)

=smfGetTagContent("
http://performance.morningstar.com/Performance/stock/dividend-history.action?y=1&t=SPY",
"td",-2,"Cash Dividend")

On Tue, May 23, 2017 at 2:48 PM, Praedial Serf praedial.serf@
​...wrote:

> I just joined. I am confused by the many options. My goal is simple: one
> Excel cell to return the last dividend per share figure for a specific ETF
> and another to return the pay date for that dividend. I would appreciate
> some guidance on the simplest formula/source to return this information.
> Thanks!
>
>

Tue May 23, 2017 3:14 pm (PDT) . Posted by:

"Jeff Sanders" jeffsand2

I hate to sound stupid, but exactly where do I find the new addin with this
function. The latest one as of 5/3/17 doesn't seem to have it.

Thanks for the help.

Jeff Sanders

jeff@jeffsanders.net

Tue May 23, 2017 4:43 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

That's because it's an experimental *beta* function, not displayed on the
website pages, and should really only be used by those testing it. See:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32237

It may change before the official release.

On Tue, May 23, 2017 at 1:44 PM, 'Jeff Sanders' jeff@
​...wrote:

>
> I hate to sound stupid, but exactly where do I find the new addin with
> this function. The latest one as of 5/3/17 doesn't seem to have it.
>
>
>
>

Tue May 23, 2017 3:33 pm (PDT) . Posted by:

stumpy_chris

I am pulling fund quotes from Globe Advisor with the SMF Convert Data function.


My cells are showing a blank. Worked perfect until yesterday so wondering if I disabled something.

Tue May 23, 2017 3:35 pm (PDT) . Posted by:

stumpy_chris

Sorry.. Globe Investor.

Tue May 23, 2017 4:45 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The smfConvertData() function doesn't "pull" anything. It converts strings
into numbers.

Please cite the function invocation. I might also need the version of the
add-in you are using:

=RCHGetElementNumber("Version&quot;)

On Tue, May 23, 2017 at 3:33 PM, cschwartz@
​...wrote:

> I am pulling fund quotes from Globe Advisor with the SMF Convert Data
> function.
>
> My cells are showing a blank. Worked perfect until yesterday so
> wondering if I disabled something.
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 16.53

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)