Web Query Returning Text But Not Numbers
Oct 3, 2009
I have this web query to retrieve Stock Market data for individual scrips which used to work fine till 2 weeks ago:
[url]
Since then all it returns is the text in the tables. Numbers eg dates and stock prices come as blanks.
These show up alright in the browser (Mozilla as well IE).
Some other tables at this site plugged into query return complete data. eg [url]
I tried many different scrips with same results.
Since it was working fine till 2 weeks ago AND other tables at this site are working fine, I suppose the site changed something.
View 10 Replies
ADVERTISEMENT
Aug 28, 2009
I use web queries to download stock options from MSN Money.
If I manually refresh the queries (right click -refresh) the downloaded
prices are in number format.
If I use:
View 3 Replies
View Related
Oct 30, 2013
I am trying to identify the row(s) where a match occurs when there can be multiple occurrences of the match.
A
B
C
1
john brown
Brown
True
2
Cathy Smith
3
Brown excavating company
4
XYZ Corp
5
Brown Advisors Inc.
The first test I ran in cell C1 with the information above was to determine if "Brown" was located in the range of A1:A5 using formula:
=IF(ISNUMBER(MATCH("*"&B1&"*",A1:A5,0))=TRUE,"True","False")
In the case of the word "Brown" it occurs in the range of A1:A5 three times, so the result of the above formula would be "True"
Now what I would like to do is return the locations, in this case the row number(s), where the word brown is contained range of A1:A5 because in the case of the word Brown, it occurs three separate times in the range of A1:A5. I would like the resulting value of the formula in this case to be "1, 3, 5" indicating the word Brown occurs in rows 1, 3, and 5. The formula also needs to work in case there is only one match as well.
I tried using the below array formula:
={MATCH(FALSE,ISERROR(SEARCH(B1,'Working List of Vendors'!$A$1:$A$5)),0)}
But it would only return the first occurrence of the match which in this case would be row 1, or "1"
View 8 Replies
View Related
Sep 7, 2009
I have a query which is giving me some trouble when returning the data from ms query. The query was written in mysql query browser and returns 2 columns of data - a date and a number. MS Query correctly processes the query but when the data is returned to excel only the second column is returned. Here is the query:
SELECT
(SELECT
max(l.the_date)
FROM
nc_view_date_functions AS l
WHERE
l.week_of_year = d.week_of_year
AND l.yyyy = d.yyyy
) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM
rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d
ON r.application_date = d.the_date
GROUP BY
d.yyyy, d.week_of_year;
View 2 Replies
View Related
Jun 23, 2013
I am trying to get data in excel sheet using web query from the following web link:
But I the data returned is a 'hypen' (i.e. a dash symbol). I am getting the row/column headers but not the figures or the numbers under them.
View 2 Replies
View Related
Jun 23, 2013
I am trying to use queries I have been running in MS SQL Server Management Studio, to return data in Excel where it would display as pivot. Some queries I was able to use through Excel but few others are not returning anything.
I am doubting it has something to do with the query itself - but they are displaying results properly in MS Query, they are just not returning any data to Excel.
View 3 Replies
View Related
May 9, 2012
I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.
I pass in the query name and the sheet name into qryName and sheetName name.
Code:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
[Code].....
View 1 Replies
View Related
May 5, 2014
this example runs a named query in Access but it does not return the header row, just the data.
Code:
Sub RunAccessStoredQuery()
Dim objMyConn As New ADODB.Connection
Dim objMyRecordSet As New ADODB.Recordset
Dim strSQL As String
[Code].....
View 1 Replies
View Related
Jun 30, 2014
I have a simple macro which extracts EUR rates from a website but the number format as appears in Excel is a disaster.
I am attaching the file : Querry EUR Rates.xlsm
How to format the rates accordingly.
Also posted here: [URL] .....
View 1 Replies
View Related
May 19, 2009
I'm Importing Columns from one sheet to another in the same workbook. imports good But:
1- Some columns with first records/rows blank do not import at all.
2- I have a column with some letter/numbers combinations and single numbers at times. Those single numbers are not imported. And half my data in that column are single numbers.
View 9 Replies
View Related
Dec 10, 2013
I have a list of data in Column A of a spreadsheet that is all at different lengths.
I would like to return the numbers from this (see below) seperately and also everything after the last number so returning "green"
Example:
EAST MIDS, YORKSHIRE0112012green
EAST MIDS, YORKSHIRE0112012red
EAST MIDS, YORKSHIRE0112013black
EAST MIDS, YORKSHIRE0112013brown
View 1 Replies
View Related
Jul 5, 2012
I have a row of upto 20 numerical entries, (1,1,2,2,10,10,3,3,4,5,6,7,8,12,1,1,7,8,9,11) what formula do I use to go about getting excel to return the two highest numbers?
In this example I'd want excel to return the numbers, 12 and 11
View 1 Replies
View Related
May 2, 2007
I have 2 columns of data, one the description, the other the number, both positive & negative numbers, (the columns are also not next to each other). I need to average only the negative numbers according to the description.
I can sum only the negative numbers if I separate out the different descriptions, but cannot average the figure:
=SUMIF(E2:E300,"
View 9 Replies
View Related
Oct 10, 2006
I am setting up a spreadsheet with two columns. The first column will contain a persons name the second column will contain four numbers.
Name Numbers
Joe 1,3,12,24
John 4,12,23,24
Jill 6,14,19,26
I need to be able to search to find the following: the correct sequence of numbers e.g. if 1,3,12,24 were pulled out then that search is easy. If I was looking for someone who had the numbers 1,12,24 then I have a problem. I was thinking about putting the four numbers in to four separate columns but my Excel knowledge is poor.
View 4 Replies
View Related
Jul 19, 2014
I need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.
I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.
Example
What I need
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
A-123 bca loreal skin care anti acne anto acne soap
Main file
Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales
y-123 abc lux 120 110 105
A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx
Category files
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
View 1 Replies
View Related
Jun 4, 2012
I have two (2) different values in the same column one value is text (INV) the other is a time date stamp 05/18/2012 10:48:32. The text i want to return in a seperate column for these two is if it is INV then the result is "PENDING" if it is a date 05/18/2012 etc. then the result would be "PAID" example:
Payment Status
05/08/2012 10:30:12
INV
05/17/2012 08:27:37
INV
and so on...................
View 9 Replies
View Related
Jul 11, 2014
Attached is the sample working file, i have a user data embedded into the comments columns for each user reference. However, i need to extract the wrapped comments column.
Text-to-columns-sample.xlsx
View 14 Replies
View Related
Mar 18, 2014
with the table give below, i need a formula to get the sustainability. Eg. cell D1 =VG and D2=G, then D3 value should be G (as per the table below).
Prev Yr
Current Yr
Sustainability
EX
EX
EX
VG
G
G
G
EX
VG
View 2 Replies
View Related
Feb 18, 2010
I want to find the number within this webpage and report it to a Cell in excel....
View 6 Replies
View Related
Oct 25, 2013
I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!
Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?
View 1 Replies
View Related
Mar 21, 2006
here is my text strong:
PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
101915744. MMU - DO NOT BREAKAP:
I want to return text starting with ALLOC but stop at and not include AP.
View 10 Replies
View Related
Jan 18, 2007
| item1, item2, item4 |
Elsewhere I have a set of points for these like this:
| item1 | 4 |
| item2 | 2 |
| item3 | 3 |
| item4 | 1 |
(the |'s represent cell borders, if it isn't obvious)
What I want is a formula that can grab the text, then produce a total score. So, for my example, the result would be 7, totalling the scores for item1, item2, and item4.
I know that the score lookup is trivial (just a VLOOKUP), but I can't figure out how to do the scan for the data from a single cell. I want the items to remain in a single cell if possible.
View 9 Replies
View Related
Sep 20, 2006
I have a large text file (greater than 64k lines) which I need to import into excel. I only need anything imported from the text file that is for dates I specify. So I would like to run a line by line query and only import the lines I need.
The dates are always in position 43-50 (dd-mm-yy) in the text file itself, so the code would have to look at that section, see if the date is within the range specified and only import if it is (and then move onto the next line).
View 5 Replies
View Related
Jan 29, 2009
I am importing data from an access database that is continually refreshed. Some of the data that is returned to my Excel sheet is in binary, where a "0" represents an unchecked box on the database program and a "1" represents the same box, but with a check mark. I want to change the data so that when it is updated on my Excel sheet it is converted to text as either "Yes" or "No" instead of "1" or "0", respectively. I'm sure this is pretty elementary stuff but I have tried VBE and cell formulas and can't come up with an answer.
View 3 Replies
View Related
Apr 14, 2014
I am looking to return text based on the maximum value in a table.
Dog
2
Cat
10
Horse
4
Fish
27
Lion
1
Let's call the column with the animals over it "A" and the one with the numbers "B", and the rows are 1-5 to make explanations easier
I am looking to have a formula return the name of the animal with the highest three numbers (fish, cat, horse), but don't need the numbers. I am pretty confident this would be an index match formula, but I'm not too familiar with those functions.
View 1 Replies
View Related
Oct 6, 2009
This is probably a pretty basic question, but I can't find an answer in Excel help or on the interwebs. I have an IF formula and I'd like 0 returned if the statement is false (e.g. =IF(A1<>0, A1, 0)); however, I would like the 0 returned as text. I've tried the TEXT function and using ` in different places, but all to no avail.
View 7 Replies
View Related
Dec 11, 2009
I have 3 columns of numerical data. In my fourth column, I would like to return the minimum value from the 3 previous columns (=min). However, instead of returning the lowest number, I would like it to return the column title (=text) of the lowest number. I haven't been able to locate a function that does this.
View 4 Replies
View Related
Mar 28, 2006
I am compiling data from several different, variable, worksheets. I am using
concatenate to add the name of each worksheet into a formula to pull the
appropriate data.
However, it is returning the text of the formula instead of
the results of the formula. How can I make it calculate the result instead of
simply displaying the formula text?
View 14 Replies
View Related
Jun 6, 2014
Store 1
Store 2
Store 3
Store 4
Top Selling
Second
Third
Fourth
[code]....
I am trying to find two formulas:
Formula 1: Formula 1 will be entered into rows 6:9 (in this example). I need it to return the highest selling product (by store) in order form highest selling to lowest selling. I have a makeshift formula already but it cannot report in order (only the highest selling) and it cannot get past the one return index( limitation. When it finds two high sellers it prioritizes the ranking based on how well that product has sold across the other stores. Product 1 sold 13 times across all stores. Product 2 sold 9 times across all stores. So if product 1 & 2 tie for Store 3 (as shown in the example) Product 1 would be chosen based on its sales across the row.
Formula 2 : Same as formula 1 but with Stores (in column 6-9). It reports the highest selling store and treats ties the same way as formula 1.
View 4 Replies
View Related
Jun 17, 2009
I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes.
A B C E F G
M S Get value from G M S 5G
M T M T 6M
M U M U 7H
M V M V 8K
View 9 Replies
View Related
