I'm working on getting company data from yahoo into excel. I can do this manually going to Data > From web etc using site: [URL] ... for example then just import from there.
I recorded this macro:
Sub OriginalImport()
'
' Macro2 Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
[Code] .......
This works fine. But i want to be able to manually select the stock code, start month, date and year and end month, date and year through excel (this is already setup easy) and have this update the URL that is pasted into here: [URL] ....
In cell A1 i have used this formula: [URL] ......
This spits out something like this: [URL] .....
That will automatically update the correct URL name to enter into the correct section of the macro. But here's where i hit a snag I've tried:
With ActiveSheet.QueryTables.Add(Connection:= _
= Range("A1") _
, Destination:=Range("$A$2"))
But it doesn't like the =Range("A1") bit. Once i solve how to get this URL into the connection web-search space then i can control what my macro searches for. What do i have to do to the
rows 1 and 2 are used for my parameters. row 1 being used for descriptions: Ticker Symbol, Start Date, End Date, and pulling in the close price and volume for each ticker symbol with the selected date range. row 2 being used for entering the parameter info. row 2 Would read (as an example)
MSFT, 1/1/2002, 1/1/2003, close price, volume.
For each ticker in column A, add a separate wksheet named the ticker symbol and pull in the corresponding info.
So the final result for MSFT, would be an added wksheet named MSFT, with the colums headers being the close and volume, and the rows being the date parameters set in row 2.
I have a code which get data from finance yahoo. When I run the code , it is giving User defined type not defined.
Private Sub UserForm_Click() Dim URL2 As String: URL2 = "http://finance.yahoo.com/" ' to get data from the url we need to creat a win Http object_ ' tools > references > select Windows Win Http Services 5.1
I have created a stock sheet on Sheet1, i have say 100 items and each item has a min and max stock order. Once the item hits a min low, the last cell (O) will display an order needs to be placed.
I was wondering if there way a way that on sheet2, it can calculate all the data on sheet1 and if there is stock that needs to be ordered, it will appear on sheet2?
This way i can just print sheet2 and send it to the supplier without having the entire 100 items displayed -if it does not need to be ordered?
I have a spreadsheet with several stock symbols in a1:a25
I want to look up the last price and put it into b1:b25 for each stock in a.
I have tried all aspects of http://download.finance.yahoo.com/d/...k symtol"&f=l1 as a query but can not get it to work without specifying the symbol in the url for each symbol.
I am assuming that I need a macro of query that can look up the range to provide the prices.
The reason is that the list of symbols changes daily and I need to look them up daily.
here is what i have but i want it to adapt it you use any company by using a list of companys in a worksheet "StockData". So the company names start in "A2" and go down the column. So i want to run the download then for the modual to go to the next company in the list without having to have a sub() for each company....
I have stock data values being updated on my sheet every 10 seconds in one Cell (E5).
I am looking to indicate a change in that cell value by changing cell color (or could be any easily programmable indicator). I want to change the cell color on first change of value but not subsequent refreshes. Stock data is numeric in string form. colors used not important as long as there is a visible change.
I Have tried playing with sheet change events but no progress as data is not manually entered.
Below are the weekly closing prices for a stock market index. I'm looking to use conditional formatting to highlight in green "buy" signals and in red "sell" signals.
Buy Signal: If the index rises 4% above any previous low point.
Sell Signal: If the index falls 4% from any previous high point.
DateAdj Close 3/10/201472.16 3/3/201473.55 2/24/201472.83 2/18/201471.92 2/10/201471.43
[code].....
In the above example a Buy Signal is generated on 1/13/2014 because the market closes 4% above the low of 68.24 on 12/9/2013.
I look at stock charts that allow an overlay of "zigzag patterns." This zigzag links price extremes with a line. The price extremes are defined according to "wave size" which is described as follows:
"Wave Size: The Most Important Setting... and the thing you will want to tinker with -- is Wave Size. Wave Size is a number that defines a high or low price extreme, by stating the number of surrounding bars on either side that must not be higher or lower than the candidate extreme. Another way to think of it is: Wave Size + 1 is the minimum distance between extremes of the same high or low type."
Here is an example of what a stock chart with these zigzags drawn in looks like. [Click on the image to zoom in and see it better.]
[URL]
The black diagonal line is built using a "wave size = 5" using the above definition. And the green one is, "wave size = 20"
I am trying to build this into a spreadsheet so I can play with the data. If I have four columns: (a:open)/(b:high)/(c:low)/(d:close) I cannot figure out how to build the columns after to do this zigzag. I understand what the line is, but it just doesn't make sense to me how to derive it from the data. how to get a column n that has the value of the zigzag (with wave size = 5 for example) I
I recently created a spreadsheet to calculate the value of certain stocks. I chose to do the s&p 500. It can take a while to manually get the info I need, so I looked to videos on vba writing and different solutions. I do not want to use web queries. Rather, I'd like to use a simple formula that pulls the necessary "single" item from a webpage and puts in a cell, then I will simply copy and paste that cell for all stocks.
from my online endeavors and inquiries, I put together this POS:
Function vi_Price(ByVal Symbol As String) Dim IE As New InternetExplorer IE.navigate "[URL]... Do DoEvents
[Code]...
Using Google Chrome, I tried to find the "element" that I need. In this case, the last close price at the top of the webpage. It's hard to distinguish which it is. If I can just get the code to work for the price, I think I can modify it for the other 3 items I need.
I have Excel 2007. I have a spreadsheet with email addresses in it surrounded by other text. I want to automatically highlight in yellow each row containing the words gmail, yahoo, hotmail and so on.
I CTRL-H then enter the info as you see it in the pic attached. When I click Replace All it says "that function is not valid". It works like a charm for every other word I test it with.
i have a script to import a workbook which works fine, but when the data is imported i would like to have all the data in the cells aligned to the left of the cells, as im new to vba i can work this out.
Below is the script i'm using to import.
I would like this to aligned all cells to the left and centered when imported.
Sub tst() With workbooks.open("C:example.xls") with .sheets(1).usedrange thisworkbook.sheets(1).cells(rows.count,1).end(xlup).offset(1).resize(.rows.count,columns.count)=.value end with .close False End with End Sub
I want to import data from the web into Excel, but the data I want come from the result of a query - I have to enter a few parameters and get the results. I would like to import these results into Excel but although I can enter the website for Excel to look at I cannot figure out how I can enter the parameters to generate the results.
I am having a little trouble with this piece of simple code that just seems to be not working the way i want to. I can't figure why it won't work.
Private Sub CommandButton1_Click() Sheets("Global").Select Range("B5:F19").Copy Range("B25").PasteSpecial (xlPasteAll) Range("B5:E5").ClearContents Range("B7:E7").ClearContents Range("B11:E11").ClearContents Range("B13:F13").ClearContents Range("B17:D17").ClearContents Range("B19:D19").ClearContents End Sub
for some reason if i try to do Range("B5:F19").Select and then do Selection.Copy, I get an application error message. This has never happened before and for some reason it just started to act up. If you want to know what it does when I run this macro.. it actually doesn't go to Sheet "Global" it stays in the same sheet and does all the copying/pasting/clearing in the same sheet as the button is. I made sure that the tab is "Global" and i have confirmed caps. spaces everything.
I have been asked to develop a dashboard for my company. The data source for my dashboard is Tally ERP 9.The problem is I am not getting how to access this data into excel. I have tried MS Query but the imported data was not what I was expecting.
I have 5 or 6 macros set up to import stats from the internet to specific sheets. I want all of my imported data to be pasted starting at A1. However when i run these macros, the data IS pasted at A1, but when i run it a second time (or the next day after the pages have been updated), it pastes the data BESIDE the previously pasted stuff. here's what i mean..
say my imported data spans columns A to S.. everything's fine. but tomorrow i want to update the data so i run the macro again. well, it pastes the new data to A1 and again it spans from A to S like it's supposed to, but now it also tacks on the same paste job from S to, AG for instance.here's the code... There are currently 5 macros that have this problem. I thought by selecting A1 as the starting point, i could alleviate all of this but apparently not.
I have the following code that I cobbled together from various recordings of data imports and queries. The problem seems to be in my WHERE statment. I get a "Run-time error '1004': Data type mismatch in criteria expression."
I need to create an statement for my clients. Book A has all of the account balances. Still in book A, the account number is in column A, and balance is in column H. I just need to import from Book A into Book B, based on account number. Now on Book B, the account# is in column A and the balance needs to go to column B.
I am using Microsoft Excel 2003 and I am trying to import data from one worksheet to another. I found out how to import the data, but I can not find out how to get the data that I am importing to go into the labelled cells that I want them to go into. Is there any way that I can get Excel to do this, or am I searching for something that can't even happen?
can use data from an Access table in my Excel functions without importing the Access data into the wookbook.
I set up a connection to the Access table that I need, but don't know what the syntax would be to reference those fields in functions in my Excel sheet (or if it's even possible).
My problem is that the Access table is large and I want to keep the size of the Excel workbook small.
have been trying to work out how to copy data from a website into a worksheet. For some reason the Data > Import External Data > New Web Query route does not work so I have tried the code below but cannot get this to work either.
What I am trying to achieve is to copy each of the various tables on the page into Excel.
Dim WebCopy As Object Dim WebtitleCopy As Object Set WebCopy = Sheets("Sheet2") WebCopy.Cells.Clear
I need to import data from an Access table (which is straightforward enough), but there's a snag. All the rows are dated (10 rows per day) and I want to start from a specific date and get everything after that date.
Anyone any ideas if this is possible and, if so, how to do it?
