PDA

View Full Version : [SOLVED] Using yields from another ss



Tenspeed39355
07-29-2005, 09:44 AM
Here is the problem. Yahoo is using trailing twelve months to figure the yield.
The number they are using is not the true yield. I can download the yield I want to use from ETFs web site. Here is what I want to do. Download 600 symbols and 600 yield numbers. Put them in a ss. I want to open a new ss and type in
25 symbols in column A. I want to be able to copy the yield numbers from the 600 that I have into the new ss and put the yield numbers in the cells beside
the symbol they belong. Can this be done?
Thanks for your help
You have been able to solve any of my problems in the past but I think this is a
bit of a reach.
Max:help

lucas
07-29-2005, 10:09 AM
:hi: Hi Max,

If the information your trying to get from a web page into your spreadsheet is in a table on the web page, you might not have to type anything. Take a look at this kb entry. You would have to figure out which table the data is on.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=576

It will retrieve data from a table and put it in your spreadsheet. Hope this helps.

Tenspeed39355
07-29-2005, 11:32 AM
Hi Steve The data is not in a table format at the web page. I can download the data I need into a ss. What I need is to be able to take the data from the ss and put in into another ss. Example. I have downloaded the symbol and the yield of 600 funds into a ss.
I open a new ss and in column A I put the symbols of 25 funds. I need to get the yield from the master ss and put that number beside the symbol it belongs to.
Master ss NEW SS
BLU 6% AOF
ARK 8% ARK
AOF 9% AWF
Steve In the example above I need to move the yield from the master ss to the new
ss and put the number beside the correct symbol.
Max

lucas
07-29-2005, 12:00 PM
Hi Steve The data is not in a table format at the web page. I can download the data I need into a ss. What I need is to be able to take the data from the ss and put in into another ss. Example. I have downloaded the symbol and the yield of 600 funds into a ss.
I open a new ss and in column A I put the symbols of 25 funds. I need to get the yield from the master ss and put that number beside the symbol it belongs to.
Master ss NEW SS
BLU 6% AOF
ARK 8% ARK
AOF 9% AWF
Steve In the example above I need to move the yield from the master ss to the new
ss and put the number beside the correct symbol.
Max

Sorry I misunderstood. Question? Are the symbols and yields next to each other in the master. What I'm getting at is there has to be some connection for us to make to be sure the right symbol is associated with the right yield. Am I making any sense?

It would be helpful if you could put a couple of example workbooks together(or strip any confidential stuff), zip em up and post them on this thread...sometimes easier to understand if you can see it.

Tenspeed39355
07-29-2005, 12:49 PM
Hi Steve Yes you are making sence. When I down load from the ETFs site the symbol and the yield number are not next to each other. In order for the information I get from ETFs
site works I have to do some things so the data will work in the ss. Steve, I can put the
symbol and the yield together, no problem. I can put the symbol in column A and the yield in column B or anyway you want it. There will be around 600 symbols and yields in the ss after I play around with it for a while. lol In the new ss there might be 25 symbols as I might want to look at just the 25. All I need is for you to set up so that
you put the correct yield next to the correct symbol.
Thanks for your time with this. Yahoo might report the yield as 9% when the yield is
7%. This is a pain as I have to look up the yield from another site one by one. lol
This is killing me.
Max

lucas
07-29-2005, 01:37 PM
Try this tenspeed. there are some NA values which you can ignore for now in the runme file. If this does the job we can probably do something about it. You will have to open the runme file and set the path in the module for the location of your master.xls. Use these 2 files together until you see if it works.

to use it: open the runme.xls and change the file path in the VBE. Then close the vbe and click the button.

Tenspeed39355
08-01-2005, 05:51 PM
Steve I think this will work. I am having a problem with the path for the master ss.
I have put in on my desktop. Could you send me the path. I have it as C:\desktop\MASTER.xls
Max

lucas
08-01-2005, 06:18 PM
Steve I think this will work. I am having a problem with the path for the master ss.
I have put in on my desktop. Could you send me the path. I have it as C:\desktop\MASTER.xls
Max

Hi Tenspeed,
Its probably different on different operating systems. I have windowsXP Home edition and the path to the desktop is:
"F:\Documents and Settings\All Users\Desktop"
I don't have a C: drive so you'll have to change that part. Pretty sure you can find the path like this:
open notepad and write a little something in it. Save it-when it asks where to save it, look for desktop. Save it there and then close or minimize everything and when your at your desktop, find the text file you saved and right click on it, then click on properties. Under the General tab look for "Location" to the right of that is the path to your desktop. Hope thats not too confusing.

Tenspeed39355
08-02-2005, 09:51 AM
Hi Steve I changed the location in the ss you sent to the following.
C:\Documents and Settings\Max Parris\Desktop.
Also when I opened the zip file you sent I saved both the MASTER and the other ss to my desktop. When the master ss would not open I went to the desktop and did a right click and when to properties to check the location. Here is the location that is on the
master, C:\Documents and Settings\Max Parris\Desktop. The macro will not open the master, do you have any idea what I am doing wrong
Max

lucas
08-02-2005, 09:58 AM
Hi Steve I changed the location in the ss you sent to the following.
C:\Documents and Settings\Max Parris\Desktop.
Also when I opened the zip file you sent I saved both the MASTER and the other ss to my desktop. When the master ss would not open I went to the desktop and did a right click and when to properties to check the location. Here is the location that is on the
master, C:\Documents and Settings\Max Parris\Desktop. The macro will not open the master, do you have any idea what I am doing wrong
Max

Hey Max,
examine this line of code in your macro and see if it matches.



Workbooks.Open("C:\Documents and Settings\Max Parris\Desktop\MASTER.xls", True, True)

Tenspeed39355
08-02-2005, 01:15 PM
Steve This is the macro. Please check


Option Explicit
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("C:\Documents and Settings\Max Parris\Desktop\MASTER.xls", True, True)
' open the source workbook, read only
With ThisWorkbook.Worksheets("Sheet1")
' read data from the source workbook
.Range("A1", "B800").Formula = wb.Worksheets("Sheet1").Range("A1", "B800").Formula
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

lucas
08-02-2005, 02:17 PM
Hi Max,

Gets frustrating doesn't it? I got to tell you that the code you just posted works for me if I just change the path. I have added an error handler to it so maybe you can get a little more information. Can you tell me what happens, do you get a debug error and it takes you to the vbe if you click on debug? If so, what line is highlighted. If that doesn't work for you try this code with the error handler to help you find your problem. I've got to tell you I think it is your file path. Are you on a network? Any info you can add to your post might help us find the problem. Here is the same code you posted with an error handler in it. If you get a message try to get all the info from the message you can.



Option Explicit

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
On Error GoTo Errhandler
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("C:\Documents and Settings\Max Parris\Desktop\MASTER.xls", True, True)
' open the source workbook, read only
With ThisWorkbook.Worksheets("Sheet1")
' read data from the source workbook
.Range("A1", "B800").Formula = wb.Worksheets("Sheet1").Range("A1", "B800").Formula
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
Exit Sub
Errhandler:
Select Case Err
Case 68, 75: ' Error 68: "Device not available"
' Error 75: "Path/File Access Error"
MsgBox "There is an error reading drive B."
Case 76: ' Error 76: "Path not found"
MsgBox "The specified path is not found."
Case Else: ' An error other than 68, 75 or 76 has occurred.
' Display the error number and the error text.
MsgBox "Error # " & Err & " : " & Error(Err)
End Select
End Sub



Max, if you know your way around your hard drive, you might try putting these two files in the same directory and you would know the path. It works if they are in the same directory too, but you still have to set the file path correctly. Keep me informed of your progress.

Tenspeed39355
08-04-2005, 05:03 AM
Hi Steve This is a reply from the big dummy. Here is what I found out. In the ss named
run-me in the second tab there is two symbols in A1 and A2 along with the button.
When I pressed the button nothing was happening. The yields were not comming over from the Master ss. I THEN selected the Sheet 1 tab and there was the yields that were comming over from the Master ss. So far so good. Here is was I found by adding symbols and yields in the Master ss when I reran the Button in the Run-me ss the macro is bringing over from the Master ss ALL the symbols and yields.
Steve, What I need is only the YIELDs for the funds I need NOT all the yields.
Example: In sheet 1 in the Run-me ss if I put in A1 thru A5 the following symbols ALL I
need is for the macro to bring over the YIELD for the five symbols like AOF,ARK,ASG,AWF,BHK.
Can this be done??
Again thanks for your time with this. If it can be accomplished it will save a great deal of time
Max

