Sabtu, 23 Juli 2011

[smf_addin] Digest Number 1895

Messages In This Digest (14 Messages)

Messages

1a.

Yahoo! Adjusted Close = Total Return?

Posted by: "mkorgie" mkorgie@southwind.net   mkorgie

Fri Jul 22, 2011 5:51 am (PDT)



Looking for your insight: Can you use Yahoo!'s adjusted closing price to calculate "total return?" Based on Yahoo's description below I would say yes but interested in other thoughts to confirm, clarify or deny!

Mike

Definition From Yahoo! Help: Adjusted Close provides the closing price for the requested day, week, or month, adjusted for all applicable splits and dividend distributions. Data is adjusted using appropriate split and dividend multipliers, adhering to Center for Research in Security Prices (CRSP) standards. Split multipliers are determined by the split ratio. For instance, in a 2 for 1 split, the pre-split data is multiplied by 0.5. Dividend multipliers are calculated based on dividend as a percentage of price, primarily to avoid negative historical pricing. For example, when a $0.08 cash dividend is distributed on Feb 19 (ex-date), and the Feb 18 closing price was 24.96, the pre-dividend data is multiplied by (1-0.08/24.96) = 0.9968. Below is a detailed example of adjusted close calculations.

http://help.yahoo.com/l/us/yahoo/finance/quotes/quote-12.html

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
1b.

Re: Yahoo! Adjusted Close = Total Return?

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Fri Jul 22, 2011 6:03 am (PDT)



It would give you an approximation of total return. However, see:

http://finance.groups.yahoo.com/group/smf_addin/message/14926

I use the smfPricesByDates() function for the purpose of "total return".

On Fri, Jul 22, 2011 at 5:51 AM, mkorgie <mkorgie@southwind.net> wrote:

> Looking for your insight: Can you use Yahoo!'s adjusted closing price to
> calculate "total return?" Based on Yahoo's description below I would say
> yes but interested in other thoughts to confirm, clarify or deny!
>
> Mike
>
> Definition From Yahoo! Help: Adjusted Close provides the closing price for
> the requested day, week, or month, adjusted for all applicable splits and
> dividend distributions. Data is adjusted using appropriate split and
> dividend multipliers, adhering to Center for Research in Security Prices
> (CRSP) standards. Split multipliers are determined by the split ratio. For
> instance, in a 2 for 1 split, the pre-split data is multiplied by 0.5.
> Dividend multipliers are calculated based on dividend as a percentage of
> price, primarily to avoid negative historical pricing. For example, when a
> $0.08 cash dividend is distributed on Feb 19 (ex-date), and the Feb 18
> closing price was 24.96, the pre-dividend data is multiplied by
> (1-0.08/24.96) = 0.9968. Below is a detailed example of adjusted close
> calculations.
>
> http://help.yahoo.com/l/us/yahoo/finance/quotes/quote-12.html
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
2a.

Is there a way to get historical closes on commodities?

Posted by: "Wayne Tarrant" wayne.tarrant@yahoo.com   wayne.tarrant

Fri Jul 22, 2011 6:18 am (PDT)



I'm looking for historical daily spot closes on gold, silver, copper and ag commodities like corn, wheat, cattle, chicken, pork bellies, cocoa, sugar, really whatever I can get my hands on. I need a long series of data, preferably something like 30 years. Is it possible with the add-in? Even if these need to come through something other than the add-in, I'd appreciate any suggestions that anyone has, preferably of the zero cost variety rather than paying $1000 for spot corn for 6 years. Yes, I found that offer. 
 
Thank you for any help you can provide.
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2b.

Is there a way to get historical closes on commodities?

Posted by: "Wayne Tarrant" wayne.tarrant@yahoo.com   wayne.tarrant

Fri Jul 22, 2011 9:08 am (PDT)



I'm looking for historical daily spot closes on gold, silver, copper and ag commodities like corn, wheat, cattle, chicken, pork bellies, cocoa, sugar, really whatever I can get my hands on. I need a long series of data, preferably something like 30 years. Is it possible with the add-in? Even if these need to come through something other than the add-in, I'd appreciate any suggestions that anyone has, preferably of the zero cost variety rather than paying $1000 for spot corn for 6 years. Yes, I found that offer. 
 
Thank you for any help you can provide.
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2c.

Re: Is there a way to get historical closes on commodities?

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Fri Jul 22, 2011 9:25 am (PDT)



You could try using BarChart with this template from the files area:

RCHGetWebData-Template-BarChart-Quotes-Extract.xls

On Fri, Jul 22, 2011 at 6:18 AM, Wayne Tarrant <wayne.tarrant@yahoo.com>wrote:

>
> I'm looking for historical daily spot closes on gold, silver, copper and ag
> commodities like corn, wheat, cattle, chicken, pork bellies, cocoa, sugar,
> really whatever I can get my hands on. I need a long series of data,
> preferably something like 30 years. Is it possible with the add-in? Even if
> these need to come through something other than the add-in, I'd appreciate
> any suggestions that anyone has, preferably of the zero cost variety rather
> than paying $1000 for spot corn for 6 years. Yes, I found that offer.
>
> Thank you for any help you can provide.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
3a.

Open a file with a macro

Posted by: "jpaucamp1" oaucamp@googlemail.com   jpaucamp1

Fri Jul 22, 2011 8:27 am (PDT)



Hi Randy

I have been using the add-in for quite a while now and have always just kept the data in excel and worked with it there. I am now trying to put some of the pricing data in an access database. I have imported all the data for the SP500 into a table and am trying to use the DoCmd.TransferSpreadsheet method in access to try and update the table with new data every now and then. I am however encountering a problem. If the vba opens an excel file where the data are hard coded it works and the new data is trf from excel to access but if I use vba to open an excel file containing a RCHGetYahooHistory array with the latest data the values of the array equals NAME error. I have tried to refresh the array in the macro but nothing works. If I close the sheet down and open it back up everything is fine again but for some reason when I use the macro from access to open the file the NAME error appears.

I have also noted that the formula for the array changes from
{=RCHGetYahooHistory(R1C8,R2C8,R3C8,R4C8,R5C8,R6C8,R7C8,R8C8,R9C8,R10C8,R11C8,R12C8)}

to

{'C:\Program Files\SMF Add In\RCH_Stock_Market_Functions.xla'!RCHGetYahooHistory($H$1,$H$2,$H$3,$H$4,$H$5,$H$6,$H$7,$H$8,$H$9,$H$10,$H$11,$H$12)}

Do you have any ides why this might be happening? Do I need to declare the add-in as a type of variable or is there a procedure I should be calling.

Your help will be greatly appreciated.

Below is my existing vba code.

Sub GetSP500Data()

Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook

Set appExcel = CreateObject("Excel.Application")
Set myWorkbook = appExcel.Workbooks.Open("C:\Users\jaucamp\My Documents\SP500WebData.xlsm")
appExcel.Visible = True
appExcel.DisplayAlerts = False

Sheets("WebData").Select

Range("A1:F150").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormulaArray = _
"=RCHGetYahooHistory(R1C8,R2C8,R3C8,R4C8,R5C8,R6C8,R7C8,R8C8,R9C8,R10C8,R11C8,R12C8)"

'appExcel.Run "smfForceRecalculation"

myWorkbook.Save


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "SP500Data", "C:\Users\jaucamp\My Documents\SP500WebData.xlsm", -1, "AccessData!A1:F150"

myWorkbook.Close
Set appExcel = Nothing
Set myWorkbook = Nothing

End Sub

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
3b.

Re: Open a file with a macro

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Fri Jul 22, 2011 8:37 am (PDT)



See:

http://finance.groups.yahoo.com/group/smf_addin/message/11346

Or, it could be that when you open the file via VBA, it doesn't resolve
links automatically?

But why not just place the data directly into EXCEL using VBA? Check the
message archives of the group for examples.

On Fri, Jul 22, 2011 at 6:53 AM, jpaucamp1 <oaucamp@googlemail.com> wrote:

