Using formula's to better layout a sheet that is linked to a External Web query

[suprsnipes]

The problem I'm having.

The sheet that I am placing the data on has blanks on it because I don't know of any other way to display the data after it is retrieved using Excels external website query. What I have been doing is copying the data from one sheet to another using formula's then using the Data / Filter function selecting (NonBlanks).

There is probably a much better way to display the information, I'm not sure at which point you could do this I guess with VBA code you could but that is out of my league so my only other option is using formula's so that would mean using sheet 1 to get the data and using sheet 2 to display it in a better fashion. Can this be done?

Any help would be appreciated.

 

[Aladin Akyurek]

Care to post a tiny sample from the area you copy by means of formulas to a different sheet and the formula you invoke?

 

[chuckchuckit]

I do a considerable amount of Website query every day. Likely there are many different ways to process the information. The end result of reaching the goal is the whole idea IMO.

The query comes in "their" cell arrangement so I also use formulas in different cells referencing their cells, to get only the data I need. (Skipping all those blank cells as you say). I am thinking you are doing the same and would think there is nothing wrong with that approach.

But after you get the initial cells you want, one has to do something with that info.

If the referenced cells is all you need, then perhaps you are done. But I tend to do many things with those cells of information and that is where "VBA Macros" become very useful and powerful for processing those cells.

I would suggest if you are having any difficulty processing the additional data needs, try some very simple steps you currently are doing using the VBA macro recorder. It is easier than one thinks, and perhaps may be a benefit to you.

Chuck

 

[suprsnipes]

Display after import

 

[suprsnipes]

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Time	Currency	Event	Importance	Actual	Forecast	Previous
2	Mon	16:00	Currency: jpy	JPY Leading Index (JAN )		10190.00%	102.30	10140.00%
3	Mar-07
5		20:30	Currency: eur	EUR Euro-Zone Sentix Investor Confidence (MAR)		1710.00%	17.2	1670.00%
6	Tue	10:00	Currency: nzd	NZD QV House Prices (YoY) (FEB)		-1.70%		-1.50%
7	1-Mar
8		10:50	Currency: jpy	JPY Trade Balance - BOP Basis (Yen) (JAN)		-394.5B	-371.8B	768.8B
10		11:01	Currency: gbp	GBP BRC Sales (FEB)		-0.40%	0.70%	2.30%
11		11:01	Currency: gbp	GBP RICS House Price Balance (FEB)		-26%	-26%	-31%
13		11:30	Currency: aud	AUD NAB Business Conditions (FEB)		-2		-6
14		11:30	Currency: aud	AUD NAB Business Confidence (FEB)		1400.00%		400.00%
15		17:45	Currency: chf	CHF Unemployment Rate (FEB)		3.60%	3.70%	3.80%
17		17:45	Currency: chf	CHF Unemployment Rate s.a. (FEB)		3.40%	3.40%	3.50%
19	Wed	0:15	Currency: cad	CAD Housing Starts (FEB)		181.9K	174.0K	170.6K
20	1-Mar
wdailyfx

 

[suprsnipes]

Excel Workbook
	A	B	C	D	E	F	G	H
1	Day	Time	FX	Description	Level	Previous	Forecast	Actual
2	Mon	16:00	JPY	Leading Index (JAN )		101.4	102.3	101.9
5		20:30	EUR	Euro-Zone Sentix Investor Confidence (MAR)		16.7	17.2	17.1
6	Tue	10:00	NZD	QV House Prices (YoY) (FEB)		-0.015		-0.017
8		10:50	JPY	Trade Balance - BOP Basis (Yen) (JAN)		768.8B	-371.8B	-394.5B
10		11:01	GBP	BRC Sales (FEB)		0.023	0.007	-0.004
11		11:01	GBP	RICS House Price Balance (FEB)		-0.31	-0.26	-0.26
13		11:30	AUD	NAB Business Conditions (FEB)		-6		-2
14		11:30	AUD	NAB Business Confidence (FEB)		4		14
15		17:45	CHF	Unemployment Rate (FEB)		0.038	0.037	0.036
17		17:45	CHF	Unemployment Rate s.a. (FEB)		0.035	0.034	0.034
19	Wed	0:15	CAD	Housing Starts (FEB)		170.6K	174.0K	181.9K
fxreader

This is what it looks like on the second sheet when I use the NonBlanks feature. What I want to do is re-create the sheet so I don't have to use NonBlanks. One additional task I want to be able to do with the above is filling in each of the days.

 

[suprsnipes]

The formula's I am using are very simple and are as follows;

fxreader

<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>A2</td><td>=IF(wdailyfx!A2="Mon",wdailyfx!A2,IF(wdailyfx!A2="Tue",wdailyfx!A2,IF(wdailyfx!A2="Wed",wdailyfx!A2,IF(wdailyfx!A2="Thu",wdailyfx!A2,IF(wdailyfx!A2="Fri",wdailyfx!A2,IF(wdailyfx!A2="Sat",wdailyfx!A2,""))))))</td></tr><tr><td>B2</td><td>=IF(wdailyfx!C2<>"",wdailyfx!B2,"")</td></tr><tr><td>C2</td><td>=IF(wdailyfx!C2<>"",LEFT(wdailyfx!D2,3),"")</td></tr><tr><td>D2</td><td>=IF(wdailyfx!C2<>"",MID(wdailyfx!D2,5,100),"")</td></tr><tr><td>E2</td><td>=IF(MID(wdailyfx!E2,13,6)="medium","Med",IF(MID(wdailyfx!E2,13,6)="high","High",""))</td></tr><tr><td>F2</td><td>=IF(wdailyfx!H2<>"",wdailyfx!H2,"")</td></tr><tr><td>G2</td><td>=IF(wdailyfx!G2<>"",wdailyfx!G2,"")</td></tr><tr><td>H2</td><td>=IF(wdailyfx!F2<>"",wdailyfx!F2,"")</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4