lucas
08-04-2005, 06:21 AM
Hi Steve This is a reply from the big dummy. Here is what I found out. In the ss named
run-me in the second tab there is two symbols in A1 and A2 along with the button.
When I pressed the button nothing was happening. The yields were not comming over from the Master ss. I THEN selected the Sheet 1 tab and there was the yields that were comming over from the Master ss. So far so good. Here is was I found by adding symbols and yields in the Master ss when I reran the Button in the Run-me ss the macro is bringing over from the Master ss ALL the symbols and yields.
Steve, What I need is only the YIELDs for the funds I need NOT all the yields.
Example: In sheet 1 in the Run-me ss if I put in A1 thru A5 the following symbols ALL I
need is for the macro to bring over the YIELD for the five symbols like AOF,ARK,ASG,AWF,BHK.
Can this be done??
Again thanks for your time with this. If it can be accomplished it will save a great deal of time
Max

The idea is to import all the data into sheet 1 and then use data validation on the sheet main to just get the ones you want from the freshly imported data on sheet 1. look on the sheet main for a formula in cells b1 and b2. You can put whatever symbols you wish to selectively get the percentages for in column a and drag the formula down column b you will get a na until you import your data to sheet 1 by running the macro. formula in cell b1 should look like this =VLOOKUP(A1, Sheet1!A1:B30, 2, 0) on sheet "Main"

Tenspeed39355
08-04-2005, 06:51 AM
Hi Ho Steve. IT WORKS, IT WORKS, IT WORKS, THANK YOU.
As I said before I am a little slow. I had to put the VLookup back in B1 as I had taken it out. I made a copy of your last remarks and read it as I changed the VLookup. This is so
cool. You will get and A+.
I have one more question. If I import from the ETFs site the NAV number into the MASTER as well as the YIELD could the macro be chaged to get the two numbers and not just one??
Again thanks for all you help
Max

lucas
08-04-2005, 08:21 AM
Hi Ho Steve. IT WORKS, IT WORKS, IT WORKS, THANK YOU.
As I said before I am a little slow. I had to put the VLookup back in B1 as I had taken it out. I made a copy of your last remarks and read it as I changed the VLookup. This is so
cool. You will get and A+.
I have one more question. If I import from the ETFs site the NAV number into the MASTER as well as the YIELD could the macro be chaged to get the two numbers and not just one??
Again thanks for all you help
Max
:content: Thats great Max. By the way, there's probably a better way to do this, maybe someone will come along with a better solution soon. On your last question, sounds like your trying to add another column of data, like in column C. If thats so then try the new attachment. Col C in the example is formated for percentages just like col B. If you want to change that just select col C in all 3 spreadsheets(1 in master, sheet 1 & main in runme) one at a time and right click and select format cells. select the number tab at top of dialog and select either general or text, whichever does what you want. I changed the macro to import 3 columns to sheet 1 and the vlookup was added to sheet main in column c to get selective data from col c on sheet 1.......hope thats not too confusing.

ps. be sure to back up your old master and runme before trying this.

Tenspeed39355
08-04-2005, 12:45 PM
Steve The only way I can say thanks is to give you an excellent rating and say THANKS
again. I have not tried the last part but I am sure it will work
Max Parris

lucas
08-04-2005, 01:19 PM
Glad I could help you Max. Post back here and let me know how it works out. By the way your username is tenspeed...bicycle enthusiast?

Tenspeed39355
08-06-2005, 06:53 AM
Steve I have ran into a problem. First let me say that everything works just great.
The problem I ran into is when I copy the data from the ETFs web site I have to do a format and some other things so I can put the data in a ss. After removing some things like the underline on the symbol which is a hyperlink I do a copy and paste into a ss.
I then did a copy and paste of the symbol, yield and nav into the MASTER.xls ss.
When I pressed the button in the run-me ss all the data came over to the run-me ss in
sheet1. At this point there is no problem. I then entered some symbols in column A.
and ran the Vlookup. Nothing will come across into column B and C. If I go over to
Sheet1 and TYPE over one of the symbols the data goes in column B and C. I have run
into this before. The VLookup command acts like it does not see the symbol until I
OVER TYPE the symbol then it works. Is there a fix for this.
Max

lucas
08-06-2005, 10:14 AM
Hey Max,
I have tried to duplicate your problem here by copying some thing from the web and pasting it in the master but I can't replicate your problem. Is there any way you could post an example of the 2 workbooks without any crititcal info so I could take a look? you will have to zip them up to be able to post them here. Go down below the place where you type in your post(scroll down) and click on manage attachments...........

Tenspeed39355
08-07-2005, 05:40 AM
Steve I found the problem. When you sent the Master and run-me ss the VLOOKUP command has VLookup only going down to B30. So after playing around I could see that the formula was only looking at the first 30. I changed the 30 to 715 which will cover all the funds. I have a question. Why do I need a MASTER ss? I can take all the symbols, yields and nav prices and put them in Sheet1 of the run-me ss and go from there. That will elimate the macro. What do you think??

lucas
08-07-2005, 06:55 AM
I can't see any reason why that won't work for you Max. I had assumed that the existance of the master was necessary. If the master is not something that other people use on a network, etc. then its not needed. Glad you figured the formula's out....

Tenspeed39355
08-07-2005, 07:21 AM
Steve Again thanks for your time with this. Like I said before I am a little slow but steady.
LOL. I can see that by setting u VLOOKUP I can add more data if necessary.
Max

lucas
08-07-2005, 07:28 AM
Your very welcome Max

Tenspeed39355
08-08-2005, 05:53 AM
Steve Since I have a new formula to play with how about this. Can I use the VLOOKUP command to look over into another ss and get some data to go into the "run-me" ss?
Here is the formula I tried to use
=VLOOKUP('[NAV ETF.XLS]Sheet2'!B3,B3:AX650,50, 0)
I keep getting n/a. The name of the ss is NAV ETF.xls. The symbols are in column B and the data I want is is column AX. I set the formula so that it should cover all the symbols and numbers. There are 630 symbols and nav numbers. What I want the formula to do is look over in the NAV ETF ss and bring back to run-me a nav number that is in column AX. Column AX is 50 columns over from column A. How close am I to
geting the formula correct.
By the way where are you located? I am in Chattanooga,TN
Max

lucas
08-08-2005, 06:15 AM
Hey Max,

I may be wrong on this(someone confirm or correct me please) but I don't think you can use formula's like vlookup across different workbooks, thats why we were using the macro in the beginning, to import the data from a different workbook. You can revert to the macro setup and change things to import from your new workbook into runme. Then set up your vlookup from there.

Tulsa Ok. whats with tenspeed, bicycle reference?

Tenspeed39355
08-08-2005, 06:29 AM
Steve That is a long story on the name . It has nothing to do with a bicycle.
Before I retired I was a professional dog trainer. I had a boarding kennel for dogs and a training school. I have given training seminar over the U.S. to other schools using my methods of teaching a dog the behaviors needed. I started out with German Shepherds and then when to Border Collies. There is a exercise named retrieve over the high jump.
One of the dogs I showed was so fast I named him Tenspeed. A true story

I will try the macro
Max

lucas
08-08-2005, 07:12 AM
I have a good friend who uses Border Collies to work cattle. One evening we were visiting and he sent the dog into the field (no cattle in sight). The dog started down one fenceline and scouted an area of at least 200 acres and in about 15 minutes he came back to the farmhouse/barn area with the entire herd of black Angus cattle. Amazing. He was also adept at getting them into head chutes, etc. What a labor saver. And the dog seemed to love the work. If you know anything about cattle then you know the black angus is a....how shall I put this....difficult and stubborn breed. Deemed not worth the trouble by many. Healthy breed but most arent worth taking the chance of getting hurt to mess with them. The dogs intelligence and desire to work is amazing.

Tenspeed39355
08-08-2005, 09:23 AM
I have been to several hearding contest to watch Border Collies work by whistle.
Hell of a site to watch the dogs obey the whistle commands. Low to high means go right,
high to low means go left. Solid means lay down .
Max

lucas
08-09-2005, 06:36 AM
Max,

If this thread is resolved, please mark it solved using the thread tools link at the top of the page. Let me know how its going.:hi:

Tenspeed39355
08-09-2005, 09:14 AM
Steve Good morning. Here is what I have added to the VLOOKUP commands.
I have added a third column using the VLOOKUP command in the run-me tab.
I have also added another tab named Sheet2. Sheet1 has the data from the ETF site.
The NAV PRICE and the YIELD. In Sheet2 I can put any data I need. For instance I wanted the NAV price from 30 days ago so I went to my ss that has that information in it.
Did a copy and a paste of the 630 nav prices and Nav symbols and put it in Sheet2 comumn A1 and B1. I configured the third column so it looks over into Sheet2 for the information at the same time it looks over into sheet1 for the other information.
Until you helped me with the VLOOKUP I had to put the date in my hand. What a drag.
Again thanks for your help.
I will close out this thread
Max

lucas
08-09-2005, 09:47 AM
Its been a pleasure doing business with you Max. Glad I could help you. See you around the forum. :friends: