• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

MySQL excel add in

I have build a large MySQL database of financial information (mainly historical daily price data) that I would like to make an excel add in for. Ideally, any user of it could enter a formula into a cell that contained the start and end date of what I was looking for the table it was in and the name of the object. I know I could do this for one cell for one specific date but it is possible to query the database for a range of values. I do not have too much experience with excel.  
0
kbreaux25
Asked:
kbreaux25
  • 5
  • 2
1 Solution
 
RobOwner (Aidellio)Commented:
Sure thing.  I'll go through conceptually what you need to do before getting to the detail:

On one sheet import part of a table of data from your mysql database (i use odbc) into excel, remembering that excel can only have a maximum of 65535 records.

On another sheet in excel you designate the cells you want the user to type data into ie:
A1 - is the start date
A2 - end date
A3 - table
A4 - object

also need to add a button (more later).  When the user clicks the button a macro will run in the background to change take the values the user has entered, validate them as required, and change the query of the mysql data table you imported into the first sheet to reflect the users input.  it would then refresh the data.
0
 
RobOwner (Aidellio)Commented:
What do you think?
0
 
kbreaux25Author Commented:
Importing the data wont really work, there are over 40 tables and some of which have over 200,000 records. I was thinking of making a function that could return an array of values for a given date range and ticker and enter those in an array. I was goin to query the database in VBA. The problem is that I dont think a user defined function can alter cells besides the existing one their in. A way around this would be to let the users enter the dates themselves and just have a function per date per cell. Or another solution is to write a macro in a standalone worksheet that takes certain paramaters in cells and dumps the data onto the sheet.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
RobOwner (Aidellio)Commented:
Your not so much importing the data as setting up the table in the format you want and then changing the query using vba.  Based on the users input you could change this querytable sql.
0
 
RobOwner (Aidellio)Commented:
How are you going with this project?
0
 
kbreaux25Author Commented:
I came up with some sort of solution. To get data from the database for one datapoint that is entered as a formula and speficies what you want. For data for multiple dates and files I made a gui form that allows you to select what you want and what date ranges you want and dumps it on the spreadsheet. I realized that a formula in one cell that could manipulate and return data to several cells would require some very complicated programming from what i read. Since add in formulas can only technically modify the cell they're in.
0
 
RobOwner (Aidellio)Commented:
Glad you found some kind of solution and good to know the results you found
0
 
Computer101Commented:
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now