Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
I need some help automating a row insert from one sheet to a second sheet. I
think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
How do you update your stock quotes sheet?
What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
The stock prices are updated via the MSNStockQuote function add-in. One
click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
Can someone please give me a hand. It looks like Martin got busy on
something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
I think the best way to try and do it is as follows
write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
Martin,
Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
Tim,
I tried to download the file but it kept on hanging on my so i will have to leave it for now anyway good luck in your quest. -- Hope this helps Martin Fishlock "tfrentz" wrote: Martin, Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
Martin,
I'm not sure if what I'm trying to do is even possible.....extract the code of an add-in and put it into my own macro. Have you ever done this before? Where would I look within Excel or elsewhere (some internal file on my computer) to get this code from the MSNStockQuote? Thanks, Tim "Martin Fishlock" wrote: Tim, I tried to download the file but it kept on hanging on my so i will have to leave it for now anyway good luck in your quest. -- Hope this helps Martin Fishlock "tfrentz" wrote: Martin, Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
Tim,
You don't want to get the code all you need is to call to the function/subroutine in the add in... Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. updatestockquotes ' name of macro/subroutine ' find total row in Quotes -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "tfrentz" wrote: Martin, I'm not sure if what I'm trying to do is even possible.....extract the code of an add-in and put it into my own macro. Have you ever done this before? Where would I look within Excel or elsewhere (some internal file on my computer) to get this code from the MSNStockQuote? Thanks, Tim "Martin Fishlock" wrote: Tim, I tried to download the file but it kept on hanging on my so i will have to leave it for now anyway good luck in your quest. -- Hope this helps Martin Fishlock "tfrentz" wrote: Martin, Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
Martin,
Thanks for responding, but your response doesn't help me at all. I mentioned before that a call to the MSNStockQuote add-in returns a quote to the spreadsheet. The function reference for it is: MSNStockQuote(Symbol,Property,CountryCode) So, if I call it within my macro, I have to supply the parameters for 1 quote. This is different from what I'm trying to do which is update all my quotes at once. This can only be achieved presently by clicking the "Update Quotes" button on the MSNStockQuote toolbar. What I want to do is reference this "Update Quotes" button within my macro. If you download the MSNStockQuote add-in, you'll see what I mean. http://www.microsoft.com/downloads/d...displaylang=en Tim "Martin Fishlock" wrote: Tim, You don't want to get the code all you need is to call to the function/subroutine in the add in... Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. updatestockquotes ' name of macro/subroutine ' find total row in Quotes -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "tfrentz" wrote: Martin, I'm not sure if what I'm trying to do is even possible.....extract the code of an add-in and put it into my own macro. Have you ever done this before? Where would I look within Excel or elsewhere (some internal file on my computer) to get this code from the MSNStockQuote? Thanks, Tim "Martin Fishlock" wrote: Tim, I tried to download the file but it kept on hanging on my so i will have to leave it for now anyway good luck in your quest. -- Hope this helps Martin Fishlock "tfrentz" wrote: Martin, Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
Hi,
If there is anybody out there who knows anything about referencing a button from an add-in in a macro, please let me know. I think I lost Martin. thanks, Tim "tfrentz" wrote: Martin, Thanks for responding, but your response doesn't help me at all. I mentioned before that a call to the MSNStockQuote add-in returns a quote to the spreadsheet. The function reference for it is: MSNStockQuote(Symbol,Property,CountryCode) So, if I call it within my macro, I have to supply the parameters for 1 quote. This is different from what I'm trying to do which is update all my quotes at once. This can only be achieved presently by clicking the "Update Quotes" button on the MSNStockQuote toolbar. What I want to do is reference this "Update Quotes" button within my macro. If you download the MSNStockQuote add-in, you'll see what I mean. http://www.microsoft.com/downloads/d...displaylang=en Tim "Martin Fishlock" wrote: Tim, You don't want to get the code all you need is to call to the function/subroutine in the add in... Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. updatestockquotes ' name of macro/subroutine ' find total row in Quotes -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "tfrentz" wrote: Martin, I'm not sure if what I'm trying to do is even possible.....extract the code of an add-in and put it into my own macro. Have you ever done this before? Where would I look within Excel or elsewhere (some internal file on my computer) to get this code from the MSNStockQuote? Thanks, Tim "Martin Fishlock" wrote: Tim, I tried to download the file but it kept on hanging on my so i will have to leave it for now anyway good luck in your quest. -- Hope this helps Martin Fishlock "tfrentz" wrote: Martin, Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
Hi,
I'm still working on this, and haven't found a solution. I'd really appreciate some help from you macro experts. Is there a way to reference a button from an excel add-in - specifically I'm trying to reference the MSNStockQuote 'update quotes' button. thanks, Tim "tfrentz" wrote: Hi, If there is anybody out there who knows anything about referencing a button from an add-in in a macro, please let me know. I think I lost Martin. thanks, Tim "tfrentz" wrote: Martin, Thanks for responding, but your response doesn't help me at all. I mentioned before that a call to the MSNStockQuote add-in returns a quote to the spreadsheet. The function reference for it is: MSNStockQuote(Symbol,Property,CountryCode) So, if I call it within my macro, I have to supply the parameters for 1 quote. This is different from what I'm trying to do which is update all my quotes at once. This can only be achieved presently by clicking the "Update Quotes" button on the MSNStockQuote toolbar. What I want to do is reference this "Update Quotes" button within my macro. If you download the MSNStockQuote add-in, you'll see what I mean. http://www.microsoft.com/downloads/d...displaylang=en Tim "Martin Fishlock" wrote: Tim, You don't want to get the code all you need is to call to the function/subroutine in the add in... Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. updatestockquotes ' name of macro/subroutine ' find total row in Quotes -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "tfrentz" wrote: Martin, I'm not sure if what I'm trying to do is even possible.....extract the code of an add-in and put it into my own macro. Have you ever done this before? Where would I look within Excel or elsewhere (some internal file on my computer) to get this code from the MSNStockQuote? Thanks, Tim "Martin Fishlock" wrote: Tim, I tried to download the file but it kept on hanging on my so i will have to leave it for now anyway good luck in your quest. -- Hope this helps Martin Fishlock "tfrentz" wrote: Martin, Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
bump....anyone have any answers for this?
"tfrentz" wrote: Hi, I'm still working on this, and haven't found a solution. I'd really appreciate some help from you macro experts. Is there a way to reference a button from an excel add-in - specifically I'm trying to reference the MSNStockQuote 'update quotes' button. thanks, Tim "tfrentz" wrote: Hi, If there is anybody out there who knows anything about referencing a button from an add-in in a macro, please let me know. I think I lost Martin. thanks, Tim "tfrentz" wrote: Martin, Thanks for responding, but your response doesn't help me at all. I mentioned before that a call to the MSNStockQuote add-in returns a quote to the spreadsheet. The function reference for it is: MSNStockQuote(Symbol,Property,CountryCode) So, if I call it within my macro, I have to supply the parameters for 1 quote. This is different from what I'm trying to do which is update all my quotes at once. This can only be achieved presently by clicking the "Update Quotes" button on the MSNStockQuote toolbar. What I want to do is reference this "Update Quotes" button within my macro. If you download the MSNStockQuote add-in, you'll see what I mean. http://www.microsoft.com/downloads/d...displaylang=en Tim "Martin Fishlock" wrote: Tim, You don't want to get the code all you need is to call to the function/subroutine in the add in... Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. updatestockquotes ' name of macro/subroutine ' find total row in Quotes -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "tfrentz" wrote: Martin, I'm not sure if what I'm trying to do is even possible.....extract the code of an add-in and put it into my own macro. Have you ever done this before? Where would I look within Excel or elsewhere (some internal file on my computer) to get this code from the MSNStockQuote? Thanks, Tim "Martin Fishlock" wrote: Tim, I tried to download the file but it kept on hanging on my so i will have to leave it for now anyway good luck in your quest. -- Hope this helps Martin Fishlock "tfrentz" wrote: Martin, Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one sheet to a second sheet. I think a macro is needed, but I'm not sure how to set one up as I've never worked with them, nor do I know how to program it. I'd appreciate a little help setting up the macro. Basically, I retrieve my stock quotes into the Quote sheet, and it updates a total amount. I want to archive the current date and amount to a Database sheet. But there is an important twist. If there is already a row inserted in the Database sheet with the same date, then I only want to update the amount, not insert another row. Once the current date changes, then it is okay to insert another row. This is my data. Sheet Quote has the total amount. The amount is dynamic and will change whenever my stock quotes update the prices of the securities. Stock Price Amount IBM $91.45 15000 MSFT $29.48 20000 ------ -------- ------- Total 35000 Sheet Database stores the current date and current balance on the last row, as well as archived dates and balances going back in time. Only the last row with the current date is to be updated. The data is sorted in ascending date as you can see. Date Amount 11/24/06 30000 11/25/06 33000 11/26/06 34000 11/27/06 35000 ** This row can be updated multiple times or a new row inserted below if date changes |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
I've put alot of work into this so far, so I would be interested in what
Yahoo can provide. However, I can't seem to locate your group Don. Can you provide a website? Thanks "Don Guillett" wrote: Yes, Don't bother with MSNstockquote. Use Yahoo instead. There are some good free files, including mine at the Yahoo xttraders group. -- Don Guillett SalesAid Software "Tim" wrote in message ... bump....anyone have any answers for this? "tfrentz" wrote: Hi, I'm still working on this, and haven't found a solution. I'd really appreciate some help from you macro experts. Is there a way to reference a button from an excel add-in - specifically I'm trying to reference the MSNStockQuote 'update quotes' button. thanks, Tim "tfrentz" wrote: Hi, If there is anybody out there who knows anything about referencing a button from an add-in in a macro, please let me know. I think I lost Martin. thanks, Tim "tfrentz" wrote: Martin, Thanks for responding, but your response doesn't help me at all. I mentioned before that a call to the MSNStockQuote add-in returns a quote to the spreadsheet. The function reference for it is: MSNStockQuote(Symbol,Property,CountryCode) So, if I call it within my macro, I have to supply the parameters for 1 quote. This is different from what I'm trying to do which is update all my quotes at once. This can only be achieved presently by clicking the "Update Quotes" button on the MSNStockQuote toolbar. What I want to do is reference this "Update Quotes" button within my macro. If you download the MSNStockQuote add-in, you'll see what I mean. http://www.microsoft.com/downloads/d...displaylang=en Tim "Martin Fishlock" wrote: Tim, You don't want to get the code all you need is to call to the function/subroutine in the add in... Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. updatestockquotes ' name of macro/subroutine ' find total row in Quotes -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "tfrentz" wrote: Martin, I'm not sure if what I'm trying to do is even possible.....extract the code of an add-in and put it into my own macro. Have you ever done this before? Where would I look within Excel or elsewhere (some internal file on my computer) to get this code from the MSNStockQuote? Thanks, Tim "Martin Fishlock" wrote: Tim, I tried to download the file but it kept on hanging on my so i will have to leave it for now anyway good luck in your quest. -- Hope this helps Martin Fishlock "tfrentz" wrote: Martin, Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
automate row insert to archive
https://login.yahoo.com/config/login...raders%2Ffiles
-- Don Guillett SalesAid Software "Tim" wrote in message ... I've put alot of work into this so far, so I would be interested in what Yahoo can provide. However, I can't seem to locate your group Don. Can you provide a website? Thanks "Don Guillett" wrote: Yes, Don't bother with MSNstockquote. Use Yahoo instead. There are some good free files, including mine at the Yahoo xttraders group. -- Don Guillett SalesAid Software "Tim" wrote in message ... bump....anyone have any answers for this? "tfrentz" wrote: Hi, I'm still working on this, and haven't found a solution. I'd really appreciate some help from you macro experts. Is there a way to reference a button from an excel add-in - specifically I'm trying to reference the MSNStockQuote 'update quotes' button. thanks, Tim "tfrentz" wrote: Hi, If there is anybody out there who knows anything about referencing a button from an add-in in a macro, please let me know. I think I lost Martin. thanks, Tim "tfrentz" wrote: Martin, Thanks for responding, but your response doesn't help me at all. I mentioned before that a call to the MSNStockQuote add-in returns a quote to the spreadsheet. The function reference for it is: MSNStockQuote(Symbol,Property,CountryCode) So, if I call it within my macro, I have to supply the parameters for 1 quote. This is different from what I'm trying to do which is update all my quotes at once. This can only be achieved presently by clicking the "Update Quotes" button on the MSNStockQuote toolbar. What I want to do is reference this "Update Quotes" button within my macro. If you download the MSNStockQuote add-in, you'll see what I mean. http://www.microsoft.com/downloads/d...displaylang=en Tim "Martin Fishlock" wrote: Tim, You don't want to get the code all you need is to call to the function/subroutine in the add in... Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. updatestockquotes ' name of macro/subroutine ' find total row in Quotes -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "tfrentz" wrote: Martin, I'm not sure if what I'm trying to do is even possible.....extract the code of an add-in and put it into my own macro. Have you ever done this before? Where would I look within Excel or elsewhere (some internal file on my computer) to get this code from the MSNStockQuote? Thanks, Tim "Martin Fishlock" wrote: Tim, I tried to download the file but it kept on hanging on my so i will have to leave it for now anyway good luck in your quest. -- Hope this helps Martin Fishlock "tfrentz" wrote: Martin, Thank you very much. That was exactly what I needed. To make it easier to invoke the macro for now, I added a command button to the Quotes sheet and then invoked the macro name from within the command button code. I could still use a little help getting the MSNStockQuote update function to work within the macro. Microsoft labels it as an add-in on their reference page...I don't know if that makes a difference or not to you. Here is a reference page to it: http://office.microsoft.com/en-us/ex...346101033.aspx While I know how to invoke it as a function, I'm not sure how to isolate the "Update Quotes" button from the toolbar in Excel to put that code into my macro. The end result would be perfect - I could invoke the UpdateStockValues macro and at the same time update the quotes. Here is a link to the MSNStockQuote add-in. Hope that helps explain what I'm trying to do. http://www.microsoft.com/downloads/d...displaylang=en Any advise appreciated. If you can think of a better way to do this, let me know. Tim "Martin Fishlock" wrote: I think the best way to try and do it is as follows write a marco as follows: '---------------------------- Sub updatestockvalues() Const sheetNameQ As String = "Quotes" Const sheetNameDB As String = "Database" Const QcolumnName As String = "A" Const QcolumnValue As String = "C" Const QrowStart As Long = 2 Const QtextTotal As String = "Total" Const DBcolumnDate As String = "A" Const DBcolumnValue As String = "B" Const DBrowStart As Long = 2 ' not the titles Dim Amt As Currency Dim r As Long ' row pointer Dim c As Long ' column number Dim wsQ As Worksheet, wsDB As Worksheet Set wsQ = ThisWorkbook.Sheets(sheetNameQ) ' insert the macro to run the quotes here. ' find total row in Quotes r = QrowStart Do While wsQ.Range(QcolumnName & r) < QtextTotal r = r + 1 If r = 65537 Then MsgBox "Error finding total, halting.", vbOKCancel, "Error" Set wsQ = Nothing Exit Sub End If Loop Amt = wsQ.Range(QcolumnValue & r).Value Set wsQ = Nothing Set wsDB = ThisWorkbook.Sheets(sheetNameDB) ' find last day in Database assume that there is at least one date r = DBrowStart Do While wsDB.Range(DBcolumnDate & r) < "" r = r + 1 If r = 65537 Then MsgBox "Error in database sheet, halting.", vbOKCancel, "Error" Set wsDB = Nothing Exit Sub End If Loop If (wsDB.Range(DBcolumnDate & r - 1) = Date) Then r = r - 1 ' date already there Else wsDB.Range(DBcolumnDate & r) = Date End If With wsDB.Range(DBcolumnValue & r) .Value = Amt .NumberFormat = "#,##0.00" End With Set wsDB = Nothing End Sub '---------------------------- This will update the sheet with the new day or current value. You may have to play around with the dates if they are not dates in the database but text. You now need to get the name of the macro that is called when you click the update quotes button and insert it into the macro above at the place where indicated to insert the macro and then instead of clicking the current button you click a new button that you need to asign to the above code. I don't have the msquotes program so I can't advise. I may be able to look at it if you give me a link to it. This should set you on your way. -- Hope this helps Martin Fishlock "tfrentz" wrote: Can someone please give me a hand. It looks like Martin got busy on something else. Thanks. "tfrentz" wrote: The stock prices are updated via the MSNStockQuote function add-in. One click of the 'Update Quotes' on the toolbar will update all the stock prices, and this will update the total. I haven't ran into missed days, but I guess the answer is I don't care if I happen to miss a day. Obviously, Saturday's and Sunday's the markets are closed, so no updates take place. So on Monday's, there would be two missing dates for the weekend. I guess, I could manually calculate the stock prices using historical data, and then add them up and insert a manual entry to the Database, but this isn't of prime importance. The only important thing to me is that the balance is current on the Database as I want to use these numbers to later track my portfolio performance over different time periods. Hope that makes it clear, and thanks for your help. "Martin Fishlock" wrote: How do you update your stock quotes sheet? What happens if you miss a day? Ie you run it on a monday and then on a wednesday? There are couple of ways to do it but I will give my comments after comment on the above. -- Hope this helps Martin Fishlock "tfrentz" wrote: I need some help automating a row insert from one |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate new Word file and insert autotext | Excel Discussion (Misc queries) | |||
Changing the Insert Row / Column Default | Excel Discussion (Misc queries) | |||
How do I insert a row at the end of a table. | Excel Discussion (Misc queries) | |||
insert picture | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel |