Markw2004

I am having trouble finding the correct sytax for formulae in creating computed columns. I would like to create three computed columns in my table in my database. The first is an average of the last 20 prices, the second is the average plus one standard deviation and the third is the average minus one standard deviation. I would appreciate any help. I have tried even simple formula but nothing seems to work, i keep getting error messages.




Re: Visual Basic Express Edition Computed Column

js06

You can create your own expression "formula" for each column

Me.MainDataSet.Tables("Maintable").Columns("Average").Expression = "(column1+column2)/2"

The only problem i have had with computed columns is that sql doesn't save formulas. I believe that a program like excel saves the formulas as text and then uses the text. I am not quite sure how it does it. I have also had problems with expressions where when you put the expression in it won't fill in the values all the way down.

You may want to try the code above in a button click event and clear the column out when you save and then add the expression back after.

To clear the expression use this:

Me.MainDataSet.Tables("Maintable").Columns("Average").Expression = ""

Everything that is underlined you need to add your own reference names.

The only other problem i had was when i added the expression it would not fill in the values all the way down the column. So i finally wrote a loop code that selected every single row and then returned.

I hope this helps you






Re: Visual Basic Express Edition Computed Column

Markw2004

Thank you for the reply. It does help, I looked up expressions on the MSDN pages and found some information. But I am still confused. It looks like you are coding this into your program I was using the Table Designer in the IDE. Do you have to use the dataset when your making an expression in the table designer Also it seems that you can only perform calculations on either an entire column or on columns in a single row I need to average the price on 20 rows and only those rows that have the same Ticker symbol and are Row.Date<=20

So for a moving average I would need something like:

Me.MainDataSet.Tables("Maintable").Columns("Average").Expression = "Avg(child(if date=row.date<=20))"

I am probably not even close, but any help would be appreciated.






Re: Visual Basic Express Edition Computed Column

js06

I tried creating computed column formulas in the designer and only ended up with errors at run time. I have not figured out how to handle these errors. But to answer your question you do not need to use "dataset" in the designer. You just use the column names: column1+column2 , etc...

I think that the formula you want may be too complex for the designer. I could be wrong though. Either way i think it is faster to test your formulas in code (if they are complex anyway).

As far as i know the formula you enter in the designer is strictly for the entire column (every row included). If you only had one row then yes it would only compute that row.

You might want to start looking in to creating functions (code). If you are trying to avoid code then you may be out of luck to accomplish what you want.

What i finally did to bypass all this was to use textboxes to store the cell values and run a looping code to evaluate each row and determine if it should calculate it based on certain criteria.

Example: If DateTextBox.formattedvalue <= 20 then

Avg(value1,value2)

End if

This is not the exact formula you would use but it should give you an idea. You would have to declare variables to store your values

I can give you some code but i will need some more info from you.

Will you always have the same 20 rows and update the values or will you add or remove rows If you always have the same 20 rows then you could "hard code" steps to evaluate those rows every time. It would only work for those 20 rows. Otherwise you need to make more "general" code.






Re: Visual Basic Express Edition Computed Column

Markw2004

Thank you again for the replies. I am creating a stock charting tool, so there are alot of Ticker symbols and associated prices which are updated daily. I will play with the designer a bit more but will probably have to resort to using code. I just thought this would be easier, hehe. I may just put all of this into excel and just automate excel from VBexpress. It seems everything I try to do is easier in excel. I wonder if making a trigger might accomplish what I want or maybe even a stored procedure. I have learned alot about VBexpress since starting this project which is great, but still have some major gaps especially when it comes to how SQL Server fits in. Lots of learning to do, thank you again.

Mark






Re: Visual Basic Express Edition Computed Column

js06

What made you want to do it in VB if you can do it in excel Excel has Visual Basic for Application built in, so you might be able to get everything done with excel alone.




Re: Visual Basic Express Edition Computed Column

Markw2004

For a couple of reasons. One is to learn VB. The other is the versatility and ease of use of VB with regards to creating a UI. My end goal is to have a stock screener that also generates a report on the stocks that are recommended. The report would include recent quarterly historicals, a DCF, news & press releases, comps, and certain ratios/statistics that i typically look at. So it would include an RSS reader to get up to date news. And then once a stock is purchased a monitoring program to track when it is time to sell, and that also gives current news. Just incase the stock implodes or explodes, lol.

So I think it might be a stretch for Excel to cover all of that. But most of the feedback i got when i first started this projected indicated that SQL was the best database. It just seems difficult to get things accomplished that are relatively simple in excel. So maybe I will look at using excel as a database, not sure really. I played with automating excel from VB before and it is a little complex but managable.






Re: Visual Basic Express Edition Computed Column

js06

Apparently there are a lot of us out there that can create great things in excel but end up lacking what vb can give. If you get creative enough you can get around your dilemma. Check out a program called Spreadsheet Gear. Imagine taking a spreadsheet and viewing & editing directly with vb.net. No matter what you use just keep in mind that what you want is going to take code. There is no designer that automates everything for you. To get you started on the RSS reader, take a look at your start page in VB Express. (video series) this whole tutorial will give you just what you want.






Re: Visual Basic Express Edition Computed Column

Markw2004

Thanks for the tips. The tutorial was a great help in the beginning and I will probably add alot of it to my project. The Spreadsheet Gear program looks awesome, but expensive. I am not really code adverse, just trying to do things the most efficient way. This has been a great learning process...more to come. I will let you know how i come out on all of this.




Re: Visual Basic Express Edition Computed Column

js06

Markw2004, Check out this link:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1279113&SiteID=1

This will show you how to aquire spreadsheet gear for free. (legally). I use it for something similar to what you want.

I also have my reports generating as data is being input with background calculations.

I think this may be the solution to your problem. With this you can learn VB to polish your spreadsheets.

Since the spreadsheet will handle all your calculation and you can create the sheet already, all you have to focus on is the "Polishing"

I started to create my program with Sql as my database and i found that if you have a design planned out well then your good to go, but if you have a "work in progress" then changes are horribly labor intensive. Working with columns is not the problem it is working with rows. "Constant rows" meaning rows that contain data that will not ever change, or that you don't want the end user to change. You can do all this with Sql or something else but the programming can be so labor intensive you might never get it done.

Right now my program consists of 22 database sheets, which also handle calculations and 3 report sheets that generate on their own all you have to do is choose the report from the menu and it is instantly shown and complete. To your end user it appears that your program is extremely fast and accurate.

I hope this will put you in the direction you are looking for.