Minggu, 05 November 2017

[smf_addin] Digest Number 4211

15 Messages

Digest #4211
1a
Re: Donation by johnross999
2.1
Re: smfGetPortfolioView() by "Michael Henry" midhenry1
2.2
Re: smfGetPortfolioView() by "Randy Harmelink" rharmelink
3a
Re: Can't download quotes by "V. Shankar" tarakayan
3b
Re: Can't download quotes by "Randy Harmelink" rharmelink
4a
Re: Google Finance by ridgebacksexcel
4b
Re: Google Finance by "Randy Harmelink" rharmelink
4c
Re: Google Finance by "Bernie Dudek" berniedudek
4d
Re: Google Finance by "Randy Harmelink" rharmelink
4e
Re: Google Finance by ridgebacksexcel
4f
Re: Google Finance by "Bernie Dudek" berniedudek
4g
Re: Google Finance by "Randy Harmelink" rharmelink
5.2

Messages

Sat Nov 4, 2017 11:11 pm (PDT) . Posted by:

"Michael Henry" midhenry1


That's interesting and it brings up the question of what the other parameter does (... 2,?,1)
_Michael 
Sent from Yahoo Mail on Android

On Sat, Nov 4, 2017 at 4:32 PM, Randy Harmelink rharmelink@gmail.com [smf_addin]<smf_addin@yahoogroups.com> wrote:  

Drop the last "1" parameter. It's what tells the function to put headers on.
=​smfGetYahooPortfolioView($B$4:$B$411,$C$2:$AJ$2) 
On Sat, Nov 4, 2017 at 2:51 PM, johnrr9@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:

I am wondering if there is a way using this function in the example sheet

=​​smfGetYahooPortfolioView($B$ 4:$B$411,$C$2:$AJ$2,,1)

and NOT displaying the headers in the first line?

#yiv9632311188 #yiv9632311188 -- #yiv9632311188ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9632311188 #yiv9632311188ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9632311188 #yiv9632311188ygrp-mkp #yiv9632311188hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9632311188 #yiv9632311188ygrp-mkp #yiv9632311188ads {margin-bottom:10px;}#yiv9632311188 #yiv9632311188ygrp-mkp .yiv9632311188ad {padding:0 0;}#yiv9632311188 #yiv9632311188ygrp-mkp .yiv9632311188ad p {margin:0;}#yiv9632311188 #yiv9632311188ygrp-mkp .yiv9632311188ad a {color:#0000ff;text-decoration:none;}#yiv9632311188 #yiv9632311188ygrp-sponsor #yiv9632311188ygrp-lc {font-family:Arial;}#yiv9632311188 #yiv9632311188ygrp-sponsor #yiv9632311188ygrp-lc #yiv9632311188hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9632311188 #yiv9632311188ygrp-sponsor #yiv9632311188ygrp-lc .yiv9632311188ad {margin-bottom:10px;padding:0 0;}#yiv9632311188 #yiv9632311188actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9632311188 #yiv9632311188activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9632311188 #yiv9632311188activity span {font-weight:700;}#yiv9632311188 #yiv9632311188activity span:first-child {text-transform:uppercase;}#yiv9632311188 #yiv9632311188activity span a {color:#5085b6;text-decoration:none;}#yiv9632311188 #yiv9632311188activity span span {color:#ff7900;}#yiv9632311188 #yiv9632311188activity span .yiv9632311188underline {text-decoration:underline;}#yiv9632311188 .yiv9632311188attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9632311188 .yiv9632311188attach div a {text-decoration:none;}#yiv9632311188 .yiv9632311188attach img {border:none;padding-right:5px;}#yiv9632311188 .yiv9632311188attach label {display:block;margin-bottom:5px;}#yiv9632311188 .yiv9632311188attach label a {text-decoration:none;}#yiv9632311188 blockquote {margin:0 0 0 4px;}#yiv9632311188 .yiv9632311188bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9632311188 .yiv9632311188bold a {text-decoration:none;}#yiv9632311188 dd.yiv9632311188last p a {font-family:Verdana;font-weight:700;}#yiv9632311188 dd.yiv9632311188last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9632311188 dd.yiv9632311188last p span.yiv9632311188yshortcuts {margin-right:0;}#yiv9632311188 div.yiv9632311188attach-table div div a {text-decoration:none;}#yiv9632311188 div.yiv9632311188attach-table {width:400px;}#yiv9632311188 div.yiv9632311188file-title a, #yiv9632311188 div.yiv9632311188file-title a:active, #yiv9632311188 div.yiv9632311188file-title a:hover, #yiv9632311188 div.yiv9632311188file-title a:visited {text-decoration:none;}#yiv9632311188 div.yiv9632311188photo-title a, #yiv9632311188 div.yiv9632311188photo-title a:active, #yiv9632311188 div.yiv9632311188photo-title a:hover, #yiv9632311188 div.yiv9632311188photo-title a:visited {text-decoration:none;}#yiv9632311188 div#yiv9632311188ygrp-mlmsg #yiv9632311188ygrp-msg p a span.yiv9632311188yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9632311188 .yiv9632311188green {color:#628c2a;}#yiv9632311188 .yiv9632311188MsoNormal {margin:0 0 0 0;}#yiv9632311188 o {font-size:0;}#yiv9632311188 #yiv9632311188photos div {float:left;width:72px;}#yiv9632311188 #yiv9632311188photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv9632311188 #yiv9632311188photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9632311188 #yiv9632311188reco-category {font-size:77%;}#yiv9632311188 #yiv9632311188reco-desc {font-size:77%;}#yiv9632311188 .yiv9632311188replbq {margin:4px;}#yiv9632311188 #yiv9632311188ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9632311188 #yiv9632311188ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9632311188 #yiv9632311188ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9632311188 #yiv9632311188ygrp-mlmsg select, #yiv9632311188 input, #yiv9632311188 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9632311188 #yiv9632311188ygrp-mlmsg pre, #yiv9632311188 code {font:115% monospace;}#yiv9632311188 #yiv9632311188ygrp-mlmsg * {line-height:1.22em;}#yiv9632311188 #yiv9632311188ygrp-mlmsg #yiv9632311188logo {padding-bottom:10px;}#yiv9632311188 #yiv9632311188ygrp-msg p a {font-family:Verdana;}#yiv9632311188 #yiv9632311188ygrp-msg p#yiv9632311188attach-count span {color:#1E66AE;font-weight:700;}#yiv9632311188 #yiv9632311188ygrp-reco #yiv9632311188reco-head {color:#ff7900;font-weight:700;}#yiv9632311188 #yiv9632311188ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9632311188 #yiv9632311188ygrp-sponsor #yiv9632311188ov li a {font-size:130%;text-decoration:none;}#yiv9632311188 #yiv9632311188ygrp-sponsor #yiv9632311188ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9632311188 #yiv9632311188ygrp-sponsor #yiv9632311188ov ul {margin:0;padding:0 0 0 8px;}#yiv9632311188 #yiv9632311188ygrp-text {font-family:Georgia;}#yiv9632311188 #yiv9632311188ygrp-text p {margin:0 0 1em 0;}#yiv9632311188 #yiv9632311188ygrp-text tt {font-size:120%;}#yiv9632311188 #yiv9632311188ygrp-vital ul li:last-child {border-right:none !important;}#yiv9632311188

Sat Nov 4, 2017 11:21 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The third parameter is obsolete and does nothing at this point.

Before the JSON file, the Yahoo portfolio views used to be tables. That
parameter would tell the add-in which view of the portfolio to download.
But now the online viewing of the portfolio is irrelevant because all of
the data is in the JSON file instead of in a table on a web page.

On Sat, Nov 4, 2017 at 11:11 PM, Michael Henry midhenry1@yahoo.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
> That's interesting and it brings up the question of what the other
> parameter does (... 2,?,1)
>
>
>

Sun Nov 5, 2017 7:49 am (PST) . Posted by:

"V. Shankar" tarakayan

Is blogspot.com good place to visit periodically and review as there are so many messages on recent RCHGetYahooquotes?thanksShankar
On Friday, November 3, 2017, 2:57:46 AM GMT+5:30, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:

 

See the announcement for the last few days:
https://smf-add-in.blogspot.com/

On Thu, Nov 2, 2017 at 2:02 PM, rho49m@​... wrote:

I have a strange problem, I used SMF to update a Excel spreadsheet of stock holdings. This worked with an array for years. Today the spreadsheets don't download data, Nothing happens at all. If I enter =RCHGetYahooQuotes("IBM" ) in a data cell nothing happens. I have tried the obvious, downloaded the latest ,zip file 02-11-17, tried to re enable the add-in in excel, which seemed to indicate the plugin was  installed.

Any idea what I should do next?

Sun Nov 5, 2017 8:24 am (PST) . Posted by:

"Randy Harmelink" rharmelink

I decided to put announcements about the add-in there, so they don't get
lost in all the noise that can happen in the Yahoo group. Easiest way to
monitor it would be to use the RSS feed.

On Sun, Nov 5, 2017 at 8:45 AM, 'V. Shankar' tarakayan@
​...
wrote:

>
> Is blogspot.com good place to visit periodically and review as there are
> so many messages on recent RCHGetYahooquotes?
> thanks
> Shankar
>
> On Friday, November 3, 2017, 2:57:46 AM GMT+5:30, Randy Harmelink
> rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
> ​
>
>
> See the announcement for the last few days:
>
> https://smf-add-in.blogspot.com/
>
>
>

Sun Nov 5, 2017 9:14 am (PST) . Posted by:

ridgebacksexcel

Randy,


I have my Google Docs Quote sheet set up and it seems to be downloading except for one small problem.


It is cutting off the last row # 41 (ticker is XOM).


=smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-1vQvTsqN9G0cDM8BonrqyvNpDdQWJrqTVRKCkx1IqSLGj3JSOQoStQLJdJyV3IZip64wcpdzQeAj32c5/pub?output=csv")


I have the sheet array formula entered from Columns A-L, and rows 1-102.


Any ideas?

Sun Nov 5, 2017 10:00 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Sorry, not a clue. But it appears to be a problem on the Google side, not
with the add-in, because if you click on the link with the browser, it
opens up the CSV file in EXCEL with only 41 lines.

Do you have some type of break after the XOM line in the Google sheet?
Invalid ticker symbol maybe? You didn't get any kind of message when you
did the publish step?

On Sun, Nov 5, 2017 at 10:13 AM, tmallen2@
​...
wrote:

> I have my Google Docs Quote sheet set up and it seems to be downloading
> except for one small problem.
>
> It is cutting off the last row # 41 (ticker is XOM).
>
> =smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-
> 1vQvTsqN9G0cDM8BonrqyvNpDdQWJrqTVRKCkx1IqSLGj3JSOQoStQLJdJyV
> 3IZip64wcpdzQeAj32c5/pub?output=csv")
>
> I have the sheet array formula entered from Columns A-L, and rows 1-102.
>
>
>

Sun Nov 5, 2017 12:02 pm (PST) . Posted by:

"Bernie Dudek" berniedudek

Randy –

I'm not very proficient in your Add-In. I think I have it installed correctly following your instructions at:

http://ogres-crypt.com/SMF/Tips-and-FAQs/Install-the-addin.html

I copied and pasted =RCHGetElementNumber("Version&quot;) and it returned the right results (see following).

I used your old version of the function to track and update my portfolio but I'm having trouble with your new function.

The link on the Tips and FAQ site isn't working for:

0.1a Getting started with the add-in (EXCEL 2010)

Could you send me a sample spreadsheet that how to retrieve the following for IBM and Vanguard Total Stock Market Index Fund (VTSAX):

Symbol

Company

Last Price

Prev Close

Last Price

Open

High

Volume

52 W High

52 W Low

Thanks

Bernie Dudek

Sun Nov 5, 2017 12:38 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Hmm. That link no longer works for me. But I believe that was written about
using the smfUpdateDownloadTable process and used MSN data elements. Those
MSN data elements have been gone for a long time. In any case, if what
you're looking for is quotes, that wouldn't have been a good tool anyway.

Last week, the answer would have been to use RCHGetYahooQuotes(), but Yahoo
stopped providing the source for that data a week ago.

Sensing a trend here? *sigh*

At this point, I'd suggest the recent update mentioned on the announcements
blog <https://smf-add-in.blogspot.com/> -- the new
smfGetYahooPortfolioView() function. The example
<http://ogres-crypt.com/SMF/Templates/smfGetYahooPortfolioView-Example.xls> I
updated yesterday should fit your needs. Just change the ticker symbols
down the yellow-shaded column and the field numbers along the yellow-shaded
row as you need. You can find a list of available field numbers on the
second worksheet tab of the workbook.

Or, you could just array-enter this over a 3-row by 10-column range:

=smfGetYahooPortfolioView("IBM,VTSAX","01031551151819216663",,1)

..which gets me:

Symbol Short Name Last Price Prev Close Last Price Open High Volume 52-Wk
High 52-Wk Low
IBM International Business Machines $151.58 $153.35 $151.58 $153.42 $153.47
4431825 $182.79 $139.13
VTSAX Vanguard Total Stock Market Ind $64.67 $64.49 $64.67 -- -- -- $64.67
$53.13

I'm not sure why you wanted "Last Price" twice, but at least that gives you
an idea of what you can quickly do. Each 2-digit number in that second
parameter indicates a field number. There are nearly 100 available.

On Sun, Nov 5, 2017 at 1:02 PM, 'Bernie Dudek' bdudek1172@
​...
wrote:

>
>
> I'm not very proficient in your Add-In. I think I have it installed
> correctly following your instructions at:
>
> http://ogres-crypt.com/SMF/Tips-and-FAQs/Install-the-addin.html
>
>
>
> I copied and pasted *=RCHGetElementNumber("Version") *and it returned the
> right results (see following).
>
>
>
>
>
> I used your old version of the function to track and update my portfolio
> but I'm having trouble with your new function.
>
>
>
> The link on the Tips and FAQ site isn't working for:
>
> 0.1a Getting started with the add-in (EXCEL 2010)
>
>
>
> Could you send me a sample spreadsheet that how to retrieve the following
> for IBM and Vanguard Total Stock Market Index Fund (VTSAX):
>
> Symbol
>
> Company
>
> Last Price
>
> Prev Close
>
> Last Price
>
> Open
>
> High
>
> Volume
>
> 52 W High
>
> 52 W Low
>
>
>
> Thanks
>

Sun Nov 5, 2017 1:44 pm (PST) . Posted by:

ridgebacksexcel

Awesome Randy. This is just what I was looking for. I'll give it a try now and leave the Google sheet as a backup. Thanks.

Sun Nov 5, 2017 2:40 pm (PST) . Posted by:

"Bernie Dudek" berniedudek

It seems to be working but I have an issue with updating the macro. When I click the SMF Force Recalculation symbol I get

I have turned off the macro, closed the spreadsheet and re-added the macro which is in C:\SMF Add-in. It still gives me the error. The message says it's looking for the macro in C:\SMF not in C:\SMF Add-in. How can I correct this error?

Bernie Dudek

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Sunday, November 05, 2017 2:38 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Google Finance

Hmm. That link no longer works for me. But I believe that was written about using the smfUpdateDownloadTable process and used MSN data elements. Those MSN data elements have been gone for a long time. In any case, if what you're looking for is quotes, that wouldn't have been a good tool anyway.

Last week, the answer would have been to use RCHGetYahooQuotes(), but Yahoo stopped providing the source for that data a week ago.

Sensing a trend here? *sigh*

At this point, I'd suggest the recent update mentioned on the announcements blog <https://smf-add-in.blogspot.com/> -- the new smfGetYahooPortfolioView() function. The example <http://ogres-crypt.com/SMF/Templates/smfGetYahooPortfolioView-Example.xls> I updated yesterday should fit your needs. Just change the ticker symbols down the yellow-shaded column and the field numbers along the yellow-shaded row as you need. You can find a list of available field numbers on the second worksheet tab of the workbook.

Or, you could just array-enter this over a 3-row by 10-column range:

=smfGetYahooPortfolioView("IBM,VTSAX","01031551151819216663",,1)

..which gets me:

Symbol

Short Name

Last Price

Prev Close

Last Price

Open

High

Volume

52-Wk High

52-Wk Low

IBM

International Business Machines

$151.58

$153.35

$151.58

$153.42

$153.47

4431825

$182.79

$139.13

VTSAX

Vanguard Total Stock Market Ind

$64.67

$64.49

$64.67

--

--

--

$64.67

$53.13

I'm not sure why you wanted "Last Price" twice, but at least that gives you an idea of what you can quickly do. Each 2-digit number in that second parameter indicates a field number. There are nearly 100 available.

On Sun, Nov 5, 2017 at 1:02 PM, 'Bernie Dudek' bdudek1172@

​...

wrote:

I'm not very proficient in your Add-In. I think I have it installed correctly following your instructions at:

http://ogres-crypt.com/SMF/Tips-and-FAQs/Install-the-addin.html

I copied and pasted =RCHGetElementNumber("Version&quot;) and it returned the right results (see following).

I used your old version of the function to track and update my portfolio but I'm having trouble with your new function.

The link on the Tips and FAQ site isn't working for:

0.1a Getting started with the add-in (EXCEL 2010)

Could you send me a sample spreadsheet that how to retrieve the following for IBM and Vanguard Total Stock Market Index Fund (VTSAX):

Symbol

Company

Last Price

Prev Close

Last Price

Open

High

Volume

52 W High

52 W Low

Thanks

Sun Nov 5, 2017 2:47 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

What are you clicking on? A button on your quick access toolbar?

I'm also confused by your comment that you "turned off the macro". I'm not
sure what that means.

On Sun, Nov 5, 2017 at 3:37 PM, 'Bernie Dudek' bdudek1172@comcast.net
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
> It seems to be working but I have an issue with updating the macro. When I
> click the SMF Force Recalculation symbol I get
>
>
>
>
>
> I have turned off the macro, closed the spreadsheet and re-added the macro
> which is in C:\SMF Add-in. It still gives me the error. The message says
> it's looking for the macro in C:\SMF not in C:\SMF Add-in. How can I
> correct this error?
>
>
>

Sun Nov 5, 2017 1:41 pm (PST) . Posted by:

u1704141

Randy,

I transitioned over to using smfGetPortfolioView for Yahoo Quotes. =smfGetYahooPortfolioView(C9,"15") works fine pulling it current quote. However, I'm not having success using field "51" which I think is the previous close quote... =smfGetYahooPortfolioView(C9,"51"). Any thoughts on perhaps it's not pulling? The cell contains '-' when trying to execute that field..

thank you


---In smf_addin@yahoogroups.com, <elandry@...> wrote :

Randy,


The new function works beautifully! Thanks. Was wondering if it were possible to add "EBITDA" and perhaps EPS estimates at some point.


Thanks!!!


Sun Nov 5, 2017 2:05 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

You'll need the 2017.11.04 version of the add-in to use the "51" field
number.

On Sun, Nov 5, 2017 at 2:41 PM, u1704141@
​...
wrote:

>
> I transitioned over to using smfGetPortfolioView for Yahoo Quotes.
> =smfGetYahooPortfolioView(C9,"15") works fine pulling it current quote.
> However, I'm not having success using field "51" which I think is the
> previous close quote... =smfGetYahooPortfolioView(C9,"51"). Any thoughts
> on perhaps it's not pulling? The cell contains '-' when trying to execute
> that field..
>
>

Sun Nov 5, 2017 2:51 pm (PST) . Posted by:

weldenc

I'm still working on converting to this new template. I previously used the "% Change" in my calculations. I don't see this in the new template. I see the "Change" column which I assume is in $. However, I assume the "% Change" was calculated based on the closing price from the previous day. I don't see anything but Open, High, and Low. Is there a way to calculate the value I want?
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Posting Komentar