Senin, 31 Oktober 2016

[smf_addin] Digest Number 3830

3 Messages

Digest #3830
1a
2a
how to merge two Yahoo price series? by "Gary Funck" garyfunck
2b
Re: how to merge two Yahoo price series? by "Randy Harmelink" rharmelink

Messages

Sun Oct 30, 2016 5:23 am (PDT) . Posted by:

wongkayau

Hi Randy,


It works! You are the best! Thank you so much.


Wish you have a great week ahead. :)


Cheers,
L

Sun Oct 30, 2016 6:22 pm (PDT) . Posted by:

"Gary Funck" garyfunck


I've a got a spreadsheet that computes the ratio of two stock/index
price series and plots the ratio as well as both normalized price series.
It also calculates some statistics to aid in analyzing pairs trades.

At present each data series is fetched like this:

=RCHGetYahooHistory(Symbol1,YEAR(Start_Date),MONTH(Start_Date),DAY(Start_Date),
YEAR(End_Date),MONTH(End_Date),DAY(End_Date),"d","DA",0,1,1,2)

where

End_Date is typically TODAY() and Start_Date is End_Date - Weeks * 5.
Weeks is an input cell where the user specifies the number of weeks
to be used in the analysis. It is assumed that there are approximately 5
trading days in each week.

It works fine, but sometimes for example I may ask for 5 years of data
when one/both of the price series doesn't have that much data.
Then, there is also the issue that both price series might not have
data for some dates even though generally they have data within
the start and end dates.

A reasonable way to work around those issues is two join both data
series on the date column, thus showing data only for the dates that
they have in common. Is there a way to accomplish this without
custom VBA code? I'm using Excel 2010.

thanks,
- Gary

Sun Oct 30, 2016 7:13 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

First thing I'll mention is that it's usually not a good idea to use
TODAY() -- it makes everything that refers to it volatile. That means it
will recalculate if ANYTHING changes in the workbook. I usually use this to
get a static date for the last trading date:

=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))

That leads me to the solution to the other issue. Here's what I would do:

1. Use RCHGetYahooHistory() to get the two data series you want, but I
would use simpler formulas. Just array-enter them over a range large enough
to have 5 years of data fit in them -- say, 1500 rows by 2 columns.

=RCHGetYahooHistory(Symbol1,,,,,,,"d","DA")
=RCHGetYahooHistory(Symbol2,,,,,,,"d","DA")

2. Use RCHGetYahooHistory() to get the two date series for SPY. It should
be the most complete series.

=RCHGetYahooHistory("SPY",,,,,,,"d","D")

3. Create the 5-year date series from the available SPY dates.

4. Use VLOOKUP() formulas on the 5-year date series against each of your
equity dates to create two columns of price data. If one of them isn't
available for a date, I wouldn't drop the date. Instead, I would used the
adjusted closing price from the previous day as a replacement for that
day's adjusted closing price. In essences, just carrying it forward.

5. Create your ratios from the two price columns.

​You could combine steps 2 and 3 by using your Start_Date and End_Date
processing, but use the RCHGetYahooQuotes() trick up above in place of the
TODAY() function.​ Then you would need to use the "resort" parameter as
well, since you want the data from old to new.

On Sun, Oct 30, 2016 at 6:22 PM, Gary Funck gary@
​...wrote:

>
> I've a got a spreadsheet that computes the ratio of two stock/index
> price series and plots the ratio as well as both normalized price series.
> It also calculates some statistics to aid in analyzing pairs trades.
>
> At present each data series is fetched like this:
>
> =
> ​​
> ​​
> ​​
> RCHGetYahooHistory(Symbol1,YEAR(Start_Date),MONTH(Start_
> Date),DAY(Start_Date),
> YEAR(End_Date),MONTH(End_Date)
> ,DAY(End_Date),"d","DA",0,1,1,2)
>
> where
>
> End_Date is typically TODAY() and Start_Date is End_Date - Weeks * 5.
> Weeks is an input cell where the user specifies the number of weeks
> to be used in the analysis. It is assumed that there are approximately 5
> trading days in each week.
>
> It works fine, but sometimes for example I may ask for 5 years of data
> when one/both of the price series doesn't have that much data.
> Then, there is also the issue that both price series might not have
> data for some dates even though generally they have data within
> the start and end dates.
>
> A reasonable way to work around those issues is two join both data
> series on the date column, thus showing data only for the dates that
> they have in common. Is there a way to accomplish this without
> custom VBA code? I'm using Excel 2010.
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
READ MORE....

[belajar-excel] Digest Number 4005

1 Message

Digest #4005

Message

Mon Oct 31, 2016 12:41 am (PDT) . Posted by:

"achmad adi" didon13@ymail.com

allhduliah...terimakasih para master semua,,,,masalah teratasi...


Pada Selasa, 25 Oktober 2016 18:37, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Hai Didon,

Terlampir contohnya.

Regards,
Kid

2016-10-24 21:34 GMT+07:00 achmad adi didon13@ymail.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  dear master,,,saya mencoba membuat adv filter tapi hanya di sheet 1 aja yang bisa difilter...yang ingin saya tanyakan,,,seadainya saya ingin memfilter data tetapi berdasarkan data sheet terpilih..bagaimanakah caranya...mohon pencerahaannya,,,saya lampirkan file

sebelumnya saya sampaikan terimakasih


#yiv4837768898 #yiv4837768898 -- #yiv4837768898ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4837768898 #yiv4837768898ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4837768898 #yiv4837768898ygrp-mkp #yiv4837768898hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4837768898 #yiv4837768898ygrp-mkp #yiv4837768898ads {margin-bottom:10px;}#yiv4837768898 #yiv4837768898ygrp-mkp .yiv4837768898ad {padding:0 0;}#yiv4837768898 #yiv4837768898ygrp-mkp .yiv4837768898ad p {margin:0;}#yiv4837768898 #yiv4837768898ygrp-mkp .yiv4837768898ad a {color:#0000ff;text-decoration:none;}#yiv4837768898 #yiv4837768898ygrp-sponsor #yiv4837768898ygrp-lc {font-family:Arial;}#yiv4837768898 #yiv4837768898ygrp-sponsor #yiv4837768898ygrp-lc #yiv4837768898hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4837768898 #yiv4837768898ygrp-sponsor #yiv4837768898ygrp-lc .yiv4837768898ad {margin-bottom:10px;padding:0 0;}#yiv4837768898 #yiv4837768898actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4837768898 #yiv4837768898activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4837768898 #yiv4837768898activity span {font-weight:700;}#yiv4837768898 #yiv4837768898activity span:first-child {text-transform:uppercase;}#yiv4837768898 #yiv4837768898activity span a {color:#5085b6;text-decoration:none;}#yiv4837768898 #yiv4837768898activity span span {color:#ff7900;}#yiv4837768898 #yiv4837768898activity span .yiv4837768898underline {text-decoration:underline;}#yiv4837768898 .yiv4837768898attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4837768898 .yiv4837768898attach div a {text-decoration:none;}#yiv4837768898 .yiv4837768898attach img {border:none;padding-right:5px;}#yiv4837768898 .yiv4837768898attach label {display:block;margin-bottom:5px;}#yiv4837768898 .yiv4837768898attach label a {text-decoration:none;}#yiv4837768898 blockquote {margin:0 0 0 4px;}#yiv4837768898 .yiv4837768898bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4837768898 .yiv4837768898bold a {text-decoration:none;}#yiv4837768898 dd.yiv4837768898last p a {font-family:Verdana;font-weight:700;}#yiv4837768898 dd.yiv4837768898last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4837768898 dd.yiv4837768898last p span.yiv4837768898yshortcuts {margin-right:0;}#yiv4837768898 div.yiv4837768898attach-table div div a {text-decoration:none;}#yiv4837768898 div.yiv4837768898attach-table {width:400px;}#yiv4837768898 div.yiv4837768898file-title a, #yiv4837768898 div.yiv4837768898file-title a:active, #yiv4837768898 div.yiv4837768898file-title a:hover, #yiv4837768898 div.yiv4837768898file-title a:visited {text-decoration:none;}#yiv4837768898 div.yiv4837768898photo-title a, #yiv4837768898 div.yiv4837768898photo-title a:active, #yiv4837768898 div.yiv4837768898photo-title a:hover, #yiv4837768898 div.yiv4837768898photo-title a:visited {text-decoration:none;}#yiv4837768898 div#yiv4837768898ygrp-mlmsg #yiv4837768898ygrp-msg p a span.yiv4837768898yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4837768898 .yiv4837768898green {color:#628c2a;}#yiv4837768898 .yiv4837768898MsoNormal {margin:0 0 0 0;}#yiv4837768898 o {font-size:0;}#yiv4837768898 #yiv4837768898photos div {float:left;width:72px;}#yiv4837768898 #yiv4837768898photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv4837768898 #yiv4837768898photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4837768898 #yiv4837768898reco-category {font-size:77%;}#yiv4837768898 #yiv4837768898reco-desc {font-size:77%;}#yiv4837768898 .yiv4837768898replbq {margin:4px;}#yiv4837768898 #yiv4837768898ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4837768898 #yiv4837768898ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4837768898 #yiv4837768898ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4837768898 #yiv4837768898ygrp-mlmsg select, #yiv4837768898 input, #yiv4837768898 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4837768898 #yiv4837768898ygrp-mlmsg pre, #yiv4837768898 code {font:115% monospace;}#yiv4837768898 #yiv4837768898ygrp-mlmsg * {line-height:1.22em;}#yiv4837768898 #yiv4837768898ygrp-mlmsg #yiv4837768898logo {padding-bottom:10px;}#yiv4837768898 #yiv4837768898ygrp-msg p a {font-family:Verdana;}#yiv4837768898 #yiv4837768898ygrp-msg p#yiv4837768898attach-count span {color:#1E66AE;font-weight:700;}#yiv4837768898 #yiv4837768898ygrp-reco #yiv4837768898reco-head {color:#ff7900;font-weight:700;}#yiv4837768898 #yiv4837768898ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4837768898 #yiv4837768898ygrp-sponsor #yiv4837768898ov li a {font-size:130%;text-decoration:none;}#yiv4837768898 #yiv4837768898ygrp-sponsor #yiv4837768898ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4837768898 #yiv4837768898ygrp-sponsor #yiv4837768898ov ul {margin:0;padding:0 0 0 8px;}#yiv4837768898 #yiv4837768898ygrp-text {font-family:Georgia;}#yiv4837768898 #yiv4837768898ygrp-text p {margin:0 0 1em 0;}#yiv4837768898 #yiv4837768898ygrp-text tt {font-size:120%;}#yiv4837768898 #yiv4837768898ygrp-vital ul li:last-child {border-right:none !important;}#yiv4837768898

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
READ MORE....

Minggu, 30 Oktober 2016

[smf_addin] Digest Number 3829

5 Messages

Digest #3829
1a
bloomberg data by wongkayau
1b
Re: bloomberg data by "Randy Harmelink" rharmelink
1c
1d
Re: bloomberg data by "Randy Harmelink" rharmelink
2a
Re: yahoo history array start date by "Magic Oreo" cs.holdings

Messages

Sat Oct 29, 2016 5:57 am (PDT) . Posted by:

wongkayau

Hi Randy,


Good morning.


I have been using excel to download index data from Bloomberg via this link:
http://www.bloomberg.com/markets/api/bulk-time-series/price/HSI:IND?timeFrame=1_YEAR


However, it does not work suddenly, although it works fine and I place the url in Firefox which shows all the data.


Is it because Bloomberg does not allow excel data download anymore?

Thank you so much for your help.


Best regards,
Laurent








Sat Oct 29, 2016 4:44 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Note that when you go to the URL you site, you end up on a different web
page:

https://www.bloomberg.com/markets/api/bulk-time-series/price/HSI:IND?timeFrame=1_YEAR

That is, "http" to "https". The add-in doesn't like being redirected to a
different URL. But your browser doesn't mind.

On Sat, Oct 29, 2016 at 5:57 AM, wongkayau@
​... wrote:

> I have been using excel to download index data from Bloomberg via this
> link:
>
> http://www.bloomberg.com/markets/api/bulk-time-series/
> price/HSI:IND?timeFrame=1_YEAR
>
> However, it does not work suddenly, although it works fine and I place the
> url in Firefox which shows all the data.
>
> Is it because Bloomberg does not allow excel data download anymore?
>
>

Sat Oct 29, 2016 7:26 pm (PDT) . Posted by:

wongkayau

Hi Randy,


Thank you so much! You are so great.


Since the data has already been downloaded to Firefox/IE by "https://", is it possible to capture the data to excel? I have tried Data from Web of "Data" tab in excel, and a dialogue box has been popped up to open HSI_IND.json. I don't know how to deal with it. Could you please help?


Thanks a lot.


Best regards,
L

Sat Oct 29, 2016 8:01 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Here's how I would extract the data from that JSON output using the add-in
functions:

C4: =RCHGetWebData("
https://www.bloomberg.com/markets/api/bulk-time-series/price/HSI:IND?timeFrame=1_YEAR
")
B5: =B4+1
C5: =DATEVALUE(smfStrExtr(smfWord($C$4,$B5+1,"""date"""),":""",""""))
D5:
=smfConvertData(smfStrExtr(smfWord($C$4,$B5+1,"""date"""),"value"":","}"))

...and then copy row 5 down as needed.

On Sat, Oct 29, 2016 at 7:26 PM, wongkayau@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> Since the data has already been downloaded to Firefox/IE by "https://",
> is it possible to capture the data to excel? I have tried Data from Web of
> "Data" tab in excel, and a dialogue box has been popped up to open
> HSI_IND.json. I don't know how to deal with it. Could you please help?
>
>

Sat Oct 29, 2016 9:09 am (PDT) . Posted by:

"Magic Oreo" cs.holdings

That's clever … thanks!!

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, October 24, 2016 11:18 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] yahoo history array start date


You can specify no dates or both dates, but not just one date...
If you're just looking for YTD numbers, try something like:

=RCHGetYahooHistory("^GSPC&quot;,2016,1,4,2016,12,31,"w","dA",,,1)
Then it will just give you what's available for the range of the two dates.

On Mon, Oct 24, 2016 at 3:54 AM, pc_babe@
​... wrote:

Im trying to use the yahoo historical array. I'd like the start date to begin with this calendar year from Jan 4, 2016 ...and wrote:

=
​​
RCHGetYahooHistory("^GSPC";,2016,01,04,,,,"w","dA",,,1)

But, the comment comes with "problems with start date". If I leave the dats switches empty, it works fine, but provides data to the size of the array

Do you have a suggestion?


For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
READ MORE....

Sabtu, 29 Oktober 2016

[smf_addin] Digest Number 3828

2 Messages

Digest #3828
1a
Stock data from Athens bourse by "jesus gonzalez" jjjgge
1b
Re: Stock data from Athens bourse by "Randy Harmelink" rharmelink

Messages

Fri Oct 28, 2016 11:12 am (PDT) . Posted by:

"jesus gonzalez" jjjgge

Hello, I would like to know how to obtain historical data from Athens bourse, but detailed data from an specific stock in a concret day (I mean all data, bid/ask orders through the day and so).

Fri Oct 28, 2016 11:15 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Beyond my knowledge of data sources. Maybe someone else can help.

On Fri, Oct 28, 2016 at 10:45 AM, jesus gonzalez jjjgge@
​...wrote:

>
> Hello, I would like to know how to obtain historical data from Athens
> bourse, but detailed data from an specific stock in a concret day (I mean
> all data, bid/ask orders through the day and so).
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
READ MORE....