> Hi Randy
>
> I have been using the add-in for quite a while now and have always just
> kept the data in excel and worked with it there. I am now trying to put
> some of the pricing data in an access database. I have imported all the
> data for the SP500 into a table and am trying to use the
> DoCmd.TransferSpreadsheet method in access to try and update the table with
> new data every now and then. I am however encountering a problem. If the
> vba opens an excel file where the data are hard coded it works and the new
> data is trf from excel to access but if I use vba to open an excel file
> containing a RCHGetYahooHistory array with the latest data the values of the
> array equals NAME error. I have tried to refresh the array in the macro but
> nothing works. If I close the sheet down and open it back up everything is
> fine again but for some reason when I use the macro from access to open the
> file the NAME error appears.
>
>
> I have also noted that the formula for the array changes from
>
> {=RCHGetYahooHistory(R1C8,R2C8,R3C8,R4C8,R5C8,R6C8,R7C8,R8C8,R9C8,R10C8,R11C8,R12C8)}
>
>
> to
>
> {'C:\Program Files\SMF Add
> In\RCH_Stock_Market_Functions.xla'!RCHGetYahooHistory($H$1,$H$2,$H$3,$H$4,$H$5,$H$6,$H$7,$H$8,$H$9,$H$10,$H$11,$H$12)}
>
>
> Do you have any ides why this might be happening? Do I need to declare the
> add-in as a type of variable or is there a procedure I should be calling.
>
> Your help will be greatly appreciated.
>
> Below is my existing vba code.
>
>
> Sub GetSP500Data()
>
>
> Dim appExcel As Excel.Application
> Dim myWorkbook As Excel.Workbook
>
> Set appExcel = CreateObject("Excel.Application")
> Set myWorkbook = appExcel.Workbooks.Open("C:\Users\jaucamp\My
> Documents\SP500WebData.xlsm")
> appExcel.Visible = True
> appExcel.DisplayAlerts = False
>
> Sheets("WebData").Select
>
> Range("A1:F150").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.FormulaArray = _
>
> "=RCHGetYahooHistory(R1C8,R2C8,R3C8,R4C8,R5C8,R6C8,R7C8,R8C8,R9C8,R10C8,R11C8,R12C8)"
>
> 'appExcel.Run "smfForceRecalculation"
>
> myWorkbook.Save
>
>
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "SP500Data",
> "C:\Users\jaucamp\My Documents\SP500WebData.xlsm", -1, "AccessData!A1:F150"
>
> myWorkbook.Close
> Set appExcel = Nothing
> Set myWorkbook = Nothing
>
>
> End Sub
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
3c.

Re: Open a file with a macro

Posted by: "jpaucamp1" oaucamp@googlemail.com   jpaucamp1

Fri Jul 22, 2011 8:55 am (PDT)



Hi Randy

I don't understand what you mean by saying "place the data directly into excel" as I am trying to get the data from excel into access.

I have looked at the message you included re removing the front section of the formula but this is something I already to when I refresh the array formula. This normally corrects the cell values in all the other sheets that I use the procedure except in this case where I open the file from access with vba. Here it continues to show the NAME error.

It is like it doesn't recognise the add-in when using access?? Any ideas?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> See:
>
> http://finance.groups.yahoo.com/group/smf_addin/message/11346
>
> Or, it could be that when you open the file via VBA, it doesn't resolve
> links automatically?
>
> But why not just place the data directly into EXCEL using VBA? Check the
> message archives of the group for examples.
>
> On Fri, Jul 22, 2011 at 6:53 AM, jpaucamp1 <oaucamp@...> wrote:
>
> > Hi Randy
> >
> > I have been using the add-in for quite a while now and have always just
> > kept the data in excel and worked with it there. I am now trying to put
> > some of the pricing data in an access database. I have imported all the
> > data for the SP500 into a table and am trying to use the
> > DoCmd.TransferSpreadsheet method in access to try and update the table with
> > new data every now and then. I am however encountering a problem. If the
> > vba opens an excel file where the data are hard coded it works and the new
> > data is trf from excel to access but if I use vba to open an excel file
> > containing a RCHGetYahooHistory array with the latest data the values of the
> > array equals NAME error. I have tried to refresh the array in the macro but
> > nothing works. If I close the sheet down and open it back up everything is
> > fine again but for some reason when I use the macro from access to open the
> > file the NAME error appears.
> >
> >
> > I have also noted that the formula for the array changes from
> >
> > {=RCHGetYahooHistory(R1C8,R2C8,R3C8,R4C8,R5C8,R6C8,R7C8,R8C8,R9C8,R10C8,R11C8,R12C8)}
> >
> >
> > to
> >
> > {'C:\Program Files\SMF Add
> > In\RCH_Stock_Market_Functions.xla'!RCHGetYahooHistory($H$1,$H$2,$H$3,$H$4,$H$5,$H$6,$H$7,$H$8,$H$9,$H$10,$H$11,$H$12)}
> >
> >
> > Do you have any ides why this might be happening? Do I need to declare the
> > add-in as a type of variable or is there a procedure I should be calling.
> >
> > Your help will be greatly appreciated.
> >
> > Below is my existing vba code.
> >
> >
> > Sub GetSP500Data()
> >
> >
> > Dim appExcel As Excel.Application
> > Dim myWorkbook As Excel.Workbook
> >
> > Set appExcel = CreateObject("Excel.Application")
> > Set myWorkbook = appExcel.Workbooks.Open("C:\Users\jaucamp\My
> > Documents\SP500WebData.xlsm")
> > appExcel.Visible = True
> > appExcel.DisplayAlerts = False
> >
> > Sheets("WebData").Select
> >
> > Range("A1:F150").Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Selection.FormulaArray = _
> >
> > "=RCHGetYahooHistory(R1C8,R2C8,R3C8,R4C8,R5C8,R6C8,R7C8,R8C8,R9C8,R10C8,R11C8,R12C8)"
> >
> > 'appExcel.Run "smfForceRecalculation"
> >
> > myWorkbook.Save
> >
> >
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "SP500Data",
> > "C:\Users\jaucamp\My Documents\SP500WebData.xlsm", -1, "AccessData!A1:F150"
> >
> > myWorkbook.Close
> > Set appExcel = Nothing
> > Set myWorkbook = Nothing
> >
> >
> > End Sub
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
3d.

Re: Open a file with a macro

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Fri Jul 22, 2011 9:22 am (PDT)



I haven't used Access for well over a decade, so can't answer questions
related to it.

The VBA I'm suggesting is to put data into the EXCEL worksheet instead of a
formula. For example:

Range("A1:F150") = RCHGetYahooHistory("MMM", pAdjust:=1, pDim1:=150,
pDim2:=6)

On Fri, Jul 22, 2011 at 8:55 AM, jpaucamp1 <oaucamp@googlemail.com> wrote:

>
> I don't understand what you mean by saying "place the data directly into
> excel" as I am trying to get the data from excel into access.
>
> I have looked at the message you included re removing the front section of
> the formula but this is something I already to when I refresh the array
> formula. This normally corrects the cell values in all the other sheets
> that I use the procedure except in this case where I open the file from
> access with vba. Here it continues to show the NAME error.
>
> It is like it doesn't recognise the add-in when using access?? Any ideas?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
4a.

Re: GetHTMLTable #VALUE! error

Posted by: "davidmaartens" davidmaartens@vodamail.co.za   davidmaartens

Fri Jul 22, 2011 12:22 pm (PDT)



Hi Randy

I found a datasource for my local exchange. It unfortunately is also a paid service requiring a login. I looked at post you made many years ago and it seems to collect data, but is giving me the #VALUE! error again. Could you please check the syntax or source for me again.

Many thanks

=RCHGetHTMLTable("http://www.sharedata.co.za/Data/000675/detailed.htm"&email="davidmaartens@vodamail.co.za"&password="bsmart","Calendar Information",-1,"",1)

--- In smf_addin@yahoogroups.com, "davidmaartens" <davidmaartens@...> wrote:
>
> I'm doing something wrong cos it gives me blanks when I do this:
>
> =IF(BloombergTicker="None"," ",RCHGetHTMLTable("http://investing.businessweek.com/businessweek/research/stocks/financials/financials.asp?ticker="&BloombergTicker&"dataset=incomeStatement&period=A&currency=native","Currency in",-1,"",1))
>
> I've named the ticker cell BloombergTicker
>
> Thx
>
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@> wrote:
> >
> > Just do normal EXCEL string concatenation when creating the URL:
> >
> > ="...string1..."&A1&"...string2..."
> >
> > On Thu, Jul 7, 2011 at 12:45 AM, davidmaartens <davidmaartens@
> > > wrote:
> >
> > > Thanks - I get it to update now. However, what would the syntax be if I
> > > want to link the ticker to a cell and not have to re-type with every change.
> > >
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
4b.

Re: GetHTMLTable #VALUE! error

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Fri Jul 22, 2011 12:31 pm (PDT)



I hope that was a made up password in the example? Otherwise, you may want
to change it.

You would get an error with that URL coding. I don't know what string you're
trying to create, as there are numerous issues.

On Fri, Jul 22, 2011 at 12:22 PM, davidmaartens <
davidmaartens@vodamail.co.za> wrote:

>
> I found a datasource for my local exchange. It unfortunately is also a paid
> service requiring a login. I looked at post you made many years ago and it
> seems to collect data, but is giving me the #VALUE! error again. Could you
> please check the syntax or source for me again.
>
> Many thanks
>
> =RCHGetHTMLTable("http://www.sharedata.co.za/Data/000675/detailed.htm
> "&email="davidmaartens@vodamail.co.za"&password="bsmart","Calendar
> Information",-1,"",1)
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
4c.

Re: GetHTMLTable #VALUE! error

Posted by: "davidmaartens" davidmaartens@vodamail.co.za   davidmaartens

Fri Jul 22, 2011 2:01 pm (PDT)



Hi Randy

Normally I'd use:

=RCHGetHTMLTable("http://www.sharedata.co.za/Data/000675/detailed.htm
,"Calendar Information",-1,"",1)

but this brings up blanks, however when I type in email and password it gives me the #value error, so I'm thinking it is more correct than the first attempt cos its giving me something.

I've given you the detail cos you wouldn't be able to help without it - I'll reset asap.

"Various issues" is a bit vague...

Thx

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I hope that was a made up password in the example? Otherwise, you may want
> to change it.
>
> You would get an error with that URL coding. I don't know what string you're
> trying to create, as there are numerous issues.
>
> On Fri, Jul 22, 2011 at 12:22 PM, davidmaartens <
> davidmaartens@...> wrote:
>
> >
> > I found a datasource for my local exchange. It unfortunately is also a paid
> > service requiring a login. I looked at post you made many years ago and it
> > seems to collect data, but is giving me the #VALUE! error again. Could you
> > please check the syntax or source for me again.
> >
> > Many thanks
> >
> > =RCHGetHTMLTable("http://www.sharedata.co.za/Data/000675/detailed.htm
> > "&email="davidmaartens@..."&password="bsmart","Calendar
> > Information",-1,"",1)
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
4d.

Re: GetHTMLTable #VALUE! error

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Fri Jul 22, 2011 2:45 pm (PDT)



That wouldn't work either, since the first string has no ending quote.

The various issues were -- no ending of URL before parameters, improper
concatenation, it looked like it didn't need concatenation. Just a confusing
jumble to me.

Once, I did track down a #VALUE! error on an RCHGetHTMLTable -- it was due
to a very long string being in one cell, which exceeded EXCEL's limits. The
array was fine in VBA, but as soon as it tried to place those results into
the EXCEL worksheet, everything came up as #VALUE!. It wouldn't surprise me
if an improperly coded table might cause something like that.

One work-around might be to use RCHGetTableCell() and get the individual
cells that you need.

On Fri, Jul 22, 2011 at 2:01 PM, davidmaartens <davidmaartens@vodamail.co.za
> wrote:

>
> Normally I'd use:
>
> =RCHGetHTMLTable("http://www.sharedata.co.za/Data/000675/detailed.htm
> ,"Calendar Information",-1,"",1)
>
> but this brings up blanks, however when I type in email and password it
> gives me the #value error, so I'm thinking it is more correct than the first
> attempt cos its giving me something.
>
> I've given you the detail cos you wouldn't be able to help without it -
> I'll reset asap.
>
> "Various issues" is a bit vague...
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
5a.

Re: RCHGetYahooQuotes erratic behavior beginning Fri, 7/15/11

Posted by: "PLA" optionzz@gmail.com   option2z

Fri Jul 22, 2011 8:14 pm (PDT)



Just a thought:
This happened to me a while back. What I did was turn of automatic
updating. (I leave it off and do a ctrl-F9 when I want to do an update).

To do this, (Excel 2007; I've long ago forgotten how to do it in older
versions), click on the little circle with the MS symbol in the upper right
corner. Choose (at the very bottom) "Excel Options." In the resultant
list, choose: "Formulas" then click "Manual" and unclick "recalculate
workbook before saving" (unless you enjoy very long closing times).

May not be your problem, but it sure made my life better.

Pete A

Amateurs practice until they get it right, experts practice until they can't
get it wrong
(Faith Duck)

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (25)
Recent Activity
Visit Your Group
New business?

Get new customers.

List your web site

in Yahoo! Search.

Yahoo! News

Odd News

You won't believe

it, but it's true

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
Diposting oleh EXCEL FEED LEARNING di 03.34

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)