15 Messages
Digest #4207
Messages
Sat Nov 4, 2017 12:31 pm (PDT) . Posted by:
"Joe Mo" joe_mo37
I looked at the Goggle Docs alternative noted on the blog. The Google Doc is reporting closing prices for stocks and ETFs for yesterday (11/3/2017), which is fine, but the NAVs for mutual funds are from last Thursday (11/2/2017 20:00:00). If you are tracking mutual funds, be cautious.
Sat Nov 4, 2017 12:41 pm (PDT) . Posted by:
"Higrm" higrm
Hi Joe,Mutual funds seem to all update at 20:00, so if you are before that time of the day, you will only see the price from the previous day. They do not change during the day, only once at 20:00.Cheers,Higrm
On Saturday, November 4, 2017, 8:32:13 PM GMT+1, Joe Mo joe_mo37@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
I looked at the Goggle Docs alternative noted on the blog. The Google Doc is reporting closing prices for stocks and ETFs for yesterday (11/3/2017), which is fine, but the NAVs for mutual funds are from last Thursday (11/2/2017 20:00:00). If you are tracking mutual funds, be cautious.
On Saturday, November 4, 2017, 8:32:13 PM GMT+1, Joe Mo joe_mo37@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
I looked at the Goggle Docs alternative noted on the blog. The Google Doc is reporting closing prices for stocks and ETFs for yesterday (11/3/2017), which is fine, but the NAVs for mutual funds are from last Thursday (11/2/2017 20:00:00). If you are tracking mutual funds, be cautious.
Sat Nov 4, 2017 1:12 pm (PDT) . Posted by:
"Pieter van Leeuwen" pietertvl
Thanks, Randy.
OK ... published to csv. Missed that option the first time thru on G Docs.
Re the upper and lower ... couldn't be sure if it was CASE or mis spelling with two L's.
I gather it can be File or FILe or fILe or filE or FIle. Fine.
Now ... earlier, I successfully setup and import the published HTML version into Excel xlsx. (And it refreshes)
I could not manage the same into either an Excel csv or straight Excel file.
In both cases, your version test works. So the add in is installed.
In both cases, all that displays in the cell after using the =smf.... instruction is a description
Eg, I tested YOUR blog entry, and it reported
"S&P500 Quotes via Google Sheets" .... but no data appear
And when I used my published URL link (csv version) ... same deal ... description but no data.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Saturday, November 04, 2017 12:13 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Quotesheet to Excel CSV format
If you're missing the "/pub?output=csv" string at the end of the URL, you probably missed the step to publish your Google sheet. The "/pub" indicates it's a published document and "output=csv" indicates it was published as a CSV file.
Upper/Lower case on function names shouldn't make a difference. I see a lot of people citing examples in all lower case.
On Sat, Nov 4, 2017 at 12:01 PM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
Hi Randy and anyone else who has succeeded all the way through.
Got my Google Docs s/s setup. Got it linked/exported into Excel xlsx format. (Assuming that refresh will work Monday)
I need data ultimately in txt, but csv seems like the way to get it there.
Loaded your SMF addins. Tested it as suggested (via "Version"). Reports back fine.
From your blog .....
I used the method to publish a CSV file of quotes data for the S&P 500 stocks. To get it into EXCEL, all that is needed is:
=smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRobB_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?output=csv")
My Google Docs URL does not have any of this text at the back end ... /pub?output=csv
So right now, the cell with this published content URL reports ERROR.
Did I miss a step along the way?
Also, I can't be sure, but is it CSVFile or CSVFIIe ? with an i or two ll's ? Tried both ways, and get ERROR either way.
OK ... published to csv. Missed that option the first time thru on G Docs.
Re the upper and lower ... couldn't be sure if it was CASE or mis spelling with two L's.
I gather it can be File or FILe or fILe or filE or FIle. Fine.
Now ... earlier, I successfully setup and import the published HTML version into Excel xlsx. (And it refreshes)
I could not manage the same into either an Excel csv or straight Excel file.
In both cases, your version test works. So the add in is installed.
In both cases, all that displays in the cell after using the =smf.... instruction is a description
Eg, I tested YOUR blog entry, and it reported
"S&P500 Quotes via Google Sheets" .... but no data appear
And when I used my published URL link (csv version) ... same deal ... description but no data.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Saturday, November 04, 2017 12:13 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Quotesheet to Excel CSV format
If you're missing the "/pub?output=csv" string at the end of the URL, you probably missed the step to publish your Google sheet. The "/pub" indicates it's a published document and "output=csv" indicates it was published as a CSV file.
Upper/Lower case on function names shouldn't make a difference. I see a lot of people citing examples in all lower case.
On Sat, Nov 4, 2017 at 12:01 PM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
Hi Randy and anyone else who has succeeded all the way through.
Got my Google Docs s/s setup. Got it linked/exported into Excel xlsx format. (Assuming that refresh will work Monday)
I need data ultimately in txt, but csv seems like the way to get it there.
Loaded your SMF addins. Tested it as suggested (via "Version"). Reports back fine.
From your blog .....
I used the method to publish a CSV file of quotes data for the S&P 500 stocks. To get it into EXCEL, all that is needed is:
=smfGetCSVFIle("https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRobB_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?output=csv")
My Google Docs URL does not have any of this text at the back end ... /pub?output=
So right now, the cell with this published content URL reports ERROR.
Did I miss a step along the way?
Also, I can't be sure, but is it CSVFile or CSVFIIe ? with an i or two ll's ? Tried both ways, and get ERROR either way.
Sat Nov 4, 2017 1:38 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sounds like when you tested my file and yours, you entered the formula into
a single cell instead of array-entering it over a range. I just
array-entered mine over a small range and it worked fine.
On Sat, Nov 4, 2017 at 1:12 PM, 'Pieter van Leeuwen' pieter_biz@ecentral.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
> Thanks, Randy.
>
> OK ... published to csv. Missed that option the first time thru on G
> Docs.
>
> Re the upper and lower ... couldn't be sure if it was CASE or mis
> spelling with two L's.
>
> I gather it can be File or FILe or fILe or filE or FIle. Fine.
>
>
>
> Now ... earlier, I successfully setup and import the published HTML
> version into *Excel xlsx*. (And it refreshes)
>
>
>
> I could not manage the same into either an Excel csv or straight Excel
> file.
>
>
>
> In both cases, your version test works. So the add in is installed.
>
>
>
> In both cases, all that displays in the cell after using the =smf....
> instruction is a description
>
>
>
> Eg, I tested YOUR blog entry, and it reported
>
> "S&P500 Quotes via Google Sheets" .... but no data appear
>
>
>
> And when I used my published URL link (csv version) ... same deal ...
> description but no data.
>
a single cell instead of array-entering it over a range. I just
array-entered mine over a small range and it worked fine.
On Sat, Nov 4, 2017 at 1:12 PM, 'Pieter van Leeuwen' pieter_biz@ecentral.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
> Thanks, Randy.
>
> OK ... published to csv. Missed that option the first time thru on G
> Docs.
>
> Re the upper and lower ... couldn't be sure if it was CASE or mis
> spelling with two L's.
>
> I gather it can be File or FILe or fILe or filE or FIle. Fine.
>
>
>
> Now ... earlier, I successfully setup and import the published HTML
> version into *Excel xlsx*. (And it refreshes)
>
>
>
> I could not manage the same into either an Excel csv or straight Excel
> file.
>
>
>
> In both cases, your version test works. So the add in is installed.
>
>
>
> In both cases, all that displays in the cell after using the =smf....
> instruction is a description
>
>
>
> Eg, I tested YOUR blog entry, and it reported
>
> "S&P500 Quotes via Google Sheets" .... but no data appear
>
>
>
> And when I used my published URL link (csv version) ... same deal ...
> description but no data.
>
Sat Nov 4, 2017 12:43 pm (PDT) . Posted by:
stumpy_chris
I have recently ran into the issues of getting quotes (obviously like everyone).
I have been reading through the topics, but is there a fix for this. Does the YahooPortfolioView work... and if so how? I can't get it working.
Thanks.
I have been reading through the topics, but is there a fix for this. Does the YahooPortfolioView work... and if so how? I can't get it working.
Thanks.
Sat Nov 4, 2017 1:15 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
First, you'd need the 2017.11.02 version of the add-in, then try the
example workbook for smfGetYahooPortfolioView()
Did you read the announcements blog?
https://smf-add-in.blogspot.com/
On Sat, Nov 4, 2017 at 12:43 PM, cschwartz@
...
wrote:
> I have recently ran into the issues of getting quotes (obviously like
> everyone).
>
> I have been reading through the topics, but is there a fix for this.
> Does the YahooPortfolioView work... and if so how? I can't get it
> working.
>
>
>
example workbook for smfGetYahooPortfolioView()
Did you read the announcements blog?
https://smf-add-in.blogspot.com/
On Sat, Nov 4, 2017 at 12:43 PM, cschwartz@
...
wrote:
> I have recently ran into the issues of getting quotes (obviously like
> everyone).
>
> I have been reading through the topics, but is there a fix for this.
> Does the YahooPortfolioView work... and if so how? I can't get it
> working.
>
>
>
Sat Nov 4, 2017 2:02 pm (PDT) . Posted by:
stumpy_chris
Thanks. Yes read blog and have updated version.
When I input formula my cells are blank. Not sure if I am using the correct formula or inserting information properly.
When I input formula my cells are blank. Not sure if I am using the correct formula or inserting information properly.
Sat Nov 4, 2017 1:02 pm (PDT) . Posted by:
"Yahoo!" lewglenn
When I duplicate what you did it still didn't work. When it executes the command in my subroutine:
Range("A2:H70") = smfGetYahooPortfolioView(SelStr, "0151181920158756", , 1)
it steps into your function modGetYahooPortfolio and it stops with the code segment:
Public Function smfGetYahooPortfolioView(ByVal pTickers As Variant, _
Optional ByVal pItems As Variant = "01020304050607080910111213141516171819202122232425262728293031323334", _
Optional ByVal pView As String = "Obsolete", _
Optional ByVal pHeader As Integer = 0, _
Optional ByVal pDim1 As Integer = 0, _
Optional ByVal pDim2 As Integer = 0)yellowed out.
When I look at pTickers, it says Empty. The same for pItems. I checked that the first argument to the smfGetYahooPortfolioView function, SelStr, is correctly populated but somehow it's not getting in to
modGetYahooPortfolio.
Do you have any suggestion as to what I'm still doing wrong?
On Saturday, November 4, 2017, 12:00:12 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
This works for me:
SelStr = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _ & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC, GILD,SRC,MRCC,DES,TQQQ," _ & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE, VOE,GDX,SILJ,DSENX,EFA," _ & "DGRW,REML"
Range("A2:I70") = smfGetYahooPortfolioView( SelStr, "013518192015163535", , 1)
...although my results are different now because I've added the additional fields. So, adding the additional fields, I have this code:
SelStr = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _ & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC,GILD,SRC,MRCC,DES,TQQQ," _ & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE,VOE,GDX,SILJ,DSENX,EFA," _ & "DGRW,REML"
Range("A2:H70") = smfGetYahooPortfolioView(SelStr, "0151181920158756", , 1)
...which generates something like:
| Symbol | Prev Close | Open | High | Low | Last Price | Last Traded Date/Time | 50-DMA |
| TLT | $125.30 | $125.47 | $125.70 | $125.14 | $125.64 | 11/3/2017 8:00:00 PM | $124.87 |
| -- | -- | -- | -- | -- | -- | -- | -- |
| EDV | $118.52 | $118.68 | $119.27 | $118.47 | $119.25 | 11/3/2017 8:00:01 PM | $117.60 |
| CVX | $115.33 | $115.48 | $115.75 | $114.73 | $114.99 | 11/3/2017 8:04:39 PM | $117.43 |
| SON | $51.65 | $51.68 | $52.12 | $51.35 | $51.91 | 11/3/2017 8:00:58 PM | $50.90 |
| VNQ | $83.30 | $82.89 | $83.41 | $82.53 | $83.09 | 11/3/2017 8:00:00 PM | $83.43 |
| SBIO | $30.42 | $30.58 | $31.08 | $30.58 | $31.05 | 11/3/2017 7:59:30 PM | $31.02 |
| T | $33.17 | $33.34 | $33.39 | $32.95 | $33.30 | 11/3/2017 8:04:34 PM | $36.76 |
| TQQQ | $129.12 | $130.49 | $132.87 | $129.17 | $132.72 | 11/3/2017 8:00:00 PM | $119.06 |
The reason the second row is empty is you passed TLT twice. As is, the function only fills in the first occurrence of a ticker symbol.
On Sat, Nov 4, 2017 at 10:39 AM, lewglenn@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
So my vba code now looks like:
Range("A2:I70") = smfGetYahooPortfolioView(SelSt r, "013518192015163535", , 1)
where SelStr = Portfolio2016 and Portfolio2016 = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _
& "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC, GILD,SRC,MRCC,DES,TQQQ," _
& "AHH,HDV,SPHD,XBI,EEM,UVE,KRE, VOE,GDX,SILJ,DSENX,EFA," _
& "DGRW,REML"
and this fails. I'm using the November 2 version of the add-in. What am I still doing wrong?
#yiv2708842290 #yiv2708842290 -- #yiv2708842290ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2708842290 #yiv2708842290ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2708842290 #yiv2708842290ygrp-mkp #yiv2708842290hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2708842290 #yiv2708842290ygrp-mkp #yiv2708842290ads {margin-bottom:10px;}#yiv2708842290 #yiv2708842290ygrp-mkp .yiv2708842290ad {padding:0 0;}#yiv2708842290 #yiv2708842290ygrp-mkp .yiv2708842290ad p {margin:0;}#yiv2708842290 #yiv2708842290ygrp-mkp .yiv2708842290ad a {color:#0000ff;text-decoration:none;}#yiv2708842290 #yiv2708842290ygrp-sponsor #yiv2708842290ygrp-lc {font-family:Arial;}#yiv2708842290 #yiv2708842290ygrp-sponsor #yiv2708842290ygrp-lc #yiv2708842290hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2708842290 #yiv2708842290ygrp-sponsor #yiv2708842290ygrp-lc .yiv2708842290ad {margin-bottom:10px;padding:0 0;}#yiv2708842290 #yiv2708842290actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2708842290 #yiv2708842290activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2708842290 #yiv2708842290activity span {font-weight:700;}#yiv2708842290 #yiv2708842290activity span:first-child {text-transform:uppercase;}#yiv2708842290 #yiv2708842290activity span a {color:#5085b6;text-decoration:none;}#yiv2708842290 #yiv2708842290activity span span {color:#ff7900;}#yiv2708842290 #yiv2708842290activity span .yiv2708842290underline {text-decoration:underline;}#yiv2708842290 .yiv2708842290attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2708842290 .yiv2708842290attach div a {text-decoration:none;}#yiv2708842290 .yiv2708842290attach img {border:none;padding-right:5px;}#yiv2708842290 .yiv2708842290attach label {display:block;margin-bottom:5px;}#yiv2708842290 .yiv2708842290attach label a {text-decoration:none;}#yiv2708842290 blockquote {margin:0 0 0 4px;}#yiv2708842290 .yiv2708842290bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2708842290 .yiv2708842290bold a {text-decoration:none;}#yiv2708842290 dd.yiv2708842290last p a {font-family:Verdana;font-weight:700;}#yiv2708842290 dd.yiv2708842290last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2708842290 dd.yiv2708842290last p span.yiv2708842290yshortcuts {margin-right:0;}#yiv2708842290 div.yiv2708842290attach-table div div a {text-decoration:none;}#yiv2708842290 div.yiv2708842290attach-table {width:400px;}#yiv2708842290 div.yiv2708842290file-title a, #yiv2708842290 div.yiv2708842290file-title a:active, #yiv2708842290 div.yiv2708842290file-title a:hover, #yiv2708842290 div.yiv2708842290file-title a:visited {text-decoration:none;}#yiv2708842290 div.yiv2708842290photo-title a, #yiv2708842290 div.yiv2708842290photo-title a:active, #yiv2708842290 div.yiv2708842290photo-title a:hover, #yiv2708842290 div.yiv2708842290photo-title a:visited {text-decoration:none;}#yiv2708842290 div#yiv2708842290ygrp-mlmsg #yiv2708842290ygrp-msg p a span.yiv2708842290yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2708842290 .yiv2708842290green {color:#628c2a;}#yiv2708842290 .yiv2708842290MsoNormal {margin:0 0 0 0;}#yiv2708842290 o {font-size:0;}#yiv2708842290 #yiv2708842290photos div {float:left;width:72px;}#yiv2708842290 #yiv2708842290photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv2708842290 #yiv2708842290photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2708842290 #yiv2708842290reco-category {font-size:77%;}#yiv2708842290 #yiv2708842290reco-desc {font-size:77%;}#yiv2708842290 .yiv2708842290replbq {margin:4px;}#yiv2708842290 #yiv2708842290ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2708842290 #yiv2708842290ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2708842290 #yiv2708842290ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2708842290 #yiv2708842290ygrp-mlmsg select, #yiv2708842290 input, #yiv2708842290 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2708842290 #yiv2708842290ygrp-mlmsg pre, #yiv2708842290 code {font:115% monospace;}#yiv2708842290 #yiv2708842290ygrp-mlmsg * {line-height:1.22em;}#yiv2708842290 #yiv2708842290ygrp-mlmsg #yiv2708842290logo {padding-bottom:10px;}#yiv2708842290 #yiv2708842290ygrp-msg p a {font-family:Verdana;}#yiv2708842290 #yiv2708842290ygrp-msg p#yiv2708842290attach-count span {color:#1E66AE;font-weight:700;}#yiv2708842290 #yiv2708842290ygrp-reco #yiv2708842290reco-head {color:#ff7900;font-weight:700;}#yiv2708842290 #yiv2708842290ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2708842290 #yiv2708842290ygrp-sponsor #yiv2708842290ov li a {font-size:130%;text-decoration:none;}#yiv2708842290 #yiv2708842290ygrp-sponsor #yiv2708842290ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2708842290 #yiv2708842290ygrp-sponsor #yiv2708842290ov ul {margin:0;padding:0 0 0 8px;}#yiv2708842290 #yiv2708842290ygrp-text {font-family:Georgia;}#yiv2708842290 #yiv2708842290ygrp-text p {margin:0 0 1em 0;}#yiv2708842290 #yiv2708842290ygrp-text tt {font-size:120%;}#yiv2708842290 #yiv2708842290ygrp-vital ul li:last-child {border-right:none !important;}#yiv2708842290
Range("A2:H70") = smfGetYahooPortfolioView(SelStr, "0151181920158756", , 1)
it steps into your function modGetYahooPortfolio and it stops with the code segment:
Public Function smfGetYahooPortfolioView(ByVal pTickers As Variant, _
Optional ByVal pItems As Variant = "01020304050607080910111213141516171819202122232425262728293031323334", _
Optional ByVal pView As String = "Obsolete", _
Optional ByVal pHeader As Integer = 0, _
Optional ByVal pDim1 As Integer = 0, _
Optional ByVal pDim2 As Integer = 0)yellowed out.
When I look at pTickers, it says Empty. The same for pItems. I checked that the first argument to the smfGetYahooPortfolioView function, SelStr, is correctly populated but somehow it's not getting in to
modGetYahooPortfolio.
Do you have any suggestion as to what I'm still doing wrong?
On Saturday, November 4, 2017, 12:00:12 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
This works for me:
SelStr = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _ & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC, GILD,SRC,MRCC,DES,TQQQ," _ & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE, VOE,GDX,SILJ,DSENX,EFA," _ & "DGRW,REML"
Range("A2:I70") = smfGetYahooPortfolioView( SelStr, "013518192015163535", , 1)
...although my results are different now because I've added the additional fields. So, adding the additional fields, I have this code:
SelStr = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _ & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC,GILD,SRC,MRCC,DES,TQQQ," _ & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE,VOE,GDX,SILJ,DSENX,EFA," _ & "DGRW,REML"
Range("A2:H70") = smfGetYahooPortfolioView(SelStr, "0151181920158756", , 1)
...which generates something like:
| Symbol | Prev Close | Open | High | Low | Last Price | Last Traded Date/Time | 50-DMA |
| TLT | $125.30 | $125.47 | $125.70 | $125.14 | $125.64 | 11/3/2017 8:00:00 PM | $124.87 |
| -- | -- | -- | -- | -- | -- | -- | -- |
| EDV | $118.52 | $118.68 | $119.27 | $118.47 | $119.25 | 11/3/2017 8:00:01 PM | $117.60 |
| CVX | $115.33 | $115.48 | $115.75 | $114.73 | $114.99 | 11/3/2017 8:04:39 PM | $117.43 |
| SON | $51.65 | $51.68 | $52.12 | $51.35 | $51.91 | 11/3/2017 8:00:58 PM | $50.90 |
| VNQ | $83.30 | $82.89 | $83.41 | $82.53 | $83.09 | 11/3/2017 8:00:00 PM | $83.43 |
| SBIO | $30.42 | $30.58 | $31.08 | $30.58 | $31.05 | 11/3/2017 7:59:30 PM | $31.02 |
| T | $33.17 | $33.34 | $33.39 | $32.95 | $33.30 | 11/3/2017 8:04:34 PM | $36.76 |
| TQQQ | $129.12 | $130.49 | $132.87 | $129.17 | $132.72 | 11/3/2017 8:00:00 PM | $119.06 |
The reason the second row is empty is you passed TLT twice. As is, the function only fills in the first occurrence of a ticker symbol.
On Sat, Nov 4, 2017 at 10:39 AM, lewglenn@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
So my vba code now looks like:
Range("
where SelStr = Portfolio2016 and Portfolio2016 = "TLT,TLT,
& "BIP,EIGR,
& "AHH,HDV,
& "DGRW,REML&quo
and this fails. I'm using the November 2 version of the add-in. What am I still doing wrong?
#yiv2708842290 #yiv2708842290 -- #yiv2708842290ygrp-
Sat Nov 4, 2017 1:32 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
If it's stopped on the function definition statement, pTickers would be
empty. It's saying it can't start the routine because it has a problem.
What VBA message are you getting at the time?
Let's try this. I'll attach a workbook with just my test macro in it. Try
it and see if your version of the add-in works with it.
On Sat, Nov 4, 2017 at 1:01 PM, Yahoo! lewglenn@
...
wrote:
>
>
> When I duplicate what you did it still didn't work. When it executes the
> command in my subroutine:
>
> Range("A2:H70") = smfGetYahooPortfolioView(SelStr, "0151181920158756", ,
> 1)
>
> it steps into your function *modGetYahooPortfolio* and it stops with the
> code segment:
>
> Public Function smfGetYahooPortfolioView(ByVal pTickers As Variant, _
> Optional ByVal pItems As Variant = "
> 01020304050607080910111213141516171819202122232425262728293031323334", _
> Optional ByVal pView As String = "Obsolete", _
> Optional ByVal pHeader As Integer = 0, _
> Optional ByVal pDim1 As Integer = 0, _
> Optional ByVal pDim2 As Integer = 0)
> yellowed out.
>
> When I look at pTickers, it says Empty. The same for pItems. I checked
> that the first argument to the smfGetYahooPortfolioView function, SelStr,
> is correctly populated but somehow it's not getting in to
> *modGetYahooPortfolio.*
>
> Do you have any suggestion as to what I'm still doing wrong?
>
>
> On Saturday, November 4, 2017, 12:00:12 PM PDT, Randy Harmelink
> rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
>
>
> This works for me:
>
>
> SelStr = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _
> & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC, GILD,SRC,MRCC,DES,TQQQ," _
> & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE, VOE,GDX,SILJ,DSENX,EFA," _
> & "DGRW,REML"
>
> Range("A2:I70") = smfGetYahooPortfolioView( SelStr, "013518192015163535",
> , 1)
>
> ...although my results are different now because I've added the additional
> fields. So, adding the additional fields, I have this code:
>
> SelStr = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _
> & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC,GILD,SRC,MRCC,DES,TQQQ," _
> & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE,VOE,GDX,SILJ,DSENX,EFA," _
> & "DGRW,REML"
>
> Range("A2:H70") = smfGetYahooPortfolioView(SelStr, "0151181920158756", ,
> 1)
>
> ...which generates something like:
>
> *Symbol* *Prev Close* *Open* *High* *Low* *Last Price* *Last Traded
> Date/Time* *50-DMA*
> TLT $125.30 $125.47 $125.70 $125.14 $125.64 11/3/2017 8:00:00 PM $124.87
> -- -- -- -- -- -- -- --
> EDV $118.52 $118.68 $119.27 $118.47 $119.25 11/3/2017 8:00:01 PM $117.60
> CVX $115.33 $115.48 $115.75 $114.73 $114.99 11/3/2017 8:04:39 PM $117.43
> SON $51.65 $51.68 $52.12 $51.35 $51.91 11/3/2017 8:00:58 PM $50.90
> VNQ $83.30 $82.89 $83.41 $82.53 $83.09 11/3/2017 8:00:00 PM $83.43
> SBIO $30.42 $30.58 $31.08 $30.58 $31.05 11/3/2017 7:59:30 PM $31.02
> T $33.17 $33.34 $33.39 $32.95 $33.30 11/3/2017 8:04:34 PM $36.76
> TQQQ $129.12 $130.49 $132.87 $129.17 $132.72 11/3/2017 8:00:00 PM $119.06
>
> The reason the second row is empty is you passed TLT twice. As is, the
> function only fills in the first occurrence of a ticker symbol.
>
>
>
empty. It's saying it can't start the routine because it has a problem.
What VBA message are you getting at the time?
Let's try this. I'll attach a workbook with just my test macro in it. Try
it and see if your version of the add-in works with it.
On Sat, Nov 4, 2017 at 1:01 PM, Yahoo! lewglenn@
...
wrote:
>
>
> When I duplicate what you did it still didn't work. When it executes the
> command in my subroutine:
>
> Range("A2:H70") = smfGetYahooPortfolioView(SelStr, "0151181920158756", ,
> 1)
>
> it steps into your function *modGetYahooPortfolio* and it stops with the
> code segment:
>
> Public Function smfGetYahooPortfolioView(ByVal pTickers As Variant, _
> Optional ByVal pItems As Variant = "
> 01020304050607080910111213141516171819202122232425262728293031323334", _
> Optional ByVal pView As String = "Obsolete", _
> Optional ByVal pHeader As Integer = 0, _
> Optional ByVal pDim1 As Integer = 0, _
> Optional ByVal pDim2 As Integer = 0)
> yellowed out.
>
> When I look at pTickers, it says Empty. The same for pItems. I checked
> that the first argument to the smfGetYahooPortfolioView function, SelStr,
> is correctly populated but somehow it's not getting in to
> *modGetYahooPortfolio.*
>
> Do you have any suggestion as to what I'm still doing wrong?
>
>
> On Saturday, November 4, 2017, 12:00:12 PM PDT, Randy Harmelink
> rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
>
>
> This works for me:
>
>
> SelStr = "TLT,TLT,
> & "BIP,EIGR,
> & "AHH,HDV,
> & "DGRW,REML&quo
>
> Range("
> , 1)
>
> ...although my results are different now because I've added the additional
> fields. So, adding the additional fields, I have this code:
>
> SelStr = "TLT,TLT,
> & "BIP,EIGR,
> & "AHH,HDV,
> & "DGRW,REML&quo
>
> Range("
> 1)
>
> ...which generates something like:
>
> *Symbol* *Prev Close* *Open* *High* *Low* *Last Price* *Last Traded
> Date/Time* *50-DMA*
> TLT $125.30 $125.47 $125.70 $125.14 $125.64 11/3/2017 8:00:00 PM $124.87
> -- -- -- -- -- -- -- --
> EDV $118.52 $118.68 $119.27 $118.47 $119.25 11/3/2017 8:00:01 PM $117.60
> CVX $115.33 $115.48 $115.75 $114.73 $114.99 11/3/2017 8:04:39 PM $117.43
> SON $51.65 $51.68 $52.12 $51.35 $51.91 11/3/2017 8:00:58 PM $50.90
> VNQ $83.30 $82.89 $83.41 $82.53 $83.09 11/3/2017 8:00:00 PM $83.43
> SBIO $30.42 $30.58 $31.08 $30.58 $31.05 11/3/2017 7:59:30 PM $31.02
> T $33.17 $33.34 $33.39 $32.95 $33.30 11/3/2017 8:04:34 PM $36.76
> TQQQ $129.12 $130.49 $132.87 $129.17 $132.72 11/3/2017 8:00:00 PM $119.06
>
> The reason the second row is empty is you passed TLT twice. As is, the
> function only fills in the first occurrence of a ticker symbol.
>
>
>
Attachment(s) from Randy Harmelink
1 of 1 File(s)
Sat Nov 4, 2017 1:57 pm (PDT) . Posted by:
john_hoel
I have been watching this discussion with interest because I write VBA as well. As far as I know, a function cannot change any cell other than the one from which it is invoked, and that only by the value it returns. Is this code part of a function, or part of a sub invoked as a macro? If a function, how can it change a range?
Sat Nov 4, 2017 1:29 pm (PDT) . Posted by:
johneknudson
OK,
Apologies for a super sub optimized solution but here is how I have gotten some visibility to pull direct share price into my spreadsheet.
I used the function: =smfPricesByDates(A2,DATE(YEAR($K$1),MONTH($K$1),DAY($K$1)))
A2 is my stock ticker field. For example A2 has SSTK in it.
K1 is a date field and contains the function =now()-1 which gives yesterday's date at the current time.
This will pull yesterday's closing price. For my investing decision cadence that is just fine. I make my tactical price choices (and correlations with Option TV) directly in my order interface. My strike price choice are more strategic and considered from closed market prices anyway. I'm sure there is a proper/effective way to work the =smfGetYahooJSONField( Ticker, Module Name, Field Name) function, likely with a "field name" that includes "LastTradePriceOnly" but all my attempts there were malformed.
Good luck! -Ted
Apologies for a super sub optimized solution but here is how I have gotten some visibility to pull direct share price into my spreadsheet.
I used the function: =smfPricesByDates(
A2 is my stock ticker field. For example A2 has SSTK in it.
K1 is a date field and contains the function =now()-1 which gives yesterday'
This will pull yesterday'
Good luck! -Ted
Sat Nov 4, 2017 1:46 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
That's not a good way to do it, because:
1. Any formula based on NOW() is going to be volatile. That means it will
recalculate any time you make a change in the workbook.
2. Yahoo historical quotes are pretty slow. It can take up to 10 times as
long as other functions.
3. You'd need to do that individually for every ticker symbol. Again, takes
longer.
On Sat, Nov 4, 2017 at 1:29 PM, johneknudson@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Apologies for a super sub optimized solution but here is how I have gotten
> some visibility to pull direct share price into my spreadsheet.
>
>
>
> I used the function: =smfPricesByDates(A2,DATE(
> YEAR($K$1),MONTH($K$1),DAY($K$1)))
>
> A2 is my stock ticker field. For example A2 has SSTK in it.
>
> K1 is a date field and contains the function =now()-1 which gives
> yesterday's date at the current time.
>
>
>
> - This will pull yesterday's closing price. For my investing decision
> cadence that is just fine. I make my tactical price choices (and
> correlations with Option TV) directly in my order interface. My strike
> price choice are more strategic and considered from closed market prices
> anyway.
> - I'm sure there is a proper/effective way to work the
> =smfGetYahooJSONField( Ticker, Module Name, Field Name) function, likely
> with a "field name" that includes "LastTradePriceOnly" but all my attempts
> there were malformed.
>
>
>
>
>
1. Any formula based on NOW() is going to be volatile. That means it will
recalculate any time you make a change in the workbook.
2. Yahoo historical quotes are pretty slow. It can take up to 10 times as
long as other functions.
3. You'd need to do that individually for every ticker symbol. Again, takes
longer.
On Sat, Nov 4, 2017 at 1:29 PM, johneknudson@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Apologies for a super sub optimized solution but here is how I have gotten
> some visibility to pull direct share price into my spreadsheet.
>
>
>
> I used the function: =smfPricesByDates(
> YEAR($K$1),MONTH(
>
> A2 is my stock ticker field. For example A2 has SSTK in it.
>
> K1 is a date field and contains the function =now()-1 which gives
> yesterday'
>
>
>
> - This will pull yesterday'
> cadence that is just fine. I make my tactical price choices (and
> correlations with Option TV) directly in my order interface. My strike
> price choice are more strategic and considered from closed market prices
> anyway.
> - I'm sure there is a proper/effective way to work the
> =smfGetYahooJSONFie
> with a "field name" that includes "LastTradePric
> there were malformed.
>
>
>
>
>
Sat Nov 4, 2017 1:37 pm (PDT) . Posted by:
johneknudson
And I see (from other threads) that =smfGetYahooPortfolioView(A2,"15") get me share price where A2 is my ticker price field.
Many thanks to Randy for his hard work due to the Yahoo change. -Ted
Many thanks to Randy for his hard work due to the Yahoo change. -Ted
Sat Nov 4, 2017 1:40 pm (PDT) . Posted by:
palminha
Randy,
Thank you for your fast reply!
But I have some doubts:
1 - I have update your last version but something maybe wrong:
I use your last version
I open your template and I must choose
If I say Yes I get
If I say No the file stay like this (I use excel 2000 under Windows 10 Home)
but when I try to insert new Tickers in column B I get
What I did wrong?
2 - In your samples for exchange rates how can I place the table with the currency's I need? is there any template?
Thank you again and sorry but I no expert like you about this :(
Kind Regards
Paulo
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
Right now, the solution for that is:
=INT(smfUNIX2Date(0+smfGetYahooPortfolioView("SPY","16")))
...but in the updated version of the add-in I'm running, with new fields added to the function, is a much simpler:
=smfGetYahooPortfolioView("SPY","78")
However, I would prefer people minimize usage of the function by doing array-entered ranges to get all the data they need at once, instead of using individual cell invocations.
My version also has fixes for currencies, so:
=smfGetYahooPortfolioView("CADUSD=X,CADGBP=X,CADAUD=X","0115",,1)
...now returns:
Symbol Last Price CADUSD=X 0.7838219 CADGBP=X 0.5994513 CADAUD=X 1.0232011 -- --
On Sat, Nov 4, 2017 at 11:22 AM, palminha@... mailto:palminha@... [smf_addin] <smf_addin@yahoogroups.com mailto:smf_addin@yahoogroups.com> wrote:
I use a lot in several files formulas like this =DATEVALUE(RCHGetYahooQuotes( Ticker,"d1")) and now that can´t be used anymore, what formula do you suggest to replace it?
I also like to have one alternative to replace =IF($B5=D$2,"--", RCHGetYahooQuotes($B5&D$2&"=X" ,"l1")) used to have exchange rates.
Those are my major problems for now!
Thank you for you kind support and efforts to overcome this sad stop from Yahoo Finance.
Thank you for your fast reply!
But I have some doubts:
1 - I have update your last version but something maybe wrong:
I use your last version
I open your template and I must choose
If I say Yes I get
If I say No the file stay like this (I use excel 2000 under Windows 10 Home)
but when I try to insert new Tickers in column B I get
What I did wrong?
2 - In your samples for exchange rates how can I place the table with the currency's I need? is there any template?
Thank you again and sorry but I no expert like you about this :(
Kind Regards
Paulo
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
Right now, the solution for that is:
=INT(smfUNIX2Date(0+smfGetYahooPortfolioView("SPY","16")))
...but in the updated version of the add-in I'm running, with new fields added to the function, is a much simpler:
=smfGetYahooPortfolioView("SPY","78")
However, I would prefer people minimize usage of the function by doing array-entered ranges to get all the data they need at once, instead of using individual cell invocations.
My version also has fixes for currencies, so:
=smfGetYahooPortfolioView("CADUSD=X,CADGBP=X,CADAUD=X","0115",,1)
...now returns:
Symbol Last Price CADUSD=X 0.7838219 CADGBP=X 0.5994513 CADAUD=X 1.0232011 -- --
On Sat, Nov 4, 2017 at 11:22 AM, palminha@... mailto:palminha@... [smf_addin] <smf_addin@yahoogroups.com mailto:smf_addin@yahoogroups.com> wrote:
I use a lot in several files formulas like this =DATEVALUE(RCHGetYa
I also like to have one alternative to replace =IF($B5=D$2,
Those are my major problems for now!
Thank you for you kind support and efforts to overcome this sad stop from Yahoo Finance.
Sat Nov 4, 2017 1:58 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
If you had images in your message, they're not coming through. The post on
the Yahoo group had the first image, but little after that.
So I have no idea what you are saying yes/no to? Hmm. I did see the image
of your "Version" results which show you have the add-in installed
someplace other than where I do. That will cause unresolved links -- maybe
your yes/no box? If so, I usually just close that out and run the
smfFixLinks macro from the add-in.
I have several changes in the next release related to currencies, so you
may want to hold off doing anything in that area.
On Sat, Nov 4, 2017 at 1:40 PM, palminha@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Thank you for your fast reply!
>
> But I have some doubts:
>
> 1 - I have update your last version but something maybe wrong:
>
> I use your last version
>
> I open your template and I must choose
>
> If I say Yes I get
>
> If I say No the file stay like this (I use excel 2000 under Windows 10
> Home)
>
> but when I try to insert new Tickers in column B I get
>
>
> What I did wrong?
>
> 2 - In your samples for exchange rates how can I place the table with the
> currency's I need? is there any template?
>
> Thank you again and sorry but I no expert like you about this :(
>
>
>
the Yahoo group had the first image, but little after that.
So I have no idea what you are saying yes/no to? Hmm. I did see the image
of your "Version" results which show you have the add-in installed
someplace other than where I do. That will cause unresolved links -- maybe
your yes/no box? If so, I usually just close that out and run the
smfFixLinks macro from the add-in.
I have several changes in the next release related to currencies, so you
may want to hold off doing anything in that area.
On Sat, Nov 4, 2017 at 1:40 PM, palminha@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Thank you for your fast reply!
>
> But I have some doubts:
>
> 1 - I have update your last version but something maybe wrong:
>
> I use your last version
>
> I open your template and I must choose
>
> If I say Yes I get
>
> If I say No the file stay like this (I use excel 2000 under Windows 10
> Home)
>
> but when I try to insert new Tickers in column B I get
>
>
> What I did wrong?
>
> 2 - In your samples for exchange rates how can I place the table with the
> currency'
>
> Thank you again and sorry but I no expert like you about this :(
